| Pages: [1] :: one page |
| Author |
Thread Statistics | Show CCP posts - 0 post(s) |
Shaz'bot
Sebiestor Tribe
Minmatar Republic
0
   |
Posted - 2015.04.07 12:39:21 -
[1] - Quote
Hi all,
I need help getting my production spreadsheet to work please! 
I'd like to start by saying that i'm not skilled with spreadsheets, this is my first real attempt at making a more 'advanced' production spreadsheet to calculate my production costs vs sale prices using imported data from EVE Central. But I really want to understand this so I can help tons of others with the same issues, i'm a smart guy, just not experienced in this!
With that out of the way, I need your help, oh wise space folk!
I'll try to explain my problem as clearly and briefly as I can:
-I'm using Google Spreadsheets
-I havn't been able to find any up-to date guides online (that speak in layman's terms lol)
So my spreadsheet is simple, all I want it to do is pull the max buy and min sell order prices of items in, Dodixie specifically, using their associated typeid's (I have a list of them already). But i'm running into problems with the EVE-Central url i'm using to try to pull the data, it just keeps telling me "Imported content is empty", and it says the same across 4 different typeid's i've tried it on. The url seems to be consistently used on other people's spreadsheets, but all of the examples I could find are from 2013 and none of them seem to work anymore, so i'm thinking something's changed in the url itself perhaps?
Here's the EVE central url i'm trying to use to pull price data from: http://api.eve-central.com/api/marketstat?&usesystem=30002659&typeid=
The 'typeid=' has no specified typeid in the url itself as it takes the info from a seperate column with the typeid in it, i'll link the ImportXML string i'm using to better explain that and perhaps even flag up a potential problem there: =ImportXML(CONCATENATE(Math!B3,B4), "//Sell/min")
So basically my question is, what am I doing wrong? =)
Thanks in advance, if you need more info i'll do my best to answer, but I greatly appreciated any help you can give. I'd like to reallllly kindly ask you to reply in easy-to-understand speak too if possible please haha!
Thanks,
Shaz'bot |
Charlie Nonoke
Blue Republic
RvB - BLUE Republic
58
|
Posted - 2015.04.07 14:40:13 -
[2] - Quote
Instead of //Sell/min,
replace it with:
/evec_api/marketstat/type/sell/min
/evec_api/marketstat/type/buy/max
inside those quotations. |
Shaz'bot
Sebiestor Tribe
Minmatar Republic
0
|
Posted - 2015.04.07 15:30:03 -
[3] - Quote
You're an amazing genius wizard!!!
Thanks so much, it's working perfectly now, I owe you one!
Shaz |
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
5101
|
Posted - 2015.04.07 16:50:09 -
[4] - Quote
To explain why it wasn't working:
google lowercases xml and attributes for importxml. (one of the reasons I'm not a fan)
https://github.com/fuzzysteve/eve-googledocs-script/blob/master/EveCentralPrices.gs may be of interest for an alternate way to load it.
Woo! CSM X!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Shaz'bot
Sebiestor Tribe
Minmatar Republic
0
|
Posted - 2015.04.08 12:33:26 -
[5] - Quote
Thanks for that, though the code in that link looks abit too complex for me atm, maybe something for the future haha.
I should ask though if there is a way of condensing multiple importXML calls into one? As i've read quite alot about googlesheets starting to fall apart when you have more than 50 importXML's calling data on the same sheet which doesn't sound so attractive.
So i'm wondering if there's a way to make use 1 importXML call for an entire column or something like that maybe? |
Steve Ronuken
Fuzzwork Enterprises
Vote Steve Ronuken for CSM
5105
|
Posted - 2015.04.08 16:49:46 -
[6] - Quote
That's one of the reasons I'm not a fan of importxml
The way you use that code is:
open a sheet.
tools -> script editor
close the popup window (this will happen once)
past the code in
save it (giving it a name)
then:
=loadprices(A1:A47,regionid)
where a1:a47 is a set of columns where you have typeids. and regionid is the id of the region you want to lead (if it's the forge, you can skip it)
Ideally, you'd put this into a new page on your sheet, as it returns quite a lot of data.
you'd then use vlookup to pull the price data you actually want into the cells you want it in.
Touch more complicated than copy and pasting the importxml, but feature wise it's better :) And doesn't have that 50 limit. you just pull all the prices you want into another sheet, and reference it as needed.
Woo! CSM X!
Fuzzwork Enterprises
Twitter: @fuzzysteve on Twitter
|
Herman Menderchuck
AscendoTech Research and Development
5
|
Posted - 2015.04.08 17:59:15 -
[7] - Quote
dude.... your face |
Bear Templar
Full Spectrum Inc
Greater Western Co-Prosperity Sphere
9
|
Posted - 2015.04.08 21:12:35 -
[8] - Quote
I've recently created my own spreadsheet after not quite liking the IPH tool..although with all the variables, I'm giving IPH a second chance. Anyway!
Here's what I ended up doing (with significant thanks to Steve Ronuken!):
1. Set up a sheet (mine is simply called "IDs") that simply has the TypeID in the first column and the name of the item in the second. For example:
IDName
34Tritanium
35Pyerite
36Mexallon
37Isogen
38"Noxcium
39Zydrine
40Megacyte
2. On a second sheet (mine is called "Prices Load") follow the steps Steve has posted about copy and pasting code into the script editor.
3. On "Prices Load", use the function =loadprices(A1:Ax, IDs)
Here the x denotes how long of a list you want the loadprices function to look at. If you have 10 items on your "IDs" sheet then put 11, if you have 26 then put 27 (+1 because of column headers).
4. Steve's code will then go off and get the data for you and return the data in the following column order:
TypeIDBuy VolumeBuy averageBuy maxBuy minBuy Std deviationBuy medianBuy PercentileSell VolumeSell AverageSell MaxSell MinSell std DeviationSell Mediansell Percentile
The code doesn't return these column headers so you need to add these in.
5. On my "Prices Load" sheet, I've then added a column to the end which does a vlookup, back to my "IDs" sheet to get the item name again. Not that you necessarily need it, as I recommend using vlookups using numbers (TypeID) rather than a string of characters, but it's handy to remind yourself what item the prices relate to.
6. These are then 2 good starting blocks to build other sheets off of. For example, I have a began a sheet for T1 ships. I then use the a vlookup against the TypeID for the ship in "Prices Load" to tell me how much the ship is selling for, and then also other vlookups against the TypeID for the minerals (again in "Prices Load") letting me know how much it would cost to buy the minerals (both from sell orders and buy-orders).
I HIGHLY recommend you use Steve's code as it has a, and I'm learning this as I go along, cachebuster which bypasses the issue of eventually, by using =IMPORTXML, running into "#N/A"'s.
Hope this helps somewhat!
~Bear
If a fish weighs 1 Kilogram plus half its own weight, how much does it weigh? (It's not 1.5kg btw)
|
Shaz'bot
SpaceTech
1
|
Posted - 2015.04.09 00:04:56 -
[9] - Quote
Wow thanks guys, you explained that really well =)
But alass, a problem! I did exactly what you said in your post, Steve, but ran into this problem after typing "=loadprices(A1:A50,10000032)" into a new sheet, to show prices in SinqLaison.
#NAME? - Unknown function: 'loadprices'
I copied and pasted the code in full from the link you gave and put it into the script editor, saving it as 'Fuzzwork Script'
Edit: Fixed it! =) Hey maybe i'm at risk of learning something here eh?? haha. I changed the function to ' =loadregionprices('TypeID''s'!A1:A5,10000032) ' and it did the trick! :) |
Shaz'bot
SpaceTech
1
|
Posted - 2015.04.09 01:32:53 -
[10] - Quote
Herman Menderchuck wrote:dude.... your face
Pahahahahaha :D Come on, you know i'm sexy! |
Shaz'bot
SpaceTech
1
|
Posted - 2015.04.12 16:25:58 -
[11] - Quote
Steve, this is working great so far, thanks again for all of your help everyone.
Is there a way to refine the search by a specific system instead of region-wide? |
Shaz'bot
SpaceTech
1
|
Posted - 2015.04.12 16:31:59 -
[12] - Quote
Shaz'bot wrote:Steve, this is working great so far, thanks again for all of your help everyone.
Is there a way to refine the search by a specific system instead of region-wide?
*Quotes himself*
Figured it out! =) Thanks anyway o7 |
KenFlorian
Jednota Inc
6
|
Posted - 2015.04.12 22:22:24 -
[13] - Quote
Does anybody have excel code that does the same thing as Steve's basic .gs load code?
I know excel better than the back of my hand and would prefer to stay there. I just need the basic load mechanism.
|
Soldarius
Kosher Nostra
The 99 Percent
1249
|
Posted - 2015.04.13 15:38:11 -
[14] - Quote
This. google screws up the xml attributes if you use caps of any kind in the string. Just keep everything lower-case and it should work.
http://youtu.be/YVkUvmDQ3HY
|
| |
|
| Pages: [1] :: one page |
| First page | Previous page | Next page | Last page |