PDA

View Full Version : Spreadsheets with LIVE GE Prices!



Turpinator
02-03-2013, 07:22 AM
Now, I realize this is quick and dirty, but i plan to improve on this slowly. the text isnt too in detail, but the main thing people need from here is just the 'get price' code sort of thing...

How to make spreadsheets with LIVE GE Prices
Soo… you want to make a spreadsheet showing profits but the GE prices keep changing? Well let me introduce you to google docs. (now called google drive or something)
https://drive.google.com/
Go there. Sign in with your google account or make a new one!
http://i.imgur.com/FzbxFPO.png
Click the create button and select spreadsheet
Now… what kind of spreadsheet are we making?
lets do a herblore one… making unfinisheds? Making full ones? How about… Buying vials of water and adding clean herbs. Simple enough right?

So now I started out and placed some labels and stuff.
http://i.imgur.com/6zGLUI5.png

How heres where the business starts.
youll need this…

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=ITEM+NAME+HERE"), "table", 1),NUMBER+1,3)
so it actually looks like this…

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=bronze+claw"), "table", 1),2,3)
this would get the first item on the page when you search for bronze claw, which is… a bronze claw.
If something were further down you will need to increase the second last number to the row number PLUS 1. (that’s why its 2 and not 1)
Lets enter the first one. Guam.

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=guam"), "table", 1),4,3)
http://i.imgur.com/59MCJsD.png
so now for this… guam unf = 2, guam seed = 3, clean guam (what we want) = 4… etc
Lets paste it into the cell…
http://i.imgur.com/jFwyHd2.png
And then press enter…
http://i.imgur.com/GWOgdfc.png

Pow. That’s exactly what the GE price says it is.
Now repeat for every item.
But turrrp! There are like 20 items on this list. And theres another row! THERES GOTTA BE A BETTER WAY!

well there is. Remember us writing the name of the item in the previous column? Well we will just use that string and put it in. so lets modify our existing guam cell code.

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A4), "table", 1),4,3)
Now see what I did there? It took 3 different strings, the first half of the url, the name of the item/the location of the name of the item and then the final half of the url (I don’t think you need it, but I always keep it there just incase)

Now… lets drag that down to each row.
(select the cell, then hover the lower right until a + appears, then click and hold and drag to the last row)
Repeat for the unfinished price.
now you get this…
http://i.imgur.com/yOKSu10.png
That all looks good. Except for the torstol one… how are we going to fix that?
Well lets use something called MID() which basically cuts the string/cell contents to just what we want.
First we will used find() to find the K then cut the cell off before that.
Soo. 17.8k – find(”k”,B19) = 5 (5th character)
now lets put that into the mid function.
MID(A19, find(”k”, B19,) - 1) = 12.6
Well we are getting closer.
So lets just mutiply that by 1000
MID(B19, find(”k”, B19,) - 1)*1000 = 12600
and that’s a winner.
Now Ill just add an IF statement in case the price drops below 10k and GE table doesn’t have a “k” in the price…

=if(find("k",B19) > 0, MID(B19,1,find("k",B19)-1)*1000,B19)
So now here.. if it finds a “k” in B19, it will split it up and turn it into a regular price, if not, it doesn’t do anything.
But now heres where it gets a little tricky… B19 is really
Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A19), "table", 1),4,3)
so… ill just go ahead and replace all the B19’s with that…

Ending up with this.

=if(find("k",Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A19), "table", 1),4,3)) > 0, MID(Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A19), "table", 1),4,3),1,find("k",Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A19), "table", 1),4,3))-1)*1000,Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=", A19), "table", 1),4,3))
its pretty long (that’s what she said), but it ges the job done…
Well… now that all that is done we have this.
http://i.imgur.com/VEV2lar.png

Now just to do a little math and we can get our gp/vial and gp/hr
Now… ill just make the price of a vial of water constant, but If you really want, you can use the GE prices again for that.
so our GP/vial for guam looks like this.

=C4-B4-15
sell for price - herb price - vial price = price/vial
Now to do this per hour… Ill estimate you can make 2.5k unf per hour, so just multiply price/vial by 2500.
http://i.imgur.com/VcrcsWP.png
Almost done… now to format our stuff so it looks better…
Seeing as some of this can go negative, I like to use conditonal formatting so its easy to see whats profitable.
To do this. Highlight a cell (or cells)
http://i.imgur.com/h2H5jGv.png
Then select conditonal formatting
We now have this.
http://i.imgur.com/FIV5q97.png
lets say if the value is less than 0, then make the text red. Otherwise if the value is greater than 0, lets make it green.
http://i.imgur.com/xNnJlt5.png
http://i.imgur.com/TWjoiVX.png
now I like the financial formatting, so ill switch the number to that.
Negative numbers are shown in ( ) to note a loss.
http://i.imgur.com/3uH4BNQ.png
Now… to publish it so it looks nice.
Click file, then Publish to the web...
http://i.imgur.com/lWh81i3.png

