Sign In »
Register Now!
Help
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
To be able to use Google Docs, you will first need a Gmail account, or Google Email. Now your first step is to 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. Now that you have Google Docs open, we need to start the process. Start off by making a new spreadsheet, this is what I use to make the calculator, or for the purpose of this guide, simply extracting prices off the database.
ImportHtml
First off, what I do is start with this formula, to extract the data from the GE database.
ImportHtml takes the HTML off of the specified site.
Now there are three parts that are going into the parenthesis for Importing the Html. The Url, the type of Html to look for, and the index of the html.
The Url is where it is extracting the data from. Make sure it is in quotations or it will not work. The link you see me using in the code above is the link that you will always be using for extracting GE prices although the query will change.
The type of Html for it to look for is next. There are only two options for this, table, or list. List will just find things that are in the list format, all you will get from this is the Drop down menus at the top of the screen. We will be using "table". This will look for tables within the page, which is what the prices are contained within. This also has to be within quotation marks or it will not work.
The index is the third thing, which is where you see the number 2. This 2 is used because the table where the prices are found is the second table within the page. For other things, you may need to use a different index but just for extracting GE prices, you should always just be using 2.
When I entered this whole code into excel, in cell A1, I came out with something that looked like this.
As you can see, that's more information then we need, so we have to narrow it down.
=ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2)ImportHtml takes the HTML off of the specified site.
Now there are three parts that are going into the parenthesis for Importing the Html. The Url, the type of Html to look for, and the index of the html.
The Url is where it is extracting the data from. Make sure it is in quotations or it will not work. The link you see me using in the code above is the link that you will always be using for extracting GE prices although the query will change.
The type of Html for it to look for is next. There are only two options for this, table, or list. List will just find things that are in the list format, all you will get from this is the Drop down menus at the top of the screen. We will be using "table". This will look for tables within the page, which is what the prices are contained within. This also has to be within quotation marks or it will not work.
The index is the third thing, which is where you see the number 2. This 2 is used because the table where the prices are found is the second table within the page. For other things, you may need to use a different index but just for extracting GE prices, you should always just be using 2.
When I entered this whole code into excel, in cell A1, I came out with something that looked like this.
As you can see, that's more information then we need, so we have to narrow it down.
Index
You see here that the first column is empty, the second column is the item name, and the third column is prices. Now prices is what we care about. The price of the thing that we want, Willow logs, is in Row 2, column 3. So now we will use "Index" to tell it to only take the information from Row 2, Column 3.
Index is a code that tells what row and column to take information from. This way instead of getting all the data like in the picture above, we can just get one specific cell out of that, the cell that contains the price of whatever you are looking up, in this case, Willow Logs.
After you add "Index" it will look like this:
So what you did was added "Index(" before Importhtml, and ",2,3)" after it.
Now the results will just be "18" the price of willow logs. Sometimes, the first option when you look up an item will not be the item you are looking for, in which case you will have to change the row. If it is the second item, just change it to ",3,3" or "4,3" for the third item, and I think you get it by now. So now you have looked up the exact price of an item and put it into a cell of your spreadsheet but we are not completely done yet.
Index is a code that tells what row and column to take information from. This way instead of getting all the data like in the picture above, we can just get one specific cell out of that, the cell that contains the price of whatever you are looking up, in this case, Willow Logs.
After you add "Index" it will look like this:
=Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2),2,3)So what you did was added "Index(" before Importhtml, and ",2,3)" after it.
Now the results will just be "18" the price of willow logs. Sometimes, the first option when you look up an item will not be the item you are looking for, in which case you will have to change the row. If it is the second item, just change it to ",3,3" or "4,3" for the third item, and I think you get it by now. So now you have looked up the exact price of an item and put it into a cell of your spreadsheet but we are not completely done yet.
Concat
Now there is one thing you can do to make it even easier. With my calculators, I don't want to manually enter every item, so I just have the code take the item that is put in after "query=" from within my spreadsheet.
To do this, I use "Concat"
Concat takes the information from a cell within your spreadsheet, and inputs it into your code. We will be using it so that instead of typing out "Willow Logs" this code will see Willow Logs typed out in A2 and put willow logs directly into the link. So now, let's change the code once more.
The code should now look like this:
As you can see I added "(Concat" after Importhtml and ",A2" after query=" What this does is tells it to find what to put in that position is the Informaton found in A2. If the price you are looking up is in A3, then just change to A3.
To do this, I use "Concat"
Concat takes the information from a cell within your spreadsheet, and inputs it into your code. We will be using it so that instead of typing out "Willow Logs" this code will see Willow Logs typed out in A2 and put willow logs directly into the link. So now, let's change the code once more.
The code should now look like this:
=Index(ImportHtml(Concat("http://itemdb-rs.runescape.com/results.ws?query=",A2), "table", 2),2,3)As you can see I added "(Concat" after Importhtml and ",A2" after query=" What this does is tells it to find what to put in that position is the Informaton found in A2. If the price you are looking up is in A3, then just change to A3.
Making a calculator
Now that you know how to extract information from the GE database, I will give you a little bit more help on making a calculator like my Gp / Xp ones.
First off you need to make the headings, which will basically be what the Raw product is, what the finished product is, how much experience you gain per, how much it costs, and how much it sells for(Use your knowledge from the above reading to do these), and then how much you lose/profit per, and then Gp/Xp.
Now that you have the headings, you need to fill them. Start off by filling up what the Raw and finished product is. Try and get the names exactly what they are so that it will extract the right prices. Then fill in the experience, there is no secret trick to this, just go on the Manual and find how much exp it gives.
The next two will be the cost of the raw, and the selling price of the finished, in these two you will fill in the code you learned earlier.
Next up is the Profit/Loss per. For this just subtract the Finished product from the Raw Product, or vice versa, depending how you want to do it. Now for this, don't just input the prices or it won't auto update that, you need to input the cells so it will always take the data from there no matter how much it changes. So put something like "=F2-E2" and that will give you the result you're looking for.
The last one is the Gp / Xp per. For this you are dividing your profit/loss by the experience gained. To do this you just do something like =G2/C2. Once you do this, the results might not always be pretty, you will probably have a decimal with 20+ digits. To fix this we need to round it, I personally round to 3 decimals. The code I use for this is: =ROUND(G2/C2,3) which divides G2 by C2 and then rounds it to 3 decimals.
First off you need to make the headings, which will basically be what the Raw product is, what the finished product is, how much experience you gain per, how much it costs, and how much it sells for(Use your knowledge from the above reading to do these), and then how much you lose/profit per, and then Gp/Xp.
Now that you have the headings, you need to fill them. Start off by filling up what the Raw and finished product is. Try and get the names exactly what they are so that it will extract the right prices. Then fill in the experience, there is no secret trick to this, just go on the Manual and find how much exp it gives.
The next two will be the cost of the raw, and the selling price of the finished, in these two you will fill in the code you learned earlier.
Next up is the Profit/Loss per. For this just subtract the Finished product from the Raw Product, or vice versa, depending how you want to do it. Now for this, don't just input the prices or it won't auto update that, you need to input the cells so it will always take the data from there no matter how much it changes. So put something like "=F2-E2" and that will give you the result you're looking for.
The last one is the Gp / Xp per. For this you are dividing your profit/loss by the experience gained. To do this you just do something like =G2/C2. Once you do this, the results might not always be pretty, you will probably have a decimal with 20+ digits. To fix this we need to round it, I personally round to 3 decimals. The code I use for this is: =ROUND(G2/C2,3) which divides G2 by C2 and then rounds it to 3 decimals.
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.
Back to top
what a lame April Fools
