Posts tagged grand exchange

Contents

• 1.0 [ Introduction ]
  
• 2.0 [ Getting Started ]
  
• 3.0 [ Using ImportHTML to import GE Prices ]
  
• 4.0 [ Extracting One Item at a Time ]
  
• 5.0 [ Using the GE Prices to Make a Spreadsheet ]
  
• 6.0 [ Concatenate - Another Useful Function ]
  
• 7.0 [ Dealing with Ks and Ms ]
• 8.0 [ Final Words and Credits ]
  

1.0 [ Introduction ] 

  You might also want to check out Grimy Bunyip’s Spreadsheets.  Grimy is a pro at making Runescape spreadsheets on Google docs.  I often use his spreadsheets when looking for the best way to train a particular skill.  For instance, by looking at his spreadsheets I found that making air battlestaffs is a great way to train Crafting and cleaning grimy herbs is a great (and profitable) way to train Herblore.
                   

                                                           How to Turn this into something beautiful.
                                                                ======================

2.0 [ Getting Started ]

Start off by doing the following:

2A) Head to Google Docs and sign in.  If you don’t have a Gmail account, you will see a button somewhere on the bottom of the screen that will lead you to a page where you can create an account.  Once logged in, on the top left part of the page press ‘CREATE NEW’ and then ‘SPREADSHEET’ (see screenshot below).

                                                              Make an account and then create a new spreadsheet.
                                                                              ======================

3.0 [ Using ImportHTML to Import GE prices]

  After you’ve gotten that all straightened out, it’s time to learn how to use Google Doc’s function ImportHTML which, unavailable in Microsoft Excel and OpenOffice Calc, is the key to extracting prices from the Grand Exchange Database:

3A) The basic ImportHTML code we’ll be using for this guide goes like this: 

=ImportHTML("LINK HERE", "table", 2)

The “table”, 2 part is necessary because otherwise it would extract everything on the page rather than only the items and their prices.  Go to the Grand Exchange Database and search for an item.  Copy the link and insert it into the code where it says “LINK HERE”.  Put the entire code, then, into one of the cells on your Google Doc spreadsheet (like A1 or A2).  Repeat this until you have imported all the items you need for your spreadsheet.

>> Let’s trying searching “Dragonhide” in order to make a spreadsheet for my Tanning Dragonhides Guide.  Notice that other items come up besides dragonhides in the search.  That’s okay (we’ll learn how to deal with that more later on) but, for now, just to narrow down the items a bit, I’ll put the price range between 1-5K.  The final link, which I will insert into A2, is http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&  price=1000-5000 and, when added to the code, becomes

=ImportHTML("http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000", "table", 2)

                               You now have a list of items that will be updated each time the GE updates.
                                                                                ======================

4.0 [ Extracting One Item at a Time ]

  By importing items the way mentioned above, you’ll almost always end up with some extra unwanted items (like Black D’hide Vambraces or Black Spiky Vambraces above).  It’s  easy to get away with this problem by simply right-clicking the unwanted-item column and pressing ‘hide’ or simply not publishing that sheet but you can also just import items 1-by-1.  Whether or not you do it this way is up to you (I personally don’t usually bother) but here’s how to import items 1-by-1, anyway: 

4A) Add Index( before the code and , ITEM COLUMN NUMBER HERE) after the code. It  looks like this in the end:

=Index(ImportHTML("LINK HERE", "table", 2), ITEM COLUMN  NUMBER HERE)

Fill in the LINK HERE and the ITEM COLUMN NUMBER (explained below) and copy and paste into a cell.  Repeat this for each item until you have all the items you need.

                                                                                                    Importing Items one-by-one

4B) To figure out the column number for, say, Red Dragonhides just count down from the first column until you reach Red Dragonhides.  If, like before, you search ‘Dragonhide’ and narrow down the options to prices between 1-5k, Red Dragonhides will be in the fifth column (see image below) so replace ITEM COLUMN NUMBER HERE with 5.  The link is, of course, figured out the same way as previously – in this case, it’s  http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000.  The final code, then (see above screenshot), is: 

=Index(ImportHTML("http://services.runescape.com/m=itemdb_rs/results.ws?query=dragonhide&price=1000-5000", "table", 2), 5)

                                Start counting at the ‘Item – Current Price – Change Today (gp)’ Column
                                                                       ======================

5.0 [ Using the GE Prices to make a Spreadsheet ]

5A) First off, import all dragonhides, tanned and untanned using the ImportHtml function (however you choose to do it: 1-by-1 or altogether) and then create a new sheet by pressing the ‘+’ on the bottom right of the same spreadsheet page. 

  Have your calculations on a separate page from the one where you imported the prices

5B) The new sheet should tell us four things: (1) Item name.  (2) Profit per hide. (3) Profit per trip.  (4) Profit per hour.  Write these four things at the top of each column (i.e. type ‘Item Name’ in A1, ‘Profit Per Hide’ in B1 and so forth).  Then simply write the names of the dragonhides in four different cells below the ‘Item Name’ column or, rather than writing it out, you could also just put CELL=CELL WHICH SAYS DRAGONHIDE NAME  HERE.  It’s really very simple: just click the cell you want, type in =, and then find and  choose the cell whose contents you want it to mirror. 

               
      On the other sheet “Calculations” on A2 it will now say Green Dragonhide

      
                 However you decide to do it, just make sure it ends up looking something like this.

