Mongo Posted November 29, 2009 Share Posted November 29, 2009 Well I decided to make a smithing calc... I know lots of them are pointless to have (rune items) but I thought it would be cool to add them anyways :D http://spreadsheets.google.com/pub?key=tFIfnO-wHIo3ceZjBrT2yqg&output=html I actually maxed out the number of ImportHtml I could use (50) but it works well :) I also made one before as a trial, it's just my slayer equipment (just to watch and see any drastic changes in price) http://spreadsheets.google.com/pub?key=t5OG4EYPfDrCiFVaGJVYMCw&output=html Great Guide! :D Link to comment Share on other sites More sharing options...
FinsToTheLeft Posted January 1, 2010 Share Posted January 1, 2010 Hey guys, this is a great guide but I like some others have been having a little trouble when a price ends in K to denote thousands. I've read the whole thread multiple times, but I've still not been able to work out what I'm doing wrong. =MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22magic_seed%22","table",2),2,3), 1,#)*1000 (I've also entered magic_seed without the underscore). I read before in the thread that I should replace the # with a number, but I'm not sure which number I would put in. Could someone give me some advice? Thanks!Fins Link to comment Share on other sites More sharing options...
Kaphias Posted January 2, 2010 Share Posted January 2, 2010 Hey guys, this is a great guide but I like some others have been having a little trouble when a price ends in K to denote thousands. I've read the whole thread multiple times, but I've still not been able to work out what I'm doing wrong. =MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22magic_seed%22","table",2),2,3), 1,#)*1000 (I've also entered magic_seed without the underscore). I read before in the thread that I should replace the # with a number, but I'm not sure which number I would put in. Could someone give me some advice? Thanks!FinsI haven't found a way to do it using one cell only, I've always imported the number with the "k" and then added the following code to another cell:=if(iferror(find("m",B22),false),left(B22,find("m",B22)-1)*1000000,if(iferror(find("k",B22),false),left(B22,find("k",B22)-1)*1000,B22)) Where B22 is the cell with the imported number. Don't know if that is what you were looking for, but hopefully it helps. 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...
Grimy_Bunyip Posted January 3, 2010 Share Posted January 3, 2010 anybody know how to extract both the second and third columns of an array with the Index() function?Index([Entry],2,3)is what your guide shows, and it extracts a single entry on the 2nd row, 3rd column My current spreadsheets use:Index([Entry],,)I remove the row 2 and column 3 inputs to extract the entire array Index([Entry],,3)extracts all of column 3 I tried using the following input to extract two columnsIndex([Entry],,2:3)but it only results in an error by no means do I NEED to extract exactly two columns, but it would sure make my spreadsheets a lot more compactIf you know i'd really appreciate it. Link to comment Share on other sites More sharing options...
jcube69 Posted January 5, 2010 Share Posted January 5, 2010 is there a way to make the color change if the number is positive/negativ? like for instanace in my "profit tab" if my profit falls to a negative number aka i lose money it changes to color red instead of green? edit never mind i figured it ou all u got to do is add rules on drop down color menu Link to comment Share on other sites More sharing options...
tedbundty Posted March 1, 2010 Share Posted March 1, 2010 I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=en ................................[Currently Have - 1300M].........................................................My Beginner's Merchanting Guide - My Youtube Channel - My Twitter Link to comment Share on other sites More sharing options...
yondergod22 Posted March 1, 2010 Share Posted March 1, 2010 I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=enIt could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing, Link to comment Share on other sites More sharing options...
AgingMiser Posted March 2, 2010 Share Posted March 2, 2010 I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=enIt could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing, You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view. Link to comment Share on other sites More sharing options...
Grimy_Bunyip Posted March 2, 2010 Share Posted March 2, 2010 I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=enIt could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing, You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view.huh, is there a formula view option for published spreadsheets? Link to comment Share on other sites More sharing options...
tedbundty Posted March 2, 2010 Share Posted March 2, 2010 I followed the directions exactly and I got the data, but it doesn't update the prices automatically - they're the same as they were 2 days ago even though GE updated. Does anybody know why? Here's link to the spreadsheet if you wanna check it out for yourself. http://spreadsheets.google.com/ccc?key=0AvcoIS071M0MdEFyeGhVblhvbnZEZHJuNXdWcnJJcHc&hl=enIt could just be because I don't have access to edit your spreadsheet, but it looks like you just typed the prices in instead of putting in the code thing, You're right, he didn't type any data retrieval formulas in; you could've confirmed it by going to formula view. I retried and it appears to be working now... Btw i tried getting the top 100 most traded list from rs database onto a spreadsheet but it didn't work, can anyone help me out with that? EDIT: okay I got it all figured out and the spreadsheets are awesome, thanks :) ................................[Currently Have - 1300M].........................................................My Beginner's Merchanting Guide - My Youtube Channel - My Twitter Link to comment Share on other sites More sharing options...
Drkmaster0 Posted March 7, 2010 Share Posted March 7, 2010 Very nice guide, this helped me a bunch. I made a spreadsheet that gets a bunch of prices for items, and occasionally #VALUE appears in the cell with the formula and nothing appears in the row...the only way I've found to fix it is to change the link and how the spreadsheet finds the item in the list. Is there any better way to fix this or what should I do?Example of the code is =Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=dragon boots", "table", 2),2,3)Last night that would give me the price, but now all I get is the #VALUE Link to comment Share on other sites More sharing options...
Grimy_Bunyip Posted March 7, 2010 Share Posted March 7, 2010 Very nice guide, this helped me a bunch. I made a spreadsheet that gets a bunch of prices for items, and occasionally #VALUE appears in the cell with the formula and nothing appears in the row...the only way I've found to fix it is to change the link and how the spreadsheet finds the item in the list. Is there any better way to fix this or what should I do?Example of the code is =Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=dragon boots", "table", 2),2,3)Last night that would give me the price, but now all I get it the #VALUEgoogle doc is just glitchythe error your describing corrects itself after a given amount of timere-entering your data points will also fix it.As far as I know, there is no good way of preventing it altogether. Link to comment Share on other sites More sharing options...
Drkmaster0 Posted March 8, 2010 Share Posted March 8, 2010 Ahh that really sucks. Thanks though Link to comment Share on other sites More sharing options...
matalena Posted March 17, 2010 Share Posted March 17, 2010 Obj numbers... Great guide! Thanks a lot. I was thinking for a while to do my own calculators and didn't know where to start from. Thanks to you I managed now! :thumbsup: I still have a question though...: is it possible to know the "obj" number of all the items? It would be nice to set the URL to go directly into the GE and take the whole table of the results to work on the data on there. To be clearer I will make an example: the link "http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=5933" will show the GE page for the Willow branch. I would like to know that Willow branch has obj=5933 by just querying (somewhere) for the name Willow branch. How can I do that? Thanks in advance for your help! :rolleyes: Link to comment Share on other sites More sharing options...
AgingMiser Posted March 20, 2010 Share Posted March 20, 2010 Obj numbers... Great guide! Thanks a lot. I was thinking for a while to do my own calculators and didn't know where to start from. Thanks to you I managed now! :thumbsup: I still have a question though...: is it possible to know the "obj" number of all the items? It would be nice to set the URL to go directly into the GE and take the whole table of the results to work on the data on there. To be clearer I will make an example: the link "http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=5933" will show the GE page for the Willow branch. I would like to know that Willow branch has obj=5933 by just querying (somewhere) for the name Willow branch. How can I do that? Thanks in advance for your help! :rolleyes: Funny, I myself used to wonder about this. Unfortunately, Google Docs is incapable of returning a URL; consequently, it can't be used to determine object numbers to my knowledge. However, it should be possible to write a script, in which you input the item's exact name, and then returns the object number. Link to comment Share on other sites More sharing options...
matalena Posted March 21, 2010 Share Posted March 21, 2010 Funny, I myself used to wonder about this. Unfortunately, Google Docs is incapable of returning a URL; consequently, it can't be used to determine object numbers to my knowledge. However, it should be possible to write a script, in which you input the item's exact name, and then returns the object number. AgingMiser thanks a lot for your reply. I would like to know how to write a script like the one you suggest! For now I prepared some VB code in Excel (I am not using Google Docs, I find it too limiting) which does the job, but the other way round. My code doeas the following:- cycles from 1 to n- apply the ItemHTML on the page in the GE with the details of the market change of an obj number (for example with number 864, I apply itemHTML on the page: http://services.runescape.com/m=itemdb_rs/viewitem.ws?obj=864 and will get the GE page for the bronze knife)- I will retrieve the whole page like this (no table numbers available!)- in row 70 I get the names of the item associated with the obj number. By doing this for "a lot" of numbers, I should end up with a full databse of obj-numbers/item-names. Of course this takes ages and I have to do it in blocks of, let's say, 1000 numbers per sheet. Good news is that, from some test I have done, I can see that when a number is not associated to an item, I get the string "Error", so at least it's easy to eliminate the unassociated numbers afterwards! I will finish preparing the script and will run it at night... ;) Ciao! :) Link to comment Share on other sites More sharing options...
jayc3399 Posted April 20, 2010 Share Posted April 20, 2010 Has anyone come up with a non cumbersome way to do this in Excel 2007? Quest Cape Achieved on November 14, 2007Items AcquiredCrystal Pick and HatchetBerzerker Ring x 33/28 Barrows Items Link to comment Share on other sites More sharing options...
AgingMiser Posted April 21, 2010 Share Posted April 21, 2010 I would like to know how to write a script like the one you suggest! Ciao! :) If I knew what to write the script in (whatever runs a macro would probably do, but I don't know that), I could easily set up a loop that took an input, searched for said input and returned the object #. Essentially, it would look something like this. The most tedious part would be setting up a method to produce the URL (easy if done by hand, but you have to go through multiple strings before you can convert an item name to a search query URL) - unless, of course, the macro could just go and search a string. After that, the code would identify the link and copy the URL, then reverse the first method to isolate the item number. Would probably take me a couple hours to be honest, simply because of the tedious nature of the necessary work. Link to comment Share on other sites More sharing options...
Myu Posted May 7, 2010 Share Posted May 7, 2010 This isn't working for me. I get a table that i can't change, so i can't clean it up. I can't use Index for some reason, and i can't use the prices to make a calculation either because GEdb writes it like "6,357" instead of "6357". Link to comment Share on other sites More sharing options...
jayc3399 Posted May 12, 2010 Share Posted May 12, 2010 How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign? Quest Cape Achieved on November 14, 2007Items AcquiredCrystal Pick and HatchetBerzerker Ring x 33/28 Barrows Items Link to comment Share on other sites More sharing options...
Singh Posted May 19, 2010 Share Posted May 19, 2010 how do post this as a picture but still have it change prices?http://spreadsheets.google.com/ccc?key=tZzrT2AOyVNu35w_Uv5SWzg&hl=en# Link to comment Share on other sites More sharing options...
Grimy_Bunyip Posted May 19, 2010 Share Posted May 19, 2010 Has anyone come up with a non cumbersome way to do this in Excel 2007?yes, there is one somewhere. Sorry I can't look it up for you though. It's either on TIP forums or zybezi just did a search on zybez and it's not there though, so try searching the guides section for "excel" This isn't working for me. I get a table that i can't change, so i can't clean it up. I can't use Index for some reason, and i can't use the prices to make a calculation either because GEdb writes it like "6,357" instead of "6357".that is a strange bug that happens for some numbers, but not for otherswhat i like to do is apply the following code to the number:rounddown(1.000000000001*number) it's probably not the most efficient way to handle the issue, but it handles the issue :P how do post this as a picture but still have it change prices?http://spreadsheets.google.com/ccc?key=tZzrT2AOyVNu35w_Uv5SWzg&hl=en#you'd have to learn how to make your own websites, which is out of my scope :) How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign?sign(number)if the number is positive it returns 1if the number is negative, it returns -1 the importhtml should import the price changes in GE as positive and negative values if this doesn't work you could apply a substitution chainbut that would be ridiculously cumbersome beyond all imagination :) then again there's probably other methods even beyond that Link to comment Share on other sites More sharing options...
Singh Posted May 19, 2010 Share Posted May 19, 2010 Hey guys, I'm trying to get Clean Torstol but i just cant get it. The problem is that its over 10k and use a "k" as price. i read the guide many times but i still get either #error or #value=MID(Index(ImportHtml("http://itemdb-rs.runescape.com/results.ws?query=%22clean torstol%22","table",2),2,3), 1,1)*1000 what do i change it too?? Link to comment Share on other sites More sharing options...
AgingMiser Posted May 31, 2010 Share Posted May 31, 2010 how do post this as a picture but still have it change prices?http://spreadsheets.google.com/ccc?key=tZzrT2AOyVNu35w_Uv5SWzg&hl=en#I assume you want to share it without letting anyone change it. Just share the document and restrict permissions when you share it, then give people the link. How would someone go about grabbing data from the third column, isolating the first character and doing a comparison of whether it is a + or - sign?No offense, but I don't think what Morionic said would work. Use a string comparison command for that; it's in GDocs help somewhere. Link to comment Share on other sites More sharing options...
Capt_Davy Posted June 13, 2010 Share Posted June 13, 2010 So far my spreadsheet for farming is coming along great, however, I'm having the issue that googledocs likes to return things back with letters (ie. lantadyme seed, comes back as 32.1k, rather than 32100), which makes them unusable in calculations. How can I make it so googledocs understands "k" to mean "1000"? EDIT: my problem is the same as Ickdeep's. 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