Sven's Guide to RS High scores Tracking with Google Docs

[sven_bohikus]

Sven Bohikus' Guide to RS High scores Tracking with Google Docs Spreadsheets

 

 

 

[This thread was lost in the great tip.it flood of 2008 - luckily I had edited and saved my post on a GoogleDoc! Reposted here for your convenience!]

 

 

 

[hide=Table of contents]1. Rationale

 

2. Getting started

 

3. Setting up your spreadsheet

 

4. Reading in high score data

 

5. Finding experience point deltas

 

6. Keeping your spreadsheet sorted

 

7. Charting your top-10

 

8. Sharing and publishing your document

 

9. Limitations[/hide][hide=1. Rationale]My use of spreadsheets to track RS high scores started while I was a council member of Skillaz clan. We would regularly host a 'skill week' wherein members were encouraged to gain as much exp in one skill as they could in a period of time (I once posted 2 million thieving exp. in 3 days). High scoring skillers would get prizes before the trade limits and later were given special signatures for their effort.

 

 

 

Keeping track of the experience gained by 30+ clan mates was difficult to do by hand, and while creating an html or php based file to show simple high score data and differentials was easy, finding hosting for such a file was troublesome. Google Docs turned out to be the answer, offering a simple and easy to use front end, free hosting, and the ability to read high scores. Getting the high scores data into the spread sheet was tricky though and people have often asked my my technique for doing so, hence this guide.[/hide][hide=2. Getting started]You will need a google gmail account to make google documents. If you have a gmail account, you're ready to go. Sign into the google documents web page at http://docs.google.com If you do not have a gmail account, you can create one on the same page. Note: I used to have privacy concerns surrounding the use of Google for document storage, and I still do. However in this instance the data on your spreadsheet is already available on the Internet in another form. Should you choose to use Google Documents more extensively, you should use caution and consider what sort of other files you really wish Google to hold for you. Most importantly pay close attention to the terms of service http://www.google.com/accounts/TOS?hl=en and the privacy policy http://www.google.com/google-d-s/privacy.html .[/hide][hide=3. Setting up your spreadsheet]Choose From the 'New' menu choose 'Spreadsheet'

 

 

 

 

In row number 1 type in the headings for your spreadsheet. I use Name, Starting exp, Current Exp, and Exp Delta. You can call them anything you like.

 

 

 

 

Grab the divider line as shown in the image below and slide it down below your headings. This will freeze your heading and make it easier to sort the remainder of the spreadsheet. You want to grab the grey rectangle indicated by the yellow box and drag it with the mouse down.

 

 

 

It should look like this when you have it moved correctly.

 

 

 

 

Add a player's RS name in column A row #2 -- Type in my name 'sven_bohikus' or any other name you like.

 

Add starting exp for the same user in column B row #2, here you can find any variety of sources for this data, I like to get a PDF of scores from RuneHead, then copy and paste the data from there. Take a moment and Save your spreadsheet before the next step.[/hide][hide=4. Reading in high score data]The next step is a formula for the cell at column C row #2. This formula will get experience data from the RS high scores page automatically and parse the data until we have a single data value from a user's high scores page. Here is the formula for the cell:

 

=INDEX(ImportDATA(CONCAT("http://hiscore.runescape.com/index_lite.ws?player=",A2)),9,3)

 

 

 

Explaining the parts of this might be tricky:

 

 

INDEX - allows us to pick one ROW and COLUMN as if you were looking at the official RS High scores page. Row 9 column 3 in the example is current exp points for cooking.

 

ImportDATA - gets the contents of a URL, in this case it gets a table of high score numbers for a given user.

 

CONCAT - this will concatenate the high scores retrieval stub to the player name in the cell to the left.

 

 

 

Put it all together and you will get a single integer value back from the high scores table, if your user's name is correct. Use the fill handle (small black square handle on the bottom right hand corner of a cell selection rectangle) to fill this formula down for all the rows in your spreadsheet.[/hide][hide=5. Finding experience point deltas]

 

In cell D2 type the following '=C2-B2' this will subtract the starting exp from the current exp.

 

