Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel

Those reading my Runescape blog regularly see screenshots from my hiscores tracker. It's usually when I'm trying to make a point - proving that a certain method gained x amount of XP - or else it's the monthly divulging of my levels. All necessary stuff for your average gamer.

Here, for example, is what I did yesterday:

I didn't update that sheet. It happens automatically and sits there waiting me for to consult it. It helps my gameplay. In many ways, it directs it. I can use it to find skills that are close to levelling or else would benefit from a little work. Though mostly I'm scrutinising another couple of charts for that.

Again these automatically update without any input from me, once it's all set up. So the question that I'm most frequently asked is how did I set it up?

Excel Hiscores Tracker: Finding the Data

Those charts look very pretty and compact, but they are just the front face. It's the make up or decoration, that causes it to look so pretty. But all that any one of those charts is doing is just presenting data collected from elsewhere.

What you can't see is the hidden worksheet in the background. Let me reveal what's doing all of the real work:

Suddenly it's not so pretty. But then neither is the average brain, which doesn't stop it doing all of the good stuff.

Let's make an Excel Hiscores Tracker!

Runescape Hiscore Tracker: Creating the Data Sheet

I'm using Microsoft Office Excel 2007. Your own version might be different, so please bear that in mind as you follow my instructions. (With many thanks to Paul P, who solved an issue with a query formula that was doing my head in.)

• Open up Excel. You'll find that you have a workbook with three tabs in front of you. Look at the bottom to find that you are on Sheet 1.
• Right-click where it says 'sheet 1'. Select 'rename' from the pull down menu. Call it 'Stats' or 'Data' or whatever you want to call a sheet that will ultimately be hidden from view.
• Click on the A, so that the whole column is highlighted.
  
• Right-click anywhere in that column, then select 'format cells' from the menu.
  
• On the category list, select 'text', then click 'ok'.
  
• Select the Data tab from the top toolbar. Select 'From Web' from the Data toolbar.

A box will open up, which serves as an internet browser. What you need to do is find out where your hiscores are stored online. This is made very easy by Jagex, the owners of Runescape. In the knowledge that so many of their gamers are making trackers, they've created a 'hiscores lite' tally. Mine can be found here: http://hiscore.runescape.com/index_lite.ws?player=Merch Gwyar.

• Click on the URL for my hiscores, then replace Merch Gwyar with your own player name. Paste that URL into the 'From Web' pop up browser.
  
• Click on the black arrow inside the box.
  
• Press the 'Import' button at the bottom of the box.
  

• Click ok, when you are asked if you want to insert this into the existing worksheet
  
• Gasp in awe and wonder, as your first column of data cascades into your worksheet. This will never have to be updated again. Jagex deal with all of that.
  

You will now be looking at a worksheet containing a column full of numbers. Each one is in three parts, separated by a comma. For example:

162663,90,5439918 - Hiscore rank is 162,663; level is 90; xp to date is 5,439,918. 134310,90,5448609 - Hiscore rank is 134,310; level is 90; xp to date is 5,448,609.

Unfortunately, Excel doesn't know this until we tell it. It's time to programme in some formulas.  

Runescape Hiscore Tracker: Formulas For the Win!

One major fail about the Jagex hiscores lite is that it doesn't tell us the name of the skills. We'll have to write those in ourselves.

• In the same sheet, click on square B1 (or anywhere really, as you can tell from my messy data sheet up above. I randomly started on B8 mostly because that's where my cursor landed first on the day that I did this).
  
• Copy the following list and paste it into B1:
  
  Total
  Attack
  Defence
  Strength
  Hitpoints
  Ranged
  Prayer
  Magic
  Cooking
  Woodcutting
  Fletching
  Fishing
  Firemaking
  Crafting
  Smithing
  Mining
  Herblore
  Agility
  Thieving
  Slayer
  Farming
  Runecraft
  Hunter
  Construction
  Summoning
  Dungeoneering
  
  
• Copy and paste the following formula into square C1:
  
  =IF(LEFT(A1,1)="-",1,INT(MID(A1,FIND(",",A1)+1,2)))
  
  
• Click on C1 and you'll see a tiny box in the right hand bottom corner.
  
• Click that tiny box and drag your cursor down to square C26.
  
• Rejoice in the fact that 99% of your levels are suddenly revealed.
  
• Click on D1 and repeat the above in that column, but with this formula to start:
  
  =TRIM(RIGHT(SUBSTITUTE(A1,",",REPT(" ",50)),50))
  
  
• Click on E1 and repeat the above in that column, but with this formula to start:
  
  =TRIM(LEFT(SUBSTITUTE(A1,",",REPT(" ",50)),50))
  
• Go back to C1 and delete the formula from that cell only. Replace it with: =SUM(C2:C26)
  

Your worksheet should now look something like this:

Tomorrow, I'll walk you through making your first chart and adding more data.

More in this series:

Part 2: Averages Table in your Runescape Excel Hiscores Tracker.