noble_aloof Posted April 20, 2009 Share Posted April 20, 2009 i'm finding tip.it extremely helpful for my return to runescape. first i find a farming timer, now i find this! very useful for my investments! thank you =D> question: is there a way that i can make it track prices over, say, 10 days if i reserve a line for each day? it would basically record the prices, then go to the next line for the next price change. is this possible? [size="5"][font="Georgia"][b]Staking:[/b][/font][font="Palatino Linotype"][color="#FF0000"][/color][color="#FFFF00"][/color][color="#00FF00"] 4+ mil[/color][/font] [font="Georgia"][b]Current Status:[/b][/font][font="Palatino Linotype"][color="#FF0000"][/color][color="#0000FF"] Training defense [/color][/font][/size] Link to comment Share on other sites More sharing options...
dotadanatic Posted May 7, 2009 Share Posted May 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.) Hmm...can you share with us how to do this with microsoft excel(nvm its not efficient)? Link to comment Share on other sites More sharing options...
brainymidget Posted May 10, 2009 Author Share Posted May 10, 2009 It's not extremely inefficient, I just find Google Docs a lot easier because you can copy and paste the code extremely easily and also with Google Docs, you can share the spreadsheets easier with friends. I have never personally tried doing it with Excel, but someone I know has a guide for it on a separate forums. Basically what you need to do is go to the Data tab in Excel and click "From web" and then I guess you have to find where the data is on the web and it will do something similar to what Google Docs does, just find the data in HTML and extract it. Link to comment Share on other sites More sharing options...
dotadanatic Posted May 10, 2009 Share Posted May 10, 2009 Thank you for the reply,brainy. I asked this because I have created several Excel-based calculators myself but I manually enter the data each day, spending up to half an hr each time. Microsoft Excel is much more flexible and nicer so I prefer to stick to Excel if I have a choice. Link to comment Share on other sites More sharing options...
Caromon Posted May 26, 2009 Share Posted May 26, 2009 Wouldn't have been able to make (and keep up to date) my runecrafting guide without this guide! :thumbup: Link to comment Share on other sites More sharing options...
De_Lille_D Posted June 20, 2009 Share Posted June 20, 2009 I find it easier to use ImportData to load the complete html file. This way I can use the link to the specific item, not the link to the search for the item. (In case the show order changes, your price will be shown wrong) This does however generate about 300 cells of text, so you might want to put it underneath your calculator or put in a separate sheet. - Code for ImportData : "=ImportData()" can be entered manually; I use this command based on the item number: "=CONCATENATE("http://itemdb-rs.runescape.com/viewitem.ws?obj=";)" - Code for price cell : "=value(MID(; 22; LEN()-21))" is the cell in which the market price is found; usually around the 219-235th row depending on the amount of cells above the importdata cell Do know you if there's a similar command in Excel (I've had to use a wizard to import data so far)? Is there a command that can import a specific line in the html-file (I think the market price is found on the 219th line). This would avoid having to load the entire file. Here's the file if it wasn't clear: http://spreadsheets.google.com/ccc?key= ... vRX0HfY7Ug PS: if you don't know the item number, search it on website GE and look at the link to the graph page; the item number is the number at the end of the link. I haven't made something that turns "k"s in 1000, but that shouldn't be too hard. RSN: De Lille D [P2P]- Visit my Advanced Farming Route Guide - Link to comment Share on other sites More sharing options...
FinsToTheLeft Posted June 23, 2009 Share Posted June 23, 2009 I would very much appreciate instructions on how to do this for Excel 2007, if anyone knows. Link to comment Share on other sites More sharing options...
yondergod22 Posted July 3, 2009 Share Posted July 3, 2009 Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :? Link to comment Share on other sites More sharing options...
Kaphias Posted July 3, 2009 Share Posted July 3, 2009 Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :? Copy the below code into a cell of your choice: =Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=ITEM NAME HERE", "table", 2),2,3) Go the the Grand Exchange page and find the exact name of the item you want to get data for. Copy the item's name and paste it over the "ITEM NAME HERE" text. The mid GE price for that item will appear in the box. 8,325th to 99 Firemaking 3/9/08 | 44,811th to 99 Cooking 7/16/084,968th to 99 Farming 10/9/09 | Runescaper August 2005-March 2010Tip.it Mod Feb. 2008-Sep. 2008 | Tip.it Crew Sep. 2008-Nov. 2009 Link to comment Share on other sites More sharing options...
yondergod22 Posted July 3, 2009 Share Posted July 3, 2009 ohhhh ok thanks a lot :D EDIT: I made a Calculator to Calc the Price of 99 range with chins from my level, using the data from Bedman's guide http://spreadsheets.google.com/pub?key=rhLY1y7OrHSwTb1DQsoGYTQ&single=true&gid=0&output=html Link to comment Share on other sites More sharing options...
yondergod22 Posted July 4, 2009 Share Posted July 4, 2009 I have another question if you don't mind please, how do I find out what table something is in? for example, I want to use the minimum price of an item. Link to comment Share on other sites More sharing options...
epicnuts Posted July 10, 2009 Share Posted July 10, 2009 Could you please write a step-by-step guide (just a really quick one will be ok if you don't have time) because I have never used google docs before and I don't know how to do the importhtml thing... Sorry if this is a stupid question sometimes I don't see what's right in front of my face :? Copy the below code into a cell of your choice: =Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=ITEM NAME HERE", "table", 2),2,3) Go the the Grand Exchange page and find the exact name of the item you want to get data for. Copy the item's name and paste it over the "ITEM NAME HERE" text. The mid GE price for that item will appear in the box. You don't need the excact name, just make sure what you mean is the first search, so you can type 'brassard' instead of Verac's brassard, and you can type parts of the examine, like 'blighted staff' or 'quarterstaff' is ahrim's staff, because the examine says 'Ahrim the Blighted's quarterstaff.' Link to comment Share on other sites More sharing options...
yondergod22 Posted July 10, 2009 Share Posted July 10, 2009 And some stuff doesn't get you the right price even if you search for the exact name because it's still the second thing, like "chinchompa" So you have to change it to 3,3) at the end. And I still need to figure out how to get the minimum price if anyone knows. Link to comment Share on other sites More sharing options...
brainymidget Posted July 14, 2009 Author Share Posted July 14, 2009 Just add the following to the end of the code * .95 That will give 95% of the current price which is what the minimum is. Link to comment Share on other sites More sharing options...
GreenFire63 Posted July 20, 2009 Share Posted July 20, 2009 First of all, absolutely wonderful guide, should help me lots in everything ;) Someone mentioned the problem of + in the price change column, and the problem was never really addressed. This problem interferes with the ability to make a column with daily percentage changes. Would it be possible to eliminate the + from this column using the importhtml code? Or any way to convince googledocs that the "+" means positive? If this is too difficult, any other code eliminating the + would be greatly appreciated Also, is it possible to make the "m" and "k" conversions directly in the importhtml code? Billionaire status as of 7/19/2009 Link to comment Share on other sites More sharing options...
Blitzer150 Posted July 21, 2009 Share Posted July 21, 2009 "#N/A" Even when I've correctly put the formula into the box and written in the item name correctly, I still keep getting this. Any thoughts? Sometimes even once a price update is complete (yes its complete, not occurring) the prices flicker between #N/A and their price. Any thoughts? Link to comment Share on other sites More sharing options...
GreenFire63 Posted July 21, 2009 Share Posted July 21, 2009 I realized that for most items "#N/A" is displayed right after GE updates, when the items' prices are getting updated. (right after GE update my entire page is full of that) Usually, it takes GoogleDocs around 20-30 minutes to have the new price showing. Since items on the GE aren't updated all at once and some even get double updated, expect #N/A to be there up to 30 minutes after item updates at many times of the day EDIT: Realized that its not limited only to ge updates. Billionaire status as of 7/19/2009 Link to comment Share on other sites More sharing options...
Robert_R Posted July 22, 2009 Share Posted July 22, 2009 Do I have to type in every url I want to use? It says I can't paste things into the cell while editing it. Link to comment Share on other sites More sharing options...
GreenFire63 Posted July 22, 2009 Share Posted July 22, 2009 Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs. Makes it much easier. Billionaire status as of 7/19/2009 Link to comment Share on other sites More sharing options...
Robert_R Posted July 22, 2009 Share Posted July 22, 2009 Personally, I have a copy of the code in notepad, edit it from there, then paste it into google docs. Makes it much easier. Ok, but it still won't let me paste into the google docs for some reason, any ideas why? Link to comment Share on other sites More sharing options...
GreenFire63 Posted July 22, 2009 Share Posted July 22, 2009 Have the entire code that you want to have in that cell in notepad first, edit as needed, then copy+paste the whole code into the cell. You can't, or even if you could, shouldn't, copy+paste bits and pieces of the code into a single cell multiple times, opens up opportunities to screw up the code and is very tedious. Also, while you are editing a single cell, google docs prohibits you from pasting; its only meant for manual editing. Does that help? Billionaire status as of 7/19/2009 Link to comment Share on other sites More sharing options...
epicnuts Posted July 25, 2009 Share Posted July 25, 2009 Could you please teach us how to do this in excel, as it's not easy to switch to google docs after years of using excel, and google docs misses some functions I really want to have in some of my calculators. Link to comment Share on other sites More sharing options...
AgingMiser Posted July 26, 2009 Share Posted July 26, 2009 First of all, absolutely wonderful guide, should help me lots in everything ;) Someone mentioned the problem of + in the price change column, and the problem was never really addressed. This problem interferes with the ability to make a column with daily percentage changes. Would it be possible to eliminate the + from this column using the importhtml code? Or any way to convince googledocs that the "+" means positive? If this is too difficult, any other code eliminating the + would be greatly appreciated Also, is it possible to make the "m" and "k" conversions directly in the importhtml code? Yeah, but... you might be scared. [hide=Open at your own risk, Thaa wasn't kidding.]=if(iferror(find("m",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)),false),left(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3),find("m",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3))-1)*1000000,if(iferror(find("k",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)),false),left(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3),find("k",Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3))-1)*1000,Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3)))[/hide] -edit- Robert, try double clicking the cell first, and then pasting the code. That might help. Link to comment Share on other sites More sharing options...
GreenFire63 Posted July 26, 2009 Share Posted July 26, 2009 Aging Miser, tyvm. Thats...complicated :shock: Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign! Billionaire status as of 7/19/2009 Link to comment Share on other sites More sharing options...
AgingMiser Posted July 26, 2009 Share Posted July 26, 2009 Aging Miser, tyvm. Thats...complicated :shock: Is it possible to make a find+replace function for a single cell deleting a +? That's a very BIG problem for me, just getting rid of the damn plus sign! Lol. Only started on Google Docs when I read this guide yesterday tbh, and I found something totally awesome. If the price of an [item] is listed as ___k in the GE Database, use this code to isolate the number: =MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000 The # is equivalent to the number of digits preceding the k, plus one. Take runite ore, which is currently listed as 13.7k. There are 3 digits preceding k - 1, 3, and 7. Add 1 to that, which gives you 4, then replace the # in the code with 4. The code to isolate a number in the millions is almost identical: =MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22[item]%22","table",2),2,3), 1,#)*1000000 Again, the # is equivalent to the number of digits preceding the m, plus one. The code in my post above was from Thaa's post, and is a logic test - thus, in other words, a universal test that will give a correct number regardless of whether it's a k or m. I'm working on a script for price changes right now, but for some reason, the spreadsheet's functions aren't loading. When I do find the script (should be in a couple of days, a week tops) I'll post here :geek: 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