As before use the fill handle to fill this formula down for all rows of your spreadsheet.[/hide][hide=6. Keeping your spreadsheet sorted]In our uses we liked to see who the top 10 experience gainers were for the time period of the competition. This required sorting the data by the Exp Delta column. I haven't found a way to keep the spreadsheet sorted automatically, but if you click the grey bar under the Exp Delta header, you will get a menu that allows everything under the divider bar to be sorted in ascending or descending order.

 

[/hide][hide=7. Charting your top-10]You can make a second sheet in your document to hold a top 10 chart and have it automatically redraw every time you open the document. On the bottom of the Google Docs window, look for a button 'Add Sheet' and click it. You will be taken to the second sheet immediately. In Cell A1 add a title for your chart - for example 'Cooking Competition'.

 

 

 

In cell A2 of Sheet2, type an = (equal sign) and then click on the tab for Sheet1, and select cell A2 on Sheet1 then hit enter. This will bring the Player name from A2 on Sheet1 to A2 on Sheet2. Do the same thing in column B to bring the Exp from Sheet1 to Sheet2.

 

 

 

 

Position the chart to the right of your list of people to chart. Use the fill function to extend the number of data items to copy over from the 1st sheet. As i said before we used to use a top 10 but you can copy all of them over if you wish.[/hide][hide=8. Sharing and publishing your document]This is the best part - want to put your high scores and chart on the Internet? Well what great luck for you today, it already it is! Its important to know the difference between 'Sharing' and 'Publishing' in Google Docs terms. Sharing means giving google the names of other google docs users who can open or edit your documents. In practice I have rarely used this. Publish means to put the data in your document on the Internet where it will be accessible by a public URL. Most often you will want to use Publish, unless you have high scores helpers. If you do have multiple people helping you, you will always be able to see ALL revisions listed by date and time, and be able to revert to any version of your document at any time - so share fearlessly.

 

 

 

In the upper right hand corner of the Google Docs window, look for the Publish tab and click it. The first time you choose this you must click 'Publish now'

 

 

 

 

The window will change to show you an active link to your published document. Be sure to check the 'Automatically republish' box if you wish to have the published document follow your changes when you save. If you have added a second worksheet with a chart, it will be included if you choose All Sheets.

 

[/hide][hide=9. Limitations]Google will only let you put 40 ImportDATA functions into any one spreadsheet. This can be troublesome if you have a very large clan or competition.

 

ImportData functions only update once an hour, clearly something closer to real time would be better, but at least no one can say you're hammering the RS high score system with many needless requests.

 

The sheet doesn't stay sorted unless you go in and poke it to be sorted. This can be a bother and is a good place for you to 'Share' the document so others can help you keep it sorted ans shipshape.

 

Some times, ImportDATA functions decide not to get their data. Sometimes, this happens, you need to be patient and wait until the next time the cell is due for an update. At all times remember how much you are paying for this service -- nothing.[/hide][hide=10. Thanks to old friends and new]Thanks to bernice00 who is awesome and not just because she has the word nice in her name for finally getting me to write this guide. Also thanks to SDK_Loyal for his asking for the instructions earlier. Best wishes to all the former members of Skillaz clan, and to all the current members of Solers clan of which I am now a part. Thanks to everyone in RS who has made me feel welcome in this great game... and as for the rest of you ... you'll get yours later.[/hide]

[mchainmail]

That's awesome.

 

 

 

Is there any way to store the imported data like every 24 hours?

[sven_bohikus]

It republishes the data to the same web page -- if you used a mirroring script it could pull it down periodically.

 

 

 

I haven't found a way to grow the spreadsheet with tracker-like data imports by date.

[zoomkey]

very, very nice guide!

 

 

 

Could you give a code for tracking prices of the ge database? It would help A LOT of moneymaking guides who want to give an accurate money/hour. It's just an idea but i'm pretty sure loads of people would use it.

[sven_bohikus]

This is a bump for the purpose of insuring this be kept for the Player Made Guides forum.

 

 

 

Erm ... -bump-

[Wkw]

