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

[Mongo]

Well I decided to make a smithing calc... I know lots of them are pointless to have (rune items) but I thought it would be cool to add them anyways :D

 

http://spreadsheets.google.com/pub?key=tFIfnO-wHIo3ceZjBrT2yqg&output=html

 

I actually maxed out the number of ImportHtml I could use (50) but it works well :)

 

I also made one before as a trial, it's just my slayer equipment (just to watch and see any drastic changes in price)

 

http://spreadsheets.google.com/pub?key=t5OG4EYPfDrCiFVaGJVYMCw&output=html

 

Great Guide!

 

:D

[FinsToTheLeft]

Hey guys, this is a great guide but I like some others have been having a little trouble when a price ends in K to denote thousands. I've read the whole thread multiple times, but I've still not been able to work out what I'm doing wrong.

 

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

 

(I've also entered magic_seed without the underscore).

 

 

I read before in the thread that I should replace the # with a number, but I'm not sure which number I would put in. Could someone give me some advice?

 

Thanks!

Fins

[Kaphias]

Hey guys, this is a great guide but I like some others have been having a little trouble when a price ends in K to denote thousands. I've read the whole thread multiple times, but I've still not been able to work out what I'm doing wrong.

 

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

 

(I've also entered magic_seed without the underscore).

 

 

I read before in the thread that I should replace the # with a number, but I'm not sure which number I would put in. Could someone give me some advice?

 

Thanks!

Fins

I haven't found a way to do it using one cell only, I've always imported the number with the "k" and then added the following code to another cell:

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

 

Where B22 is the cell with the imported number.

 

Don't know if that is what you were looking for, but hopefully it helps.

[Grimy_Bunyip]

anybody know how to extract both the second and third columns of an array with the Index() function?

Index([Entry],2,3)

is what your guide shows, and it extracts a single entry on the 2nd row, 3rd column

 

My current spreadsheets use:

Index([Entry],,)

I remove the row 2 and column 3 inputs to extract the entire array

 

Index([Entry],,3)

extracts all of column 3

 

I tried using the following input to extract two columns

Index([Entry],,2:3)

but it only results in an error

 

by no means do I NEED to extract exactly two columns, but it would sure make my spreadsheets a lot more compact

If you know i'd really appreciate it.

[jcube69]

is there a way to make the color change if the number is positive/negativ?

 

like for instanace in my "profit tab" if my profit falls to a negative number aka i lose money it changes to color red instead of green?

 

edit never mind i figured it ou all u got to do is add rules on drop down color menu

[tedbundty]

I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en

[yondergod22]

I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en

It could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing,

[AgingMiser]

I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en

It could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing,

 

You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view.

[Grimy_Bunyip]

I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en

It could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing,

 

You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view.

huh, is there a formula view option for published spreadsheets?

[tedbundty]

I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en

It could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing,

 

You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view.

 

I retried and it appears to be working now...

 

Btw i tried getting the top 100 most traded list from rs database onto a spreadsheet but it didn't work, can anyone help me out with that?

 

EDIT: okay I got it all figured out and the spreadsheets are awesome, thanks :)

[Drkmaster0]

Very nice guide, this helped me a bunch. I made a spreadsheet that gets a bunch of prices for items, and occasionally #VALUE appears in the cell with the formula and nothing appears in the row...the only way I've found to fix it is to change the link and how the spreadsheet finds the item in the list. Is there any better way to fix this or what should I do?

Example of the code is

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

Last night that would give me the price, but now all I get is the #VALUE

[Grimy_Bunyip]

Very nice guide, this helped me a bunch. I made a spreadsheet that gets a bunch of prices for items, and occasionally #VALUE appears in the cell with the formula and nothing appears in the row...the only way I've found to fix it is to change the link and how the spreadsheet finds the item in the list. Is there any better way to fix this or what should I do?

Example of the code is

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

Last night that would give me the price, but now all I get it the #VALUE

google doc is just glitchy

the error your describing corrects itself after a given amount of time

re-entering your data points will also fix it.

As far as I know, there is no good way of preventing it altogether.

[Drkmaster0]

Ahh that really sucks. Thanks though

[matalena]

Obj numbers...

 

Great guide! Thanks a lot. I was thinking for a while to do my own calculators and didn't know where to start from. Thanks to you I managed now! :thumbsup:

 

I still have a question though...: is it possible to know the "obj" number of all the items? It would be nice to set the URL to go directly into the GE and take the whole table of the results to work on the data on there.

 

To be clearer I will make an example:

 

the link "http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=5933" will show the GE page for the Willow branch. I would like to know that Willow branch has obj=5933 by just querying (somewhere) for the name Willow branch. How can I do that?

 

Thanks in advance for your help! :rolleyes:

[AgingMiser]

Obj numbers...

 

Great guide! Thanks a lot. I was thinking for a while to do my own calculators and didn't know where to start from. Thanks to you I managed now! :thumbsup:

 

I still have a question though...: is it possible to know the "obj" number of all the items? It would be nice to set the URL to go directly into the GE and take the whole table of the results to work on the data on there.

 

To be clearer I will make an example:

 

the link "http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=5933" will show the GE page for the Willow branch. I would like to know that Willow branch has obj=5933 by just querying (somewhere) for the name Willow branch. How can I do that?

 

Thanks in advance for your help! :rolleyes:

 

Funny, I myself used to wonder about this. Unfortunately, Google Docs is incapable of returning a URL; consequently, it can't be used to determine object numbers to my knowledge. However, it should be possible to write a script, in which you input the item's exact name, and then returns the object number.

[matalena]

Funny, I myself used to wonder about this. Unfortunately, Google Docs is incapable of returning a URL; consequently, it can't be used to determine object numbers to my knowledge. However, it should be possible to write a script, in which you input the item's exact name, and then returns the object number.

 

AgingMiser thanks a lot for your reply. I would like to know how to write a script like the one you suggest!

 

For now I prepared some VB code in Excel (I am not using Google Docs, I find it too limiting) which does the job, but the other way round. My code doeas the following:

- cycles from 1 to n

- apply the ItemHTML on the page in the GE with the details of the market change of an obj number (for example with number 864, I apply itemHTML on the page: http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=864 and will get the GE page for the bronze knife)

- I will retrieve the whole page like this (no table numbers available!)

- in row 70 I get the names of the item associated with the obj number.

 

By doing this for "a lot" of numbers, I should end up with a full databse of obj-numbers/item-names. Of course this takes ages and I have to do it in blocks of, let's say, 1000 numbers per sheet. Good news is that, from some test I have done, I can see that when a number is not associated to an item, I get the string "Error", so at least it's easy to eliminate the unassociated numbers afterwards!

 

I will finish preparing the script and will run it at night... ;)

 

