Part 4: Adding Activity Logs and More to your Runescape Excel Hiscores Tracker

Welcome to my series on creating your own Runescape Hiscores Tracker in Microsoft Office Excel. This time, we will be looking at how to add player Activity Logs and more.

For those just joining us, this guide is written in the assumption that you have already completed the first three:

Part 1: How to Make a Runescape Hiscores Tracker in Microsoft Office Excel - Collecting data ready to feed your tables and charts.

Part 2: Averages Table in your Runescape Excel Hiscores Tracker - Creating the first table, which worked out your XP averages from highest to lowest skill. There was also a bonus section answering some formatting questions, mostly from those new to Excel.

Part 3: Calculating XP to Next Level in your Runescape Excel Hiscores Tracker - Creating a table to calculate the XP needed until your next level.

Now it's on to grabbing more data and creating a chart to show off your latest Runescape achievements.

Creating Another Data Worksheet in Excel

We already have one data worksheet, but I'm going to recommend another. This is for no other reason than to keep it looking neat and tidy, especially as we're about to insert a massive chunk of automatically up-dating information.

• Right-click the 'Sheet3' tab at the foot of your Excel workbook.
• Select 'Rename'.
• Type 'Data2' and press enter.
• Left-click the 'Data2' tab and drag it in between your other two tabs.

We will be working on the Data2 sheet for the next part.

The Runescape Activity Log Data Feed in your Hiscore Tracker

The very first task that we did, in this series, was to take data from a web query. That produced Jagex's hiscore lite text feed, which was lovely, but now we're going to take this to the next level.

• From Excel's top toolbar tab, select 'Data'.
• Select 'From Web' from the 'Get External Data' category.

A mini browser will pop up. This gives you access to anywhere on the web, which will prove extremely useful in getting data for our Runescape Excel Hiscores Tracker.

• Copy this URL: http://services.runescape.com/m=adventurers-log/rssfeed?searchName=[player name]
• Paste it into the address bar of the pop up browser.
• Change [player name] to your own Runescape name. (Mine is http://services.runescape.com/m=adventurers-log/rssfeed?searchName=Merch Gwyar.)
• Click the little yellow and black square to highlight the contents of the browser.
• Press 'Import' at the bottom.
• Excel will tell you that the XML source does not refer to a schema. We don't care. Press OK.
• Press OK on the Import Data box. (But only if it's got 'XML table in existing worksheet' ticked for the cell $A$1.

You should now be seeing why I recommended a separate data worksheet, as the information fills the page. This works on any RSS feed, so do experiment with adding in any that you think will be useful. It will automatically update as soon as you log out of the game or, more specifically, as soon as the Activity Log updates on the Runescape site.

Displaying your Runescape Activity Log in Excel

The beauty of Microsoft Office Excel is that it allows you to have more than one tab. I'm going to create a new page to display my Activity Log.

• At the foot of your workbook, click the final tab. (It has an icon of a worksheet with a little gold star in the corner.)
• Rename 'Sheet4' to read 'Activity Log' (or the name of your choice).
• Click cell A1 and type 'Activity'.
• Click cell B1 and type 'Description'.
• Click cell C1 and type 'Date'.
• Copy and paste the following formula into cell A2:
  
  =Data2!K2
• Drag that formula down to cell A51.
• Widen column A, so it easily displays the information.
• Copy and paste the following formula into cell B2:
  
  =Data2!L2
• Drag the formula down to cell B51.
• Widen the B column to fit the information.
• Repeat the process in C2, but with the following formula:
  
  =Data2!N2

You should now have all your Activity Log handily showing in your Runescape Hiscores Tracker. You will not need to touch that data again, so let's make the page go away.

• Right-click the 'Data2' tab.
• Select 'hide'.

Bye-bye Data2 Worksheet. Should you ever need it again, just click to create a new worksheet. If you right-click the tab of a blank worksheet, you'll find the option to 'unhide'. Selecting that will list all of your hidden sheets and Data2 will there amongst them. Once you click the name, Data2 will be visible again.

All that remains now is to colour, format and do whatever else you would like to make your Activity log look pretty. These techniques have been walked through before, so I won't repeat them here.

My Activity Log now looks like this:

Click the image for a full-sized view.

An Alternative Way of Adding Activity Log Data to your Hiscores Tracker

We've added text lite and RSS feeds from the web, but we can also insert the information from whole web-pages. As that updates, then so will the raw version in your Excel workbook.

• Create a new worksheet.
• Name it 'Data3' and drag the tab to a position after your main datasheet. 
• From Excel's top toolbar tab, select 'Data'.
• Select 'From Web' from the 'Get External Data' category.
• Copy and paste the following URL into the address bar of the pop-up brower:
  
  http://services.runescape.com/m=adventurers-log/display_player_profile.ws?searchName=[player name]
• Replace [player name] with your Runescape name. (My URL is: http://services.runescape.com/m=adventurers-log/display_player_profile.ws?searchName=Merch Gwyar.)

• Click the yellow and black box to highlight the whole page.
• Press the 'Import' button.
• Press OK on the pop up 'Import Data' box. (But only if 'Existing Worksheet' is selected and =$A$1 is the cell named in the box.)

A lot of raw data will be copied into your Excel worksheet. It doesn't look like the webpage that you nicked it off, but it will update as that page does. By scrolling down and comparing the two, you will see what data you have. Anything can be taken from this worksheet and added to a table in the usual way.

Creating a Small Runescape Activity Log Tracker from Website Data

As this isn't the full RSS feed, the number of activities is much reduced. We want a table with three columns and five rows.

• Chose where you wish to place it and create that table.
• Colour it in, then format it with borders.
• Type 'Activity', 'Description' and 'Date' in the cells heading each column. These are the headers.
• In the cell under the 'Activity' header, copy and paste this formula:
  
  =Data3!A292

Just a refresher for those unfamiliar with that formula. It is basically an address. 'Data3' is the name of the worksheet; A292 is the cell that from which we wish to copy the contents.

• In the cell under the 'Description' header, copy and paste this formula:
  
  =Data3!A293
• In the cell under the 'Date' header, copy and paste this formula:
  
  =Data3!B292
• Repeat this for the next row, using these three formulas in each consecutive cell:
  
  =Data3!A294
  =Data3!A295
  =Data3!B294
• Repeat this for the third activity row, using these three formulas:
  
  =Data3!A296
  =Data3!A297
  =Data3!B296
• Repeat this for the final row, using these three formulas:
  
  =Data3!A298
  =Data3!A299
  =Data3!B298
  
• Hide the 'Data3' worksheet.

My little Activity Log looks like this:

Please click the image for a full size version.

Website Data and Tables for your Runescape Hiscores Tracker

The main point that I wished to demonstrate, with the last table, was that you can take disparate cells and put them together to make your own charts. The sprawl of data that you took from the Runescape Activity Log webpage has plenty more opportunities to feed tables.

For example, you could add another column to your XP to the Next Level table, by altering that formula to read the data telling you the percentage of a Level completed. That's listed in the Data3 worksheet. Just find the cell letter and number, then add it to =Data3! and paste it in.

This works with every webpage out there, so happy hunting for things to add to your Runescape Excel Hiscores Tracker!

Next time we will be looking at ways to track your daily XP gains.