5C) To figure out the ‘Profit per hide’ all you have to do is subtract the price of the untanned dragon hide + the cost of tanning the dragonhide (20gp) from the price of the corresponding tanned dragonhide.  The code in the cell to the right of each dragonhide name should end up looking something like this, then: =CELL THAT CONTAINS PRICE OF TANNED DRAGONHIDE-CELL THAT CONTAINS PRICE OF UNTANNED DRAGONHIDE-20. 
       
           
                       Notice that there are some rows of cells you can’t see.  This is because I hid them.

         
                                     It should end up looking like this on the other sheet.

5D) To figure out the profit per trip just multiply 27 (the amount you can do per trip) by the cell that shows the profit per hide i.e. C2=27*B2.  Because the formula is the same for all four hides, you can simply copy the first cell and paste it into the other three. 

     
                                                    Don’t forget that you can just copy and paste

5E) To figure out the ‘Profit Per Hour’ simply multiply the corresponding ‘Profit Per Trip’ by the number of trips you can do in an hour – which in this case is 110 i.e. D2=110*C2.

       
                       Okay, that’s almost everything! The only thing that’s missing is a little color.

5F) It’s very easy (and worthwhile) in Google Docs to add coloring and change font type and size.  Simply scroll over the cells you want to color and choose the text type, text color and background color for all of them. 

         
                               Here’s how I colored mine. Ain’t it purty? How will you color yours?

5G) Now you just need to share your spreadsheet with the world.  Click the ‘Share’ button in the near-top right corner and share it whatever way you want.  You can even embed a spreadsheet on your website or blog like I’ve done and it’ll give your readers a good reason to come back every day.  Make sure to post a link to your spreadsheet(s) in a comment on this post, too – I’d love to see it!

                  
        Be generous and you will be broccoli.  Help others and they will eat you….
                                   ======================

6.0 [ Concatenate - Another Useful Function ]

6A) The basic concatenate function code is this:

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),2,3)

Looks complicated, right?  All you need to know, though, is that, with this code, whatever you put in A2 will be put in place of where it now says A2 in the code.  So if you type ‘Green Dragonhide’ into A2 the code will now be

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",Green Dragonhide), "table", 2),2,3)

The new code will tell you what the price of Green Dragonhides are.

                                         Yo-ho-ho it’s magic, you know! Never believe it’s not so!

6B) Fill Column B with the basic Concatenate code by first putting the code into B2 and then clicking the bottom right corner of that cell and pulling down (this is a very useful thing to know, so don’t forget!).  Then all you have to do is write the names of items in Column A and you’ll quickly have the item price.


                             “Wait a second… Red D-leather doesn’t cost 222gp each!”  Yes, I’ll get to that

6C) Occasionally you’ll run into a small problem when using Concatenate.  The problem is that sometimes (pretty rarely) when you search an item name it doesn’t show up in the second column so you end up getting the price of some other item that came up in that column instead.  This is the case for Red D-leather.  To fix the problem, as before, replace the 2 in the code “Table”, 2),2,3) with the actual column number:

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),5,3)

  By searching Red D-leather on the Grand Exchange Database we learn that it comes up in the fifth column (see 4B screenshot  above which demonstrates the same procedure, in case you’ve forgotten).  So we replace the 2 in the code with a 5 and that’s that.

                                               It can be annoying sometimes but it’s not too bad.

6D) If you also want to see how much the item in the ‘A’ column changed in price today just replace the final 3 in the code “Table”, 2),2,3) with a 4 and that will do it because when you search for an item on the Grand Exchange Database it is the fourth column that shows the Change in Price.

=Index(ImportHtml(Concatenate("http://services.runescape.com/m=itemdb_rs/results.ws?query=",A2), "table", 2),2,4)

                                    All you have to do is count.  I did it for you, just in case you don’t know how.

                                                          Pretty nifty, huh?
                                                                   ======================

7.0 [ Dealing with Ks and Ms ]

   Any item worth 10,000 gold pieces or more will show up as 10k.  Any item worth 1,000,000 gold pieces or more will show up as 1m.  Google Docs doesn’t recognize that k stands for three zeros and that m stands for six so the calculations won’t work properly (I know.  Annoying, right?).  To fix this problem copy and paste the following code into whatever cell you want the fixed price to show up in:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( FAULTY CELL; "+";"") ; "k" ;"");"m";"") *IF(OR( RIGHT(FAULTY CELL;1)="k" ;RIGHT(FAULTY CELL;1)="m") ;sum(Index((right(FAULTY CELL)={"k","m"})*10^{3,6}));1)

If the “faulty cell” is B2, replace all the FAULTY CELLs with B2.  There are four altogether.

                                   If you can understand that code you can understand the meaning to life
                                                                                   ======================

8.0 [ Final Words and Credits ]

   Much of the credit for this post goes to Brainymidget, the guy whose post on tip.it  forums taught me the basics of creating Runescape spreadsheets.  I was originally just going to link you to his post but I saw it was a bit outdated and missing some stuff I thought was important so I decided just to make my own.