Ciao! :)

[jayc3399]

Has anyone come up with a non cumbersome way to do this in Excel 2007?

[AgingMiser]

I would like to know how to write a script like the one you suggest!

 

Ciao! :)

 

If I knew what to write the script in (whatever runs a macro would probably do, but I don't know that), I could easily set up a loop that took an input, searched for said input and returned the object #. Essentially, it would look something like this. The most tedious part would be setting up a method to produce the URL (easy if done by hand, but you have to go through multiple strings before you can convert an item name to a search query URL) - unless, of course, the macro could just go and search a string. After that, the code would identify the link and copy the URL, then reverse the first method to isolate the item number.

 

Would probably take me a couple hours to be honest, simply because of the tedious nature of the necessary work.

[Myu]

This isn't working for me. I get a table that i can't change, so i can't clean it up. I can't use Index for some reason, and i can't use the prices to make a calculation either because GEdb writes it like "6,357" instead of "6357".

[jayc3399]

How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign?

[Singh]

how do post this as a picture but still have it change prices?

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

[Grimy_Bunyip]

Has anyone come up with a non cumbersome way to do this in Excel 2007?

yes, there is one somewhere. Sorry I can't look it up for you though. It's either on TIP forums or zybez

i just did a search on zybez and it's not there though, so try searching the guides section for "excel"

 

This isn't working for me. I get a table that i can't change, so i can't clean it up. I can't use Index for some reason, and i can't use the prices to make a calculation either because GEdb writes it like "6,357" instead of "6357".

that is a strange bug that happens for some numbers, but not for others

what i like to do is apply the following code to the number:

rounddown(1.000000000001*number)

 

it's probably not the most efficient way to handle the issue, but it handles the issue :P

 

how do post this as a picture but still have it change prices?

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

you'd have to learn how to make your own websites, which is out of my scope :)

 

How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign?

sign(number)

if the number is positive it returns 1

if the number is negative, it returns -1

 

the importhtml should import the price changes in GE as positive and negative values

 

if this doesn't work you could apply a substitution chain

but that would be ridiculously cumbersome beyond all imagination :)

 

then again there's probably other methods even beyond that

[Singh]

Hey guys, I'm trying to get Clean Torstol but i just cant get it. The problem is that its over 10k and use a "k" as price. i read the guide many times but i still get either #error or #value

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

 

what do i change it too??

[AgingMiser]

how do post this as a picture but still have it change prices?

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

I assume you want to share it without letting anyone change it. Just share the document and restrict permissions when you share it, then give people the link.

 

How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign?

No offense, but I don't think what Morionic said would work. Use a string comparison command for that; it's in GDocs help somewhere.

[Capt_Davy]

So far my spreadsheet for farming is coming along great, however, I'm having the issue that googledocs likes to return things back with letters (ie. lantadyme seed, comes back as 32.1k, rather than 32100), which makes them unusable in calculations.

 

How can I make it so googledocs understands "k" to mean "1000"?

 

EDIT: my problem is the same as Ickdeep's.