Jump to content

Welcome to Rune Tips, the first ever RuneScape help site. We aim to offer skill guides, quest guides, maps, calculators, informative databases, tips, and much more to help you get the most from the Massive Online Adventure Game, RuneScape, by Jagex Ltd © 2009.

Report Ad

Welcome to Forum.Tip.It
Register now to gain access to all of our features. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more. If you already have an account, login here - otherwise create an account for free today!
Photo

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


  • Please log in to reply
113 replies to this topic

#21
sven_bohikus
[ Display Name History ]

sven_bohikus

    Rat Meat

  • Members
  • 33 posts
  • Gender:Not Telling
  • Joined:25 February 2007
  • RuneScape Status:P2P
  • RSN:Svenne
  • RSN2:d4damager
  • Clan:Supreme Skillers
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! \<img src=/' />

#14340th to 99 magic!! w000t

#22
gda_scooby
[ Display Name History ]

gda_scooby

    Unicorn Horn

  • Members
  • 151 posts
  • Joined:24 March 2006
can any one make one that stores the prices historically against date, so eventually we would have more than 30 days info?



G
Posted Image

#23
brainymidget
[ Display Name History ]

brainymidget

    Retired TET Member

  • Clan Official
  • 826 posts
  • Gender:Male
  • Location:Case Western Reserve University
  • Joined:2 October 2006
  • RuneScape Status:None
  • RSN:Brainymidget
  • Clan:SoDB
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.
Posted Image
Posted Image

#24
primadog
[ Display Name History ]

primadog

    Varrock Guard

  • Members
  • 1,428 posts
  • Gender:Not Telling
  • Joined:9 July 2004
  • RuneScape Status:None

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

#25
primadog
[ Display Name History ]

primadog

    Varrock Guard

  • Members
  • 1,428 posts
  • Gender:Not Telling
  • Joined:9 July 2004
  • RuneScape Status:None
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

#26
noble_aloof
[ Display Name History ]

noble_aloof

    Varrock Guard

  • Members
  • 1,326 posts
  • Gender:Not Telling
  • Location:Dunder mifflin
  • Joined:27 January 2008
  • RuneScape Status:P2P
  • RSN:Noble Aloof
  • Clan:Fenghahe's Elite Investing Guild
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?
Staking: 4+ mil
Current Status: Training defense

#27
dotadanatic
[ Display Name History ]

dotadanatic

    Spider Egg

  • Members
  • 71 posts
  • Joined:15 December 2007

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

#28
brainymidget
[ Display Name History ]

brainymidget

    Retired TET Member

  • Clan Official
  • 826 posts
  • Gender:Male
  • Location:Case Western Reserve University
  • Joined:2 October 2006
  • RuneScape Status:None
  • RSN:Brainymidget
  • Clan:SoDB
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.
Posted Image
Posted Image

#29
dotadanatic
[ Display Name History ]

dotadanatic

    Spider Egg

  • Members
  • 71 posts
  • Joined:15 December 2007
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.

#30
Caromon
[ Display Name History ]

Caromon

    Goblin Armour

  • Members
  • 135 posts
  • Gender:Not Telling
  • Location:Canadian Rockies
  • Joined:4 September 2007
  • RuneScape Status:P2P
  • RSN:Admiral Caro
Wouldn't have been able to make (and keep up to date) my runecrafting guide without this guide! :thumbup:

#31
De_Lille_D
[ Display Name History ]

De_Lille_D

    Scorpion Pit

  • Members
  • 652 posts
  • Gender:Male
  • Location:Belgium
  • Joined:10 March 2006
  • RuneScape Status:P2P
  • RSN:De_Lille_D
  • RSN2:Quiet_Attack
  • Clan:None
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.... ... 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.

#32
FinsToTheLeft
[ Display Name History ]

FinsToTheLeft

    Chicken Feather

  • Members
  • 15 posts
  • Joined:2 January 2009
I would very much appreciate instructions on how to do this for Excel 2007, if anyone knows.

#33
yondergod22
[ Display Name History ]

yondergod22

    Goblin Armour

  • Members
  • 95 posts
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 :?
Posted Image
Posted Image

#34
Kaphias
[ Display Name History ]

Kaphias

    FL380

  • Members
  • 3,399 posts
  • Gender:Male
  • Location:Alaska
  • Joined:9 October 2006
  • RuneScape Status:Retired
  • RSN:Matt91493

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.
Posted Image
8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/08
4,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010
Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009

#35
yondergod22
[ Display Name History ]

yondergod22

    Goblin Armour

  • Members
  • 95 posts
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....d=0&output=html
Posted Image
Posted Image

#36
yondergod22
[ Display Name History ]

yondergod22

    Goblin Armour

  • Members
  • 95 posts
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.
Posted Image
Posted Image

#37
epicnuts
[ Display Name History ]

epicnuts

    Goblin Armour

  • Members
  • 97 posts
  • Gender:Male
  • Location:Belgium
  • Joined:30 October 2008
  • RuneScape Status:P2P
  • RSN:zoomkey
  • Clan:Zeitphasm

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.'
Posted Image

#38
yondergod22
[ Display Name History ]

yondergod22

    Goblin Armour

  • Members
  • 95 posts
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.
Posted Image
Posted Image

#39
brainymidget
[ Display Name History ]

brainymidget

    Retired TET Member

  • Clan Official
  • 826 posts
  • Gender:Male
  • Location:Case Western Reserve University
  • Joined:2 October 2006
  • RuneScape Status:None
  • RSN:Brainymidget
  • Clan:SoDB
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.
Posted Image
Posted Image

#40
GreenFire63
[ Display Name History ]

GreenFire63

    Chicken Feather

  • Members
  • 9 posts
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?
Billionaire status as of 7/19/2009




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users