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

[noble_aloof]

i'm finding tip.it extremely helpful for my return to runescape.

 

 

 

first i find a farming timer, now i find this! very useful for my investments! thank you =D>

 

question: is there a way that i can make it track prices over, say, 10 days if i reserve a line for each day?

 

it would basically record the prices, then go to the next line for the next price change. is this possible?

[dotadanatic]

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.)

 

 

 

 

Hmm...can you share with us how to do this with microsoft excel(nvm its not efficient)?

[brainymidget]

It's not extremely inefficient, I just find Google Docs a lot easier because you can copy and paste the code extremely easily and also with Google Docs, you can share the spreadsheets easier with friends. I have never personally tried doing it with Excel, but someone I know has a guide for it on a separate forums. Basically what you need to do is go to the Data tab in Excel and click "From web" and then I guess you have to find where the data is on the web and it will do something similar to what Google Docs does, just find the data in HTML and extract it.

[dotadanatic]

Thank you for the reply,brainy. I asked this because I have created several Excel-based calculators myself but I manually enter the data each day, spending up to half an hr each time. Microsoft Excel is much more flexible and nicer so I prefer to stick to Excel if I have a choice.

[Caromon]

Wouldn't have been able to make (and keep up to date) my runecrafting guide without this guide! :thumbup:

[De_Lille_D]

I find it easier to use ImportData to load the complete html file. This way I can use the link to the specific item, not the link to the search for the item. (In case the show order changes, your price will be shown wrong)

 

This does however generate about 300 cells of text, so you might want to put it underneath your calculator or put in a separate sheet.

 

 

 

- Code for ImportData : "=ImportData()"

 

can be entered manually;

 

I use this command based on the item number: "=CONCATENATE("http://itemdb-rs.runescape.com/viewitem.ws?obj=";)"

 

 

 

 

 

- Code for price cell : "=value(MID(; 22; LEN()-21))"

 

is the cell in which the market price is found; usually around the 219-235th row depending on the amount of cells above the importdata cell

 

 

 

 

 

Do know you if there's a similar command in Excel (I've had to use a wizard to import data so far)?

 

Is there a command that can import a specific line in the html-file (I think the market price is found on the 219th line). This would avoid having to load the entire file.

 

 

 

 

 

Here's the file if it wasn't clear: http://spreadsheets.google.com/ccc?key= ... vRX0HfY7Ug

 

PS: if you don't know the item number, search it on website GE and look at the link to the graph page; the item number is the number at the end of the link.

 

I haven't made something that turns "k"s in 1000, but that shouldn't be too hard.

[FinsToTheLeft]

I would very much appreciate instructions on how to do this for Excel 2007, if anyone knows.

[yondergod22]

Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :?

[Kaphias]

Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :?

 

 

 

Copy the below code into a cell of your choice:

 

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

 

 

 

Go the the Grand Exchange page and find the exact name of the item you want to get data for.

 

 

 

Copy the item's name and paste it over the "ITEM NAME HERE" text. The mid GE price for that item will appear in the box.

[yondergod22]

ohhhh ok thanks a lot :D

 

 

 

EDIT: I made a Calculator to Calc the Price of 99 range with chins from my level, using the data from Bedman's guide

 

http://spreadsheets.google.com/pub?key=rhLY1y7OrHSwTb1DQsoGYTQ&single=true&gid=0&output=html

[yondergod22]

I have another question if you don't mind please, how do I find out what table something is in? for example, I want to use the minimum price of an item.

[epicnuts]

Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :?

 

 

 

Copy the below code into a cell of your choice:

 

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

 

 

 

Go the the Grand Exchange page and find the exact name of the item you want to get data for.

 

 

 

Copy the item's name and paste it over the "ITEM NAME HERE" text. The mid GE price for that item will appear in the box.

 

You don't need the excact name, just make sure what you mean is the first search, so you can type 'brassard' instead of Verac's brassard, and you can type parts of the examine, like 'blighted staff' or 'quarterstaff' is ahrim's staff, because the examine says 'Ahrim the Blighted's quarterstaff.'

[yondergod22]

And some stuff doesn't get you the right price even if you search for the exact name because it's still the second thing, like "chinchompa" So you have to change it to 3,3) at the end.

 

 

 

And I still need to figure out how to get the minimum price if anyone knows.

[brainymidget]

Just add the following to the end of the code

 

* .95

 

That will give 95% of the current price which is what the minimum is.

