Jump to content
brainymidget

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

Recommended Posts

Google Docs is essentially like Microsoft Office, and the spreadsheet form which we will be using is essentially like Excel, but it has a helpful feature which is that it can extract data from the internet(Although I recently found out Excel can as well but I haven't found a way to do it as efficiently so I'm sticking with Google Docs.)

 

Extracting prices on the GE can be helpful for many things, the biggest one is making calculators. I make lots of Gp / Xp calculators and I use this so that they are always up to date.

 

So ya, on with the guide.

 

 

 

[hide=How to use Google Docs]To be able to use Google Docs, you will first need a Gmail account, or Google Email. Now your first step is to head to Google Docs and sign in. If you don't have a Gmail account, you will see a button somewhere on the bottom of the screen that will lead you to a page where you can create an account. Now that you have Google Docs open, we need to start the process. Start off by making a new spreadsheet, this is what I use to make the calculator, or for the purpose of this guide, simply extracting prices off the database.[/hide]

 

 

 

[hide=ImportHtml]First off, what I do is start with this formula, to extract the data from the GE database.

 

=ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2)

 

ImportHtml takes the HTML off of the specified site.

 

Now there are three parts that are going into the parenthesis for Importing the Html. The Url, the type of Html to look for, and the index of the html.

 

The Url is where it is extracting the data from. Make sure it is in quotations or it will not work. The link you see me using in the code above is the link that you will always be using for extracting GE prices although the query will change.

 

The type of Html for it to look for is next. There are only two options for this, table, or list. List will just find things that are in the list format, all you will get from this is the Drop down menus at the top of the screen. We will be using "table". This will look for tables within the page, which is what the prices are contained within. This also has to be within quotation marks or it will not work.

 

The index is the third thing, which is where you see the number 2. This 2 is used because the table where the prices are found is the second table within the page. For other things, you may need to use a different index but just for extracting GE prices, you should always just be using 2.

 

 

 

When I entered this whole code into excel, in cell A1, I came out with something that looked like this.

 

importhtmlfq5.png

 

As you can see, that's more information then we need, so we have to narrow it down.[/hide]

 

 

 

[hide=Index]You see here that the first column is empty, the second column is the item name, and the third column is prices. Now prices is what we care about. The price of the thing that we want, Willow logs, is in Row 2, column 3. So now we will use "Index" to tell it to only take the information from Row 2, Column 3.

 

Index is a code that tells what row and column to take information from. This way instead of getting all the data like in the picture above, we can just get one specific cell out of that, the cell that contains the price of whatever you are looking up, in this case, Willow Logs.

 

After you add "Index" it will look like this:

 

=Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2),2,3)

 

So what you did was added "Index(" before Importhtml, and ",2,3)" after it.

 

Now the results will just be "18" the price of willow logs. Sometimes, the first option when you look up an item will not be the item you are looking for, in which case you will have to change the row. If it is the second item, just change it to ",3,3" or "4,3" for the third item, and I think you get it by now. So now you have looked up the exact price of an item and put it into a cell of your spreadsheet but we are not completely done yet.[/hide]

 

 

 

[hide=Concat]Now there is one thing you can do to make it even easier. With my calculators, I don't want to manually enter every item, so I just have the code take the item that is put in after "query=" from within my spreadsheet.

 

To do this, I use "Concat"

 

Concat takes the information from a cell within your spreadsheet, and inputs it into your code. We will be using it so that instead of typing out "Willow Logs" this code will see Willow Logs typed out in A2 and put willow logs directly into the link. So now, let's change the code once more.

 

The code should now look like this:

 

=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)

 

As you can see I added "(Concat" after Importhtml and ",A2" after query=" What this does is tells it to find what to put in that position is the Informaton found in A2. If the price you are looking up is in A3, then just change to A3.[/hide]

 

 

 

[hide=Making a calculator]Now that you know how to extract information from the GE database, I will give you a little bit more help on making a calculator like my Gp / Xp ones.

 

First off you need to make the headings, which will basically be what the Raw product is, what the finished product is, how much experience you gain per, how much it costs, and how much it sells for(Use your knowledge from the above reading to do these), and then how much you lose/profit per, and then Gp/Xp.

 

 

 

