Science & Industry

I've recently tried to update all my old spreadsheets to be compatible with the new update. I've been struggling for a few hours now.

I'm using this as a guide - http://eve-industry.org/export/IndustryFormulas.pdf
http://community.eveonline.com/news/dev-blogs/industry-3rd-party-developers/?_ga=1.145189238.330099233.1412076317

The most glaring issue I'm having is, whats the base job costs? I'm sure I'm going to have more issues, but to get things moving that is the first thing I need to know :)

Here's my spreadsheet - https://docs.google.com/spreadsheets/d/11bjThXGShW2JNcPKe19XCFgUaigQdt6J0Mzjr6kTpgA/edit?usp=sharing

any help would be great. thanks.

I've had the same difficulty. I've got several blogs/posts/pdfs of where people tell you it can be pulled from an API... however, no clear examples of how to actually do it.

This is the closest I've found to being able to decipher an API call in google docs: http://api.eve-industry.org/

But I'm still at a loss as to how to really implement it into my spreadsheet without calling hundreds more API calls for all my BPOs.

Pretty frustrating that there is a barrier to industry on any scale beyond simple GUI in-game dabbling. To do anything on scale you really need to know how to pull API info into google docs. I've just resigned that I'll have to take a weekend to learn the ins & outs of how to use an API.

Sure you can use tools such as IPH, but that one in particular is so frustratingly complex, I load it up stare at it for an hour or so and close it down again, as I can't narrow down what I want clearly enough without piles of garbage showing up in the list of what to make.

Hah, no I don't want you to write exactly what I need (not for me anyway). :)

I'm a huge proponent of 'give a man a fish, feed him for a day, teach a man to fish, feed him for a lifetime'.

I do it all the time at work for others.


I just find it amazing that to be able to figure out all the costs and chances and hidden fees, you need to have a good understanding of how to pull information from APIs... for a game.

EVE certainly pushes the limits of ability of many people. Especially when it comes to industry.


This afternoon I basically played spreadsheets online at work (hah, love how noone notices I'm not really working) and I got my massive spreadsheet working.


CJ, as mentioned, this is the site you need to work with, Qoi did some great work in giving examples: http://api.eve-industry.org/
I would highly recommend reading carefully through this PDF on Qoi's site too: http://eve-industry.org/export/IndustryFormulas.pdf
Dwissi has also summarized most of the pertinent info above.

Your system cost indexes can be pulled from here: api.eve-industry.org/system-cost-index.xml?name=Jita

Your adjusted costs for your jobs can be pulled very simply like this (thanks Qoi for your usage example):

=IMPORTXML("http://api.eve-industry.org/job-base-cost.xml?names=Punisher",
"//job-base-cost")

This will give you the adjusted cost for that one item in one cell.

The difficulty I ran into quickly was that you can use up to 50 of these calls in one google spreadsheet. Google Docs limits IMPORTXML calls to 50 in a single document.
To have this return multiple items is a little more complicated, you need to use the CONTINUE function, basically you can read up on it here: https://forums.eveonline.com/default.aspx?g=posts&t=15796
It's a little complicated to get it running pulling multiple items in one call so you can get a result of more than 50 items, but I have done it successfully, so it's entirely possible.
My example would be greek to you because it pulls all the cells from other sheets, so it's a long garbled mess if you don't have the other tabs.

It took a while, but I mainly used Qoi's site to figure it all out, and that extra thread to pull multiple items from one IMPORTXML call.

Good luck, I've had many people help me out on these forums so I figured I'd lend a helping hand when I just figured this out myself.

Google removed the 50 calls limit about 6-9 months ago. Also the continue command is no longer used too. Make sure your spreadsheet has been transferred over to 'new' google sheets

Why not just use the site you've been quoting?

http://eve-industry.org/calc/

I copied your spreadsheet so I could play with it. Link is below.

https://docs.google.com/spreadsheets/d/1L_2H8rGawc8IJ64DS9hu7Sr7OGo5d-kf6YMT8APGIVQ/edit?usp=sharing

Goggle Spreadsheets is no longer limited to 50 import calls. But that doesn't mean you should drop a thousand into it. Fortunately, there is a way around having to type all that out.

"Join" is used to join a bunch of things together using a common delimiter. Very useful for turning an array of values into a comma separated string, which is what you need for price calls to eve-central. Something about Microsoft router software not liking long strings. But it does nothing for a single value. For a single price call, just use the cell reference, not Join. Check the service's API documentation to see if and how many values it will take at once.

Use named ranges to make referring to commonly referenced ranges like your price sheet or a recipe sheet much easier. This way if you make a change to your price sheet or some other long list of things you don't need to go through the entire spreadsheet looking for references in formulas. Just change the range dimensions in your named range. You can find this feature under "Data > Named Ranges..."

Rather than making a column really narrow to hide it, just use the Hide feature. Literally right click on the column head, click "Hide Column". A small icon will become visible in the column headers where the hidden column would be. Clicking on that icon will reveal the column.

When you need to do this:

=sum(H4*D4)+(H5*D5)+(H6*D6)+(H7*D7)+(H8*D8)+(H9*D9)+(H10*D10)

Do this instead:

=sumproduct(h4:h10,d4:d10)

Summing the product of 2 declared values is pointless. Instead of using sum(x*y)+x, just use x*(1+y). Its a standard formula for increasing something by a variable percentage, which is very common in Eve. No need to make formula calls here. Just do math.

I changed some of your formulas to make it much easier to add and remove items from your prices sheet, as well as to more easily deal with multiple calls. ArrayFormula is your friend. So rather than having to edit a formula on every row of a large list of things, you just change the one formula up top and voila! Everything in the entire list updates at once, and with only one function call. This makes your spreadsheet a lot faster.

I really like what you did to allow for multiple simultaneous selections with mineral quantities for each run of ships. Personally, I would have put the recipes and mineral requirements on their own sheets. But that's just me. I did change the Tritanium column from individual formulas to a single array formula. Its a simple example that shows how powerful array Formulas can be. It also clearly demonstrates how to specify a entire column or row as an array range by leaving out one part of the reference.

Example: A1:A is entire column A. B1:B = entire column B. A1:1 would be entire first row. B1:1 would be first row, but starting at column B.

I've felt your pain. The Market Research Report in Eve Production Manager distills right down to the essentials, filtering out most items (faction, storyline) you'll never build. I'm now considering a filter that will look at your capital and sales volume and filter out items you can't afford to build.