Hey All
I am returning after several years absence and my old excel manufacturing spreadsheets don’t update anymore. The old sheets had eve central market API connections.
Can someone please advise me as to the best solution to fixing my sheets?
I prefer to fix the sheets with new connections if possible and not rebuild new sheets from zero.
Thanks in advance.
I have used fuzzwork.
If you use google sheets, You can use eve marketer with a limited amount of call.
see https://docs.google.com/spreadsheets/d/1oeuRUKfcza5ndT07sVgD940qyIHx8MG1yKtByW2HXw0 , page “prices” : this page uses only two calls to get the BO and SO of 50 items. However if you have more items, you need more calls (up to 200 as specified in https://api.evemarketer.com/ec/swagger.yaml ).Typically you just need to copy the formula lower, after 200 items…
This is possible because evemarketer returns the list of item in the order they were asked.
My Excel spreadsheet uses Eve marketer for prices, with Eve market-data as a possible backup.
An Excel call looks like this:
=FILTERXML(WEBSERVICE("http://api.evemarketer.com/ec/marketstat?usesystem=30000142&typeid="&A9),"/exec_api/marketstat/type/sell/min")
Which has the ID of an item at A9 and returns the Jita sell price.
I use single calls since I don’t want the prices of everything, just the relatively few items I manufacture.
Isk Per Hour can do this in a couple of button presses once set up. In my case however I want big alarms telling me which items I can sell for lots of ISK but don’t have in stock!
you should factor all your calls together : you make a list of the ids, then create the correct URL, and call it once
so one cell with =WEBSERVICE(“marketer?typeid=”&join(’,’,ids) ; with ids being the range of the ids (like is done in that cheat)
And then each cell for the price is filterxml(A15, “//type[@type_id=”&B4&"]/sell/min") ; with A15 being the cell that holds the webservice result, and B4 the id you want.
This way you only have one single API call (for up to 200 ids) and it’s much faster to update (add a hash at the end, that is added after each URL ; change the hash=> force refresh)
Ah, I guessed there might be some way of doing this (takes notes). 
As it is right now Excel takes about half a minute to refresh everything for me, which isn’t a bother as I only refresh once a day minimum. My ESI stuff takes as long to download.
[added}
After some trial and error the Excel versions could be:
=WEBSERVICE("http://api.evemarketer.com/ec/marketstat?usesystem=30000142&typeid=34,35,36,37,38,39,40,11399")
=FILTERXML(A$1,"/exec_api/marketstat/type[" & (ROW()-ROW(A$2)+1) &"]/sell/min")
Where A1 is the location of WEBSERVICE and A2 that of the FILTERXML. Fill down from A2 and the prices will appear!
with one call for orders it should only take a split second to update.
This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.