Now that you have the headings, you need to fill them. Start off by filling up what the Raw and finished product is. Try and get the names exactly what they are so that it will extract the right prices. Then fill in the experience, there is no secret trick to this, just go on the Manual and find how much exp it gives.

 

The next two will be the cost of the raw, and the selling price of the finished, in these two you will fill in the code you learned earlier.

 

Next up is the Profit/Loss per. For this just subtract the Finished product from the Raw Product, or vice versa, depending how you want to do it. Now for this, don't just input the prices or it won't auto update that, you need to input the cells so it will always take the data from there no matter how much it changes. So put something like "=F2-E2" and that will give you the result you're looking for.

 

The last one is the Gp / Xp per. For this you are dividing your profit/loss by the experience gained. To do this you just do something like =G2/C2. Once you do this, the results might not always be pretty, you will probably have a decimal with 20+ digits. To fix this we need to round it, I personally round to 3 decimals. The code I use for this is: =ROUND(G2/C2,3) which divides G2 by C2 and then rounds it to 3 decimals.[/hide]

 

 

 

So there you go, you can now make an always updating calculator with prices straight from the GE database.

 

Once you're done with it all, you should have a finished product something like this.

 

cookingcalculatordq5.png


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites

When i extract the price of runite ore, it returns 16.1k and the "k" makes the spreadsheet fail to calculate the cost of smelting runite bar. What can i do to make it calculate correctly? Please help!


bannerbancocopiadj3.jpg

1113 total. Always f2p.

Share this post


Link to post
Share on other sites

Hmm, I honestly don't know. I'll take a look into it and tell you if I find out how to make it work.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites

Hello, I tried creating formula for parsing the "m" and "k" prices.

 

The formula would be very big if I would have to merge it with the importhtml formula, so the value loaded from the price list is in D2.

 

=if(iferror(find("m",D2),false),left(D2,find("m",D2)-1)*1000000,if(iferror(find("k",D2),false),left(D2,find("k",D2)-1)*1000,D2))

 

here is a snapshot:

 

picture2in1.png

 

 

 

BTW: Thank you for this guide. Really great idea.

Share this post


Link to post
Share on other sites

I so love you <3:<3:<3:

 

I asked this to sven bohikus, but he didn't reply (he had a guide for tracking xp)

 

realy, thanks for doing this


EisS.gif

Share this post


Link to post
Share on other sites
I so love you <3:<3:<3:

 

I asked this to sven bohikus, but he didn't reply (he had a guide for tracking xp)

 

realy, thanks for doing this

 

 

 

Ya, I've used svens guide for a while to track competitions within my community. I based this largely off that, I just had to ImportHtml instead of ImportData.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites
When i extract the price of runite ore, it returns 16.1k and the "k" makes the spreadsheet fail to calculate the cost of smelting runite bar. What can i do to make it calculate correctly? Please help!

 

I'm getting the same problem with the + signs on the price changes. It regonizes - as negatives, but the + is treated as a character, which messes it up.

 

 

 

Hello, I tried creating formula for parsing the "m" and "k" prices.

 

The formula would be very big if I would have to merge it with the importhtml formula, so the value loaded from the price list is in D2.

 

=if(iferror(find("m",D2),false),left(D2,find("m",D2)-1)*1000000,if(iferror(find("k",D2),false),left(D2,find("k",D2)-1)*1000,D2))

 

I presume it's possible to make something that gets rid of + signs?


quit

Share this post


Link to post
Share on other sites

I made some F2P high alch sheets real quick... Check'em out :)

 

 

 

 

 

Armor:

 

http://spreadsheets.google.com/ccc?key= ... WhBqpWmUaQ

 

 

 

Jewelry & Sets:

 

http://spreadsheets.google.com/ccc?key= ... UzqFLvLwfw


pikatips001.pngjjjon123.png

pikatips002.pngjjjon123.png

pikatips003.pngjjjon123.png

pikatips004.pngjjjon123.png

pikatips005.png1,000 F2P Total Level Reached 10/8/10 !

 

[hide=Guides]Magic & Summoning Profit Spreadsheets! *UPDATES EVERY HOUR* (includes: High alch, Superheat, and Enchanting)

4 BETTER alternatives to flesh crawlers[/hide]

 

WOT WOT!

 

☉.☉☂

Share this post


Link to post
Share on other sites

so if f2p then high alch rune longswords? thats pretty helpful

 

 

 

im going to go and buy 50k rune longswords then tell everyone about this


macho_munky.png

Share this post


Link to post
Share on other sites

Thanks a lot! I was kinda looking for this and I've now updated my MTK sheets with this method of drawing prices out. Now, I've got one question: how often does this update? Every 24 hours, or every time someone opens it?


Aurei_Animus.png

Share this post


Link to post
Share on other sites

Well with Hi-scores, which should be the same as extracting from the GE database, it updates every 1 hour, or whenever I refreshed all the data in my spreadsheet, but I never really do that so it's pretty much every 1 hour.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites
Thank you Brainy!! I can have a quick and easy sheet for my herblore guide. :) <3:

 

 

 

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

 

Hehe!

 

 

 

This guide rocks!!! <3:

 

 

 

 

 

Glad to help, and by the way, the URL you posted links to the page where you edit the spread sheet. You don't have it set so that people can edit or view your document, so people can't view it. What you need to do is click Share > Publish as a web page, then it will give you the link to it.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites

Awesome guide. Made this ranged calc yesterday.

 

 

 

By the way, if anyone needs to pull stat experience from the high scores the formula is: =Index(ImportHtml(Concat("http://hiscore.runescape.com/hiscorepersonal.ws?user1=",B1), "table", 1), 7,5). All you have to change is B1 to your user/cell where your user's in and the bolded 7, which is the row your skill is.

Share this post


Link to post
Share on other sites

Any ides where I can find the data for total trade volumes? The data is there somewhere because they use it for the top 100 most traded items, but I would like to be able to poll the data for other items as well.

Share this post


Link to post
Share on other sites

I don't think that it's public information for anything except the top 100.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites

To extract prices and other numeric or non-numeric values from html tables, there is a very good script posted at http://www.biterscripting.com/install.html .

 

 

 

Now, to take care of the k (1000) and m (1000000) notations, I would add the following at the end of that script.

 

 

 

while ( { sen r -c "^;k^" $csv } > 0 )

   sal -r -c "^;k^" ",000" $csv > null



while ( { sen -r -c "^;m^" $csv } > 0 )

   sal -r -c "^;m^" ",000,000" $csv > null

 

 

 

Add the above before the echo $csv line in the script. I am using -c (case insensitive option) and -r (regular expression), because k and m may also appear as "K or "M" or " K" or " M", etc.

 

 

 

Patrick

Share this post


Link to post
Share on other sites

That doesn't work with GoogleDocs, GoogleDocs uses the same kind of code as Excel. And someone already posted how to convert the m's and the k's with Google Docs.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites

Why is all your query types =ImportHtml("http://itemdb-WOW.runescape.com/results.ws?query=Willow Logs", "table", 2)

 

 

 

instead of

 

 

 

=ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2)

 

 

 

 

 

Oh wait... don't tell me... -.- what a lame April Fools


All I learned in life, I learned on Tip.it

Share this post


Link to post
Share on other sites

Hey cool! You guys did it! :thumbsup:

 

 

 

I couldn't figure out how I was going to parse the GE database output, but I'm glad someone else did. Excellent work guys! \'


svenne.png

#14340th to 99 magic!! w000t

Share this post


Link to post
Share on other sites

can any one make one that stores the prices historically against date, so eventually we would have more than 30 days info?

 

 

 

G


Gda_Scooby.png

Share this post


Link to post
Share on other sites

I'm pretty sure there isn't a way to do that but if anyone wants to try it, feel free and if you do post on here so I can add the information to my guide, with credit given to the finder of course.


Brainymidget.png

multimultiza9.gif

Share this post


Link to post
Share on other sites
can any one make one that stores the prices historically against date, so eventually we would have more than 30 days info?

 

 

 

G

 

 

 

 

 

RuneCrypt already has something similiar here http://www.runecrypt.com/graph/11732


All I learned in life, I learned on Tip.it

Share this post


Link to post
Share on other sites

Thanks again for this tutorial, I been putting this into good use:

 

 

 

GE values for Crafting - Gems, Google Docs version

 

 

 

Pikkupstix Pouch Trading shard/gp conversion rate, Google Docs version

 

 

 

This is very useful, although I am not too sure whether google docs updates the web


All I learned in life, I learned on Tip.it

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.