[GreenFire63]

First of all, absolutely wonderful guide, should help me lots in everything ;)

 

 

 

Someone mentioned the problem of + in the price change column, and the problem was never really addressed. This problem interferes with the ability to make a column with daily percentage changes. Would it be possible to eliminate the + from this column using the importhtml code?

 

Or any way to convince googledocs that the "+" means positive?

 

If this is too difficult, any other code eliminating the + would be greatly appreciated

 

 

 

Also, is it possible to make the "m" and "k" conversions directly in the importhtml code?

[Blitzer150]

"#N/A"

 

 

 

Even when I've correctly put the formula into the box and written in the item name correctly, I still keep getting this.

 

 

 

Any thoughts?

 

 

 

Sometimes even once a price update is complete (yes its complete, not occurring) the prices flicker between #N/A and their price.

 

 

 

Any thoughts?

[GreenFire63]

I realized that for most items "#N/A" is displayed right after GE updates, when the items' prices are getting updated. (right after GE update my entire page is full of that) Usually, it takes GoogleDocs around 20-30 minutes to have the new price showing. Since items on the GE aren't updated all at once and some even get double updated, expect #N/A to be there up to 30 minutes after item updates at many times of the day

 

 

 

EDIT: Realized that its not limited only to ge updates.

[Robert_R]

Do I have to type in every url I want to use? It says I can't paste things into the cell while editing it.

[GreenFire63]

Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs.

 

 

 

Makes it much easier.

[Robert_R]

Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs.

 

 

 

Makes it much easier.

 

Ok, but it still won't let me paste into the google docs for some reason, any ideas why?

[GreenFire63]

Have the entire code that you want to have in that cell in notepad first, edit as needed, then copy+paste the whole code into the cell.

 

 

 

You can't, or even if you could, shouldn't, copy+paste bits and pieces of the code into a single cell multiple times, opens up opportunities to screw up the code and is very tedious.

 

 

 

Also, while you are editing a single cell, google docs prohibits you from pasting; its only meant for manual editing.

 

 

 

Does that help?

[epicnuts]

Could you please teach us how to do this in excel, as it's not easy to switch to google docs after years of using excel, and google docs misses some functions I really want to have in some of my calculators.

[AgingMiser]

First of all, absolutely wonderful guide, should help me lots in everything ;)

 

 

 

Someone mentioned the problem of + in the price change column, and the problem was never really addressed. This problem interferes with the ability to make a column with daily percentage changes. Would it be possible to eliminate the + from this column using the importhtml code?

 

Or any way to convince googledocs that the "+" means positive?

 

If this is too difficult, any other code eliminating the + would be greatly appreciated

 

 

 

Also, is it possible to make the "m" and "k" conversions directly in the importhtml code?

 

 

 

Yeah, but... you might be scared.

 

 

 

[hide=Open at your own risk, Thaa wasn't kidding.]

=if(iferror(find("m",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)),false),left(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3),find("m",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3))-1)*1000000,if(iferror(find("k",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)),false),left(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3),find("k",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3))-1)*1000,Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)))

[/hide]

 

 

 

-edit-

 

 

 

Robert, try double clicking the cell first, and then pasting the code. That might help.

[GreenFire63]

Aging Miser, tyvm.

 

Thats...complicated :shock:

 

 

 

Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign!

[AgingMiser]

Aging Miser, tyvm.

 

Thats...complicated :shock:

 

 

 

Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign!

 

 

 

Lol. Only started on Google Docs when I read this guide yesterday tbh, and I found something totally awesome.

 

 

 

If the price of an [item] is listed as ___k in the GE Database, use this code to isolate the number:

 

 

 

=MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000

 

 

 

The # is equivalent to the number of digits preceding the k, plus one. Take runite ore, which is currently listed as 13.7k. There are 3 digits preceding k - 1, 3, and 7. Add 1 to that, which gives you 4, then replace the # in the code with 4.

 

 

 

The code to isolate a number in the millions is almost identical:

 

 

 

=MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000000

 

 

 

Again, the # is equivalent to the number of digits preceding the m, plus one.

 

 

 

The code in my post above was from Thaa's post, and is a logic test - thus, in other words, a universal test that will give a correct number regardless of whether it's a k or m.

 

 

 

I'm working on a script for price changes right now, but for some reason, the spreadsheet's functions aren't loading. When I do find the script (should be in a couple of days, a week tops) I'll post here :geek: