Results 1 to 17 of 17

Thread: Spreadsheets with LIVE GE Prices!

  1. #1
    Join Date
    Jan 2012
    Posts
    1,596
    Mentioned
    78 Post(s)
    Quoted
    826 Post(s)

    Default Spreadsheets with LIVE GE Prices!

    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.
    Code:
    =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.

    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
    Last edited by Turpinator; 02-03-2013 at 09:01 AM.

  2. #2
    Join Date
    Mar 2007
    Posts
    5,125
    Mentioned
    275 Post(s)
    Quoted
    901 Post(s)

    Default

    Wow, amazing work here!
    Thanks for the information

  3. #3
    Join Date
    Sep 2012
    Location
    Australia.
    Posts
    839
    Mentioned
    16 Post(s)
    Quoted
    225 Post(s)

    Default

    Good guide, just needs abit of a cleanup to look better.

    Thanks for this.

  4. #4
    Join Date
    Jan 2012
    Location
    Runescape News and General & Skill Guides
    Posts
    2,544
    Mentioned
    37 Post(s)
    Quoted
    545 Post(s)

  5. #5
    Join Date
    Jan 2012
    Posts
    1,596
    Mentioned
    78 Post(s)
    Quoted
    826 Post(s)

    Default

    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.

  6. #6
    Join Date
    Mar 2006
    Location
    Belgium
    Posts
    3,564
    Mentioned
    111 Post(s)
    Quoted
    1475 Post(s)

    Default

    Just what I needed! cheers mate

    Creds to DannyRS for this wonderful sig!

  7. #7
    Join Date
    Feb 2013
    Posts
    15
    Mentioned
    0 Post(s)
    Quoted
    3 Post(s)

    Default

    might aswell start trying these things, will post if they work well ;D

  8. #8
    Join Date
    Dec 2013
    Posts
    5
    Mentioned
    0 Post(s)
    Quoted
    0 Post(s)

    Default

    Nice looking guide , for sure reading this take awhile
    Thanks!

  9. #9
    Join Date
    Feb 2014
    Posts
    47
    Mentioned
    1 Post(s)
    Quoted
    26 Post(s)

    Default

    Amazing guide

  10. #10
    Join Date
    Jan 2012
    Posts
    1,596
    Mentioned
    78 Post(s)
    Quoted
    826 Post(s)

    Default

    Quote Originally Posted by Anish View Post
    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.

  11. #11
    Join Date
    Jul 2014
    Posts
    20
    Mentioned
    0 Post(s)
    Quoted
    2 Post(s)

    Default

    Wow - this will actually prove very useful for me. Thankyou Much appreciated

  12. #12
    Join Date
    Apr 2012
    Posts
    31
    Mentioned
    1 Post(s)
    Quoted
    11 Post(s)

    Default

    This is awesome! I've been trying to figure out the best way to add GE info to my spreadsheets.

  13. #13
    Join Date
    Jan 2012
    Posts
    37
    Mentioned
    0 Post(s)
    Quoted
    17 Post(s)

    Default

    This is awesome!!

  14. #14
    Join Date
    May 2012
    Posts
    31
    Mentioned
    0 Post(s)
    Quoted
    12 Post(s)

    Default

    This is really cool, gonna make a spreadsheet with everything I've been flipping!

  15. #15
    Join Date
    Oct 2014
    Posts
    43
    Mentioned
    1 Post(s)
    Quoted
    25 Post(s)

    Default

    rsca.PNG
    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?

  16. #16
    Join Date
    Jan 2012
    Posts
    1,596
    Mentioned
    78 Post(s)
    Quoted
    826 Post(s)

    Default

    Quote Originally Posted by wowgnomes View Post
    rsca.PNG
    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.

  17. #17
    Join Date
    Feb 2007
    Location
    Alberta, Canada
    Posts
    4,615
    Mentioned
    50 Post(s)
    Quoted
    429 Post(s)

    Default

    Quote Originally Posted by wowgnomes View Post
    rsca.PNG
    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.

    Scripts: Edgeville Chop & Bank, GE Merchanting Aid
    Tutorials: How to Dominate the Grand Exchange

    Quote Originally Posted by YoHoJo View Post
    I like hentai.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •