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
116 replies to this topic

#1
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
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.



How to use Google Docs




ImportHtml




Index




Concat




Making a calculator




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.

Posted Image
Posted Image
Posted Image

#2
runite_ore
[ Display Name History ]

runite_ore

    Chicken Feather

  • Members
  • 20 posts
  • Joined:11 December 2006
  • RSN:ggg0o0
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!
Posted Image
1113 total. Always f2p.

#3
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
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.
Posted Image
Posted Image

#4
thaa
[ Display Name History ]

thaa

    Chicken Feather

  • Members
  • 1 posts
  • Joined:6 August 2006
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:

Posted Image



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

#5
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
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
Posted Image

#6
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 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.
Posted Image
Posted Image

#7
mister_moocky
[ Display Name History ]

mister_moocky

    Ghost Cloak

  • Members
  • 1,952 posts
  • Gender:Male
  • Location:Alberta, Canada
  • Joined:9 June 2008
  • RuneScape Status:None
  • RSN:lMl MrMoocky

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

#8
jjjon123
[ Display Name History ]

jjjon123

    Warrior of Redwall

  • Members
  • 1,170 posts
  • Gender:Male
  • Location:SoCal W00t
  • Joined:11 June 2007
  • RuneScape Status:P2P
  • RSN:jjjon123
  • RSN2:Kirbybakatya
I made some F2P high alch sheets real quick... Check'em out :)





Armor:

http://spreadsheets.... ... WhBqpWmUaQ



Jewelry & Sets:

http://spreadsheets.... ... UzqFLvLwfw
Posted ImagePosted Image
Posted ImagePosted Image
Posted ImagePosted Image
Posted ImagePosted Image
Posted Image1,000 F2P Total Level Reached 10/8/10 !



WOT WOT!

☉.☉☂

#9
fbswift
[ Display Name History ]

fbswift

    Rat Meat

  • Members
  • 42 posts
  • Joined:27 August 2008
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
Posted Image

#10
knight10071
[ Display Name History ]

knight10071

    Bear Fur

  • Members
  • 410 posts
  • Gender:Male
  • Location:Location doesn't matter, destination does!
  • Joined:8 January 2006
  • RuneScape Status:P2P
  • RSN:Aurei Animus
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?
Posted Image

#11
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
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.
Posted Image
Posted Image

#12
Sentry_Wolf
[ Display Name History ]

Sentry_Wolf

    Follower Of The Crate

  • Members
  • 1,199 posts
  • Gender:Not Telling
  • Joined:4 December 2006
  • RuneScape Status:None
Thank you Brainy!! I can have a quick and easy sheet for my herblore guide. :) <3:



http://spreadsheets.... ... iureEDFSMw

Hehe!



This guide rocks!!! <3:

#13
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

Thank you Brainy!! I can have a quick and easy sheet for my herblore guide. :) <3:



http://spreadsheets.... ... 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.
Posted Image
Posted Image

#14
Sentry_Wolf
[ Display Name History ]

Sentry_Wolf

    Follower Of The Crate

  • Members
  • 1,199 posts
  • Gender:Not Telling
  • Joined:4 December 2006
  • RuneScape Status:None
Thanks, heres the right link now. :)



http://spreadsheets.... ... iureEDFSMw

#15
llcoolguy972
[ Display Name History ]

llcoolguy972

    Skeleton Shield

  • Members
  • 1,024 posts
  • Joined:28 August 2005
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.

#16
rsInvestorr
[ Display Name History ]

rsInvestorr

    Rat Meat

  • Members
  • 33 posts
  • Joined:12 January 2009
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.

#17
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 don't think that it's public information for anything except the top 100.
Posted Image
Posted Image

#18
PatrickMc
[ Display Name History ]

PatrickMc

    Chicken Feather

  • Members
  • 1 posts
  • Joined:26 March 2009
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

#19
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
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.
Posted Image
Posted Image

#20
primadog
[ Display Name History ]

primadog

    Hobgoblin Killer

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




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users