Now we got a link… but no one can see it!
http://i.imgur.com/YoEJa5r.png

so now make it public…
File then click Share (first one)
then we get this... http://i.imgur.com/fMiUKFm.png
Click Change and then select Public.
http://i.imgur.com/S4EFKyD.png
and then save.

and thats it. So. comment on how crappily this was written. Ill be improving it, but i just wanted to get it out there...
Jakee


So... Here you can see 3 ive made (including this one i just hastily made)
https://docs.google.com/spreadsheet/pub?key=0AmACHCdAKJ6ldENiVHdpeTZ2VXdueElXTUtzOHhQX 1E&single=true&gid=0&output=html --- Orb Charging Profits
https://docs.google.com/spreadsheet/pub?key=0AmACHCdAKJ6ldGMzajJiMUg1dGw1c09mZ1lXOExGc UE&output=html --- Summoning Costs
https://docs.google.com/spreadsheet/pub?key=0AmACHCdAKJ6ldGhCOWNfeElSNDhCZm1QZ25zY05se lE&output=html --- Unf Production costs

Justin
02-03-2013, 07:28 AM
Wow, amazing work here!
Thanks for the information

Chris!
02-03-2013, 07:37 AM
Good guide, just needs abit of a cleanup to look better.

Thanks for this.

Jake
02-03-2013, 08:31 AM
Oh damn
I'm gonna give this a better read and try when I wake up
Thank again :)

Turpinator
02-03-2013, 08:53 AM
Excellent... :)

When writing this i wasnt planning on the torstol price screwing everything up, so i spent most of the time making this trying to figure out how to convert it to an actual number... That part still isnt refined yet... i think it breaks on ones that dont have a 'k', but you would really only use it on ones that needed it.
also, looking at plans to 'rewrite'/add the importhtml function into a different pure/raw 'data' sheet because google docs limits the number of allowed importhtml functions (currently using them twice for each herb).

But anyway... Ill hopefully get this PoS in shape tomorrow. unless i have homework for monday... which i get the feeling i do.

Sjoe
08-25-2013, 03:00 PM
Just what I needed! cheers mate

noxehhh
03-06-2014, 08:58 AM
might aswell start trying these things, will post if they work well ;D

q8post
03-27-2014, 08:25 PM
Nice looking guide , for sure reading this take awhile :p
Thanks!

Anish
07-16-2014, 07:34 PM
Amazing guide

Turpinator
07-16-2014, 11:00 PM
Amazing guide

thanks. annnyway. checked all the spreadsheets. the summoning and pots ones were broken. fixed those up and should be good still. in case anyone still wants to use them. even if its just for reference.

Linked
09-06-2014, 10:56 AM
Wow - this will actually prove very useful for me. Thankyou :) Much appreciated

jarofhearts
11-17-2014, 12:14 AM
This is awesome! I've been trying to figure out the best way to add GE info to my spreadsheets.

TehCrazyBrit
11-22-2014, 11:39 AM
This is awesome!!

jrtsoget
01-13-2015, 09:37 PM
This is really cool, gonna make a spreadsheet with everything I've been flipping!

wowgnomes
01-22-2015, 02:11 AM
24846
it's small, but it's a start!

Really awesome guide, I'll definitively add to my spreadsheet as needed. Do you know if there is any way to track what the price was 1 day ago, 15 days ago, and 30 days ago?

Turpinator
01-22-2015, 02:35 AM
24846
it's small, but it's a start!

Really awesome guide, I'll definitively add to my spreadsheet as needed. Do you know if there is any way to track what the price was 1 day ago, 15 days ago, and 30 days ago?

you can use whatever http://runescape.wikia.com/wiki/Exchange:Jug_of_wine does. but i dont think theres a way to grab the info in spreadsheet.

3Garrett3
01-22-2015, 04:32 PM
24846
it's small, but it's a start!

Really awesome guide, I'll definitively add to my spreadsheet as needed. Do you know if there is any way to track what the price was 1 day ago, 15 days ago, and 30 days ago?

It's pretty simple to do with Simba, if you want an example see the code that Slacky wrote up for me in my MerchantAid script (in my sig).

Of course that won't help you get the data into Excel, but for getting the last X days of data I don't think there's an easier way than reading the page source. Maybe Excel can do that but I've never bothered much with automatic data linkage into Excel, just tested my algorithms manually exporting the data from Simba to Excel for a few items, then saw what happened in Simba with 100.