=INDEX(ImportDATA(CONCAT("http://hiscore.runescape.com/index_lite.ws?player=",A2)),9,3)

 

That is for cooking..

 

How do I tweek it for something else?

 

 

 

I got it!

 

 

 

9 is the skill, 3 is what you want it to be. GOT IT :D

[ikkevincent]

Hi sven. i got a question about your tracking system.

 

 

 

Could i apply this on the GE prices too? ive tried a couple of things, and failed.

 

i would like to insert the price from wolf bones, this is the link

 

http://itemdb-rs.runescape.com/viewitem.ws?obj=2859

 

 

 

Thank you

 

 

 

ikkevincent

[Sativas]

I've been looking for something like this forever, HUGE bump and thanks.

 

 

 

I was wondering if you could do the same thing with excel, and if so how would you import the stats on there?

[sven_bohikus]

Hi sven. i got a question about your tracking system.

 

 

 

Could i apply this on the GE prices too? ive tried a couple of things, and failed.

 

i would like to insert the price from wolf bones, this is the link

 

http://itemdb-rs.runescape.com/viewitem.ws?obj=2859

 

 

 

Thank you

 

 

 

ikkevincent

 

 

 

I think you already found this other thread but I will link it here:

 

How to use Google Docs to extract GE prices ~By Brainymidget

 

http://forum.tip.it/viewtopic.php?f=180&t=782319

[sven_bohikus]

I've been looking for something like this forever, HUGE bump and thanks.

 

 

 

I was wondering if you could do the same thing with excel, and if so how would you import the stats on there?

 

 

 

I started off with Excel and it does work - but it wasn't easy to share with others. You use the 'get external data' function of excel and it creates an entire sheet of the imported HTML. You can then reference the cells in the new sheet using regular Excel formulas.

 

 

 

Some reference links on Excel and web data are available here:

 

http://www.exceluser.com/explore/webqueries_1.htm

 

http://support.microsoft.com/kb/165499

[Joe1992b]

=INDEX(ImportDATA(CONCAT("http://hiscore.runescape.com/index_lite.ws?player=",A2)),9,3)

 

That is for cooking..

 

How do I tweek it for something else?

 

 

 

I got it!

 

 

 

9 is the skill, 3 is what you want it to be. GOT IT :D

 

 

 

9 is the skill id, and 3 is exp in that skill realtime.

 

 

 

