brainymidget Posted February 7, 2009 Share Posted February 7, 2009 Google Docs is essentially like Microsoft Office, and the spreadsheet form which we will be using is essentially like Excel, but it has a helpful feature which is that it can extract data from the internet(Although I recently found out Excel can as well but I haven't found a way to do it as efficiently so I'm sticking with Google Docs.) 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. [hide=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.[/hide] [hide=ImportHtml]First off, what I do is start with this formula, to extract the data from the GE database. =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.[/hide] [hide=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: =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.[/hide] [hide=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: =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.[/hide] [hide=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.[/hide] 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. Link to comment Share on other sites More sharing options...
runite_ore Posted February 7, 2009 Share Posted February 7, 2009 When i extract the price of runite ore, it returns 16.1k and the "k" makes the spreadsheet fail to calculate the cost of smelting runite bar. What can i do to make it calculate correctly? Please help! 1113 total. Always f2p. Link to comment Share on other sites More sharing options...
brainymidget Posted February 7, 2009 Author Share Posted February 7, 2009 Hmm, I honestly don't know. I'll take a look into it and tell you if I find out how to make it work. Link to comment Share on other sites More sharing options...
thaa Posted February 7, 2009 Share Posted February 7, 2009 Hello, I tried creating formula for parsing the "m" and "k" prices. The formula would be very big if I would have to merge it with the importhtml formula, so the value loaded from the price list is in D2. =if(iferror(find("m",D2),false),left(D2,find("m",D2)-1)*1000000,if(iferror(find("k",D2),false),left(D2,find("k",D2)-1)*1000,D2)) here is a snapshot: BTW: Thank you for this guide. Really great idea. Link to comment Share on other sites More sharing options...
epicnuts Posted February 8, 2009 Share Posted February 8, 2009 I so love you I asked this to sven bohikus, but he didn't reply (he had a guide for tracking xp) realy, thanks for doing this Link to comment Share on other sites More sharing options...
brainymidget Posted February 8, 2009 Author Share Posted February 8, 2009 I so love you I asked this to sven bohikus, but he didn't reply (he had a guide for tracking xp) realy, thanks for doing this Ya, I've used svens guide for a while to track competitions within my community. I based this largely off that, I just had to ImportHtml instead of ImportData. Link to comment Share on other sites More sharing options...
mister_moocky Posted February 9, 2009 Share Posted February 9, 2009 When i extract the price of runite ore, it returns 16.1k and the "k" makes the spreadsheet fail to calculate the cost of smelting runite bar. What can i do to make it calculate correctly? Please help! I'm getting the same problem with the + signs on the price changes. It regonizes - as negatives, but the + is treated as a character, which messes it up. Hello, I tried creating formula for parsing the "m" and "k" prices. The formula would be very big if I would have to merge it with the importhtml formula, so the value loaded from the price list is in D2. =if(iferror(find("m",D2),false),left(D2,find("m",D2)-1)*1000000,if(iferror(find("k",D2),false),left(D2,find("k",D2)-1)*1000,D2)) I presume it's possible to make something that gets rid of + signs? quit Link to comment Share on other sites More sharing options...
jjjon123 Posted February 9, 2009 Share Posted February 9, 2009 I made some F2P high alch sheets real quick... Check'em out :) Armor: http://spreadsheets.google.com/ccc?key= ... WhBqpWmUaQ Jewelry & Sets: http://spreadsheets.google.com/ccc?key= ... UzqFLvLwfw 1,000 F2P Total Level Reached 10/8/10 ! [hide=Guides]Magic & Summoning Profit Spreadsheets! *UPDATES EVERY HOUR* (includes: High alch, Superheat, and Enchanting)4 BETTER alternatives to flesh crawlers[/hide] WOT WOT! ☉.☉☂ Link to comment Share on other sites More sharing options...
fbswift Posted February 12, 2009 Share Posted February 12, 2009 so if f2p then high alch rune longswords? thats pretty helpful im going to go and buy 50k rune longswords then tell everyone about this Link to comment Share on other sites More sharing options...
knight10071 Posted February 15, 2009 Share Posted February 15, 2009 Thanks a lot! I was kinda looking for this and I've now updated my MTK sheets with this method of drawing prices out. Now, I've got one question: how often does this update? Every 24 hours, or every time someone opens it? Link to comment Share on other sites More sharing options...
brainymidget Posted February 15, 2009 Author Share Posted February 15, 2009 Well with Hi-scores, which should be the same as extracting from the GE database, it updates every 1 hour, or whenever I refreshed all the data in my spreadsheet, but I never really do that so it's pretty much every 1 hour. Link to comment Share on other sites More sharing options...
Sentry_Wolf Posted February 15, 2009 Share Posted February 15, 2009 Thank you Brainy!! I can have a quick and easy sheet for my herblore guide. :) http://spreadsheets.google.com/pub?key= ... iureEDFSMw Hehe! This guide rocks!!! Link to comment Share on other sites More sharing options...
brainymidget Posted February 15, 2009 Author Share Posted February 15, 2009 Thank you Brainy!! I can have a quick and easy sheet for my herblore guide. :) http://spreadsheets.google.com/ccc?key= ... FSMw&hl=en Hehe! This guide rocks!!! Glad to help, and by the way, the URL you posted links to the page where you edit the spread sheet. You don't have it set so that people can edit or view your document, so people can't view it. What you need to do is click Share > Publish as a web page, then it will give you the link to it. Link to comment Share on other sites More sharing options...
Sentry_Wolf Posted February 15, 2009 Share Posted February 15, 2009 Thanks, heres the right link now. :) http://spreadsheets.google.com/pub?key= ... iureEDFSMw Link to comment Share on other sites More sharing options...
llcoolguy972 Posted February 25, 2009 Share Posted February 25, 2009 Awesome guide. Made this ranged calc yesterday. By the way, if anyone needs to pull stat experience from the high scores the formula is: =Index(ImportHtml(Concat("http://hiscore.runescape.com/hiscorepersonal.ws?user1=",B1), "table", 1), 7,5). All you have to change is B1 to your user/cell where your user's in and the bolded 7, which is the row your skill is. Link to comment Share on other sites More sharing options...
rsInvestorr Posted March 23, 2009 Share Posted March 23, 2009 Any ides where I can find the data for total trade volumes? The data is there somewhere because they use it for the top 100 most traded items, but I would like to be able to poll the data for other items as well. Link to comment Share on other sites More sharing options...
brainymidget Posted March 23, 2009 Author Share Posted March 23, 2009 I don't think that it's public information for anything except the top 100. Link to comment Share on other sites More sharing options...
PatrickMc Posted March 26, 2009 Share Posted March 26, 2009 To extract prices and other numeric or non-numeric values from html tables, there is a very good script posted at http://www.biterscripting.com/install.html . Now, to take care of the k (1000) and m (1000000) notations, I would add the following at the end of that script. while ( { sen r -c "^;k^" $csv } > 0 ) sal -r -c "^;k^" ",000" $csv > null while ( { sen -r -c "^;m^" $csv } > 0 ) sal -r -c "^;m^" ",000,000" $csv > null Add the above before the echo $csv line in the script. I am using -c (case insensitive option) and -r (regular expression), because k and m may also appear as "K or "M" or " K" or " M", etc. Patrick Link to comment Share on other sites More sharing options...
brainymidget Posted March 26, 2009 Author Share Posted March 26, 2009 That doesn't work with GoogleDocs, GoogleDocs uses the same kind of code as Excel. And someone already posted how to convert the m's and the k's with Google Docs. Link to comment Share on other sites More sharing options...
primadog Posted April 1, 2009 Share Posted April 1, 2009 Why is all your query types =ImportHtml("http://itemdb-WOW.runescape.com/results.ws?query=Willow Logs", "table", 2) instead of =ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=Willow Logs", "table", 2) Oh wait... don't tell me... -.- what a lame April Fools All I learned in life, I learned on Tip.it Link to comment Share on other sites More sharing options...
sven_bohikus Posted April 15, 2009 Share Posted April 15, 2009 Hey cool! You guys did it! :thumbsup: I couldn't figure out how I was going to parse the GE database output, but I'm glad someone else did. Excellent work guys! \ #14340th to 99 magic!! w000t Link to comment Share on other sites More sharing options...
gda_scooby Posted April 16, 2009 Share Posted April 16, 2009 can any one make one that stores the prices historically against date, so eventually we would have more than 30 days info? G Link to comment Share on other sites More sharing options...
brainymidget Posted April 16, 2009 Author Share Posted April 16, 2009 I'm pretty sure there isn't a way to do that but if anyone wants to try it, feel free and if you do post on here so I can add the information to my guide, with credit given to the finder of course. Link to comment Share on other sites More sharing options...
primadog Posted April 16, 2009 Share Posted April 16, 2009 can any one make one that stores the prices historically against date, so eventually we would have more than 30 days info? G RuneCrypt already has something similiar here http://www.runecrypt.com/graph/11732 All I learned in life, I learned on Tip.it Link to comment Share on other sites More sharing options...
primadog Posted April 19, 2009 Share Posted April 19, 2009 Thanks again for this tutorial, I been putting this into good use: GE values for Crafting - Gems, Google Docs version Pikkupstix Pouch Trading shard/gp conversion rate, Google Docs version This is very useful, although I am not too sure whether google docs updates the web All I learned in life, I learned on Tip.it Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now