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!
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.
How heres where the business starts.
youll need this…
Code:
=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…
Code:
=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.
Code:
=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=guam"), "table", 1),4,3)
so now for this… guam unf = 2, guam seed = 3, clean guam (what we want) = 4… etc
Lets paste it into the cell…
And then press enter…
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.
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…
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…
Code:
=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
Code:
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.
Code:
=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.
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.
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.
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)
Then select conditonal formatting
We now have this.
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.
now I like the financial formatting, so ill switch the number to that.
Negative numbers are shown in ( ) to note a loss.
Now… to publish it so it looks nice.
Click file, then Publish to the web...
Now we got a link… but no one can see it!
so now make it public…
File then click Share (first one)
then we get this...
Click Change and then select Public.
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/...=0&output=html --- Orb Charging Profits
https://docs.google.com/spreadsheet/...UE&output=html --- Summoning Costs
https://docs.google.com/spreadsheet/...lE&output=html --- Unf Production costs