if you follow the address for the html grab (http://hiscore.runescape.com/index_lite.ws?player=",)

 

change player to any username and make your window small so you only have 3 columns then the first row is total level and the third column is exp in that skill ( 1,3 )

 

so do the same for any skill; 9,3 = cooking etc always keep the 3 (as it is the exp value as oppose to rank or level) and change the first number to suite the skill you want,

 

 

 

anyways nice guide man helped alot

 

hope i helped

[xequma]

I do that with excel, but is excel really the same thing as what your doing?

[Ultimute]

ty good guide! \

[AgingMiser]

Cool. This is better if you're tracking xp over a set amount of time though, like 2 weeks or whatever.

 

 

 

For daily xp and such, there are tracker sites like rsstats.nl & runemonkey.net that you can use, which tbh, I'd much rather prefer using.

 

 

 

Still, cool and informative =D>

[TyrantElf]

could you make/find a way for it to put down the information periodically?

[x1donut]

this is great

[x1donut]

K so i have a problem when i paste the link =INDEX(ImportDATA(CONCAT("http://hiscore.runescape.com/index_lite.ws?player=",A2)),9,3) what would it be if i wanted to get excell to pull the data insted of useing google docs?

[GlobalSmack]

Very nice, Sven. Simple and easy to amend to my own needs...Here's an example of something I threw together for myself that could be used to track goal completion and average daily gains.

 

http://spreadsheets.google.com/ccc?key=0AmzuAfbFuFSVdFo2QzQwbzBOQzdPdjgyc2Y2OGY2NFE&hl=en

 

There's a nice function in the Google spreadsheet that allows you to just continue from the previous cell...so you can work around that limitation on how many indexes you pull from.

 

HOW THAT WORKS:

I'll go with the example you set up in your guide...

so in C3 we have the formula to pull the Cooking XP.

in C4, we just use the formula "=CONTINUE(C3,10,3)"

that will pull the XP of the next skill, Smithing. (Reuse the cell's index, row 10, col 3)

 

This would only really work if you want to read multiple entries from a certain table you pull from. The limitation is 50 References, the CONTINUE function allows you to use a reference more than once...perfect if you're just making a personal skill list or something.

 

 

 

 

(side note: nice to see a former fellow skillaz member, hope all is well)

[sven_bohikus]

I'm always impressed when I see other people's RuneScape related Google docs and they mention this post as how they got started. This is looking a little old and creaky now but still seems to be helping people.

 

One thing I thought I should mention, something that took me a while to figure out. If you are in another country where the comma is used instead of a decimal point (ie 3,14 for PI rather than 3.14) then you will be using a SEMICOLON to separate elements in your IF statements instead of COMMAS.

 

That took ages to realize and only occurred when someone from Portugal created a new document with my instructions and pasted my IF's into their document.

 

Good luck to everyone who continues to make and use Google docs!

[Waave]

I do realize that this post is somewhat old and that this is the first reply in some time. However, as I was trying to figure out how to do exactly what this guide means to explain, I realized that Jagex has changed their website format; the ImportDATA and link do not work anymore. The solution I found is the following cell formula:

 

=Index(ImportHTML("

http://services.runescape.com/m=hiscore/hiscorepersonal.ws?user1=Waave","table",1

),23,5)

 

My username is Waave, so just replace that with your username and the cell should spit out the appropriate information. The above formula gives Runecrafting experience.

[brainymidget]

That doesn't give you the lite version so will put an unnecessary load on Jagex's servers and might also take longer to extract.

The lite version link is still the same so ImportDATA should still be working. If it's not for you, there might have been something else wrong.

 

This is the link you should use, same is always has been.

http://hiscore.runescape.com/index_lite.ws?player=YourUsername

[H4LO]

How do you tweak this to calculate the xp gained on each skill every 24hrs?

Thanks.

[Good I Snipr]

=INDEX(ImportDATA(CONCAT("http://hiscore.runescape.com/index_lite.ws?player=",A2)),9,3)

 

I've gotten this formula to work great in Google Docs for keeping track of my clanmate's daily xp gains.

The troubles I have are:

1. I do not use Google Docs on a regular basis. All my stuff is OpenOffice Calc to be shared via DropBox.

2. Google Docs can only handle 50 at a time. atm my clan is 73 and we have no intention of getting smaller.

3. OpenOffice Calc always shows Err:50? when I try to use this formula.

4. OpenOffice Calc hangs, stops responding, eventually comes back and allows me to see the options for using the wizard to import data. However, it never does anything with the data no matter what I've selected/deselected in those options. The "OK" button never goes live for me to spit it to the spreadsheet.

 

I love the ability to use the username from a cell. I do this in my spreadsheets so that when someone name changes, I need change only the one cell and all others that reference it are auto-updated.

 

Is there a way to make a formula like this work in OpenOffice Calc?

For specifics, I'll display 2 example rows of my spreadsheet. Row 1 is of course on top. Column A | Column B | Column C... u get the idea.

 

username | xp from second to last time i checked | xp from last time I checked | xp difference

Good I Snipr | 5043289 | 504732 | =C2-B2 <- note that I have to manually enter the data into column C. I want to replace that with a formula that grabs it from hiscore lite.

 

I use this data to determine who's been inactive in the clan so we're not hanging onto shell accounts (ppl who've stopped playing). There are other columns that handle the dates that I've checked them and how long a person's been inactive. It's been a great system. However, with Jagex constantly screwing up the clanmate list, it's become necessary for me to grab a player's data directly from the hiscores. I can't manually grab that data for 73+ people each day. I'd go insane.

 

If anyone can help with detailed step-by-step on what should be in Column C, I'd so greatly appreciate it. Feel free to contact me in game too, my pm's always on if I'm online.