Wednesday, 9 July 2014

Fun with Goo and er... Spreadsheets

Since it was announced, that the bug with Moon Mining equipment that prevented their use in 0.4 systems was being fixed, I've been working on a scanning and cataloging local 0.4 systems and working on a spreadsheet to help me determine whether or not moon goo extraction is profitable.

Google Docs has been my primary spreadsheet for some time, I still use Excel both at home and work but it's much easier to share via Google Docs than Excel. The ImportXML feature is outstanding too, I know Excel can do it but for portability Google Docs wins every time at the moment.


Calculations for profitably are quite simple, figure in the running costs of a tower against what you can sell the moon goo for. It's really quite simple but calculating this based on "live" data from EVE Online can be tricky, especially when you have to take into account Sovereignty and the fluctuating price of fuel/goo.

It would also be nice to see the 30 day average amount sold for each moon goo, that way we can see whether we're be selling reasonably fast or it will not be a fast sale. i.e. The more sold per month would indicate faster selling.


I settled on the following features for my first version:
  1. Calculate extraction profitability based on different numbers and tower types
  2. Allow indication of number of sovereignty based towers to reflect reduced fuel costs in overall calculations
  3. Show Profit/Loss for 7, 14 and 28 day periods
  4. Show average 30 day volume sold in Jita
Few initial features but some could end up being rather complex to get working, regardless it's a nice challenge and would certainly be a useful tool.


I played around with various user interfaces to allow information to be entered and displayed but eventually I settled for a layout that had the different moon goo's listed vertically on the left axis and along the horizontal y axis I had the number of and tower types with the 7/14/28 day profitability result.

User Interface for Moon Goo extraction profitability

I added colour coding for the different moon goo groupings as well as using vertical colouring to indicate non-sovereignty/sovereignty towers. It's quite a simple arrangement but more importantly contrasts and works well.


Most of the functionality of the spreadsheet is via the use of ImportXML() to get data from EVE Central and the 30 day volume information from EVE Market Data. Formulas are then used to concatenate data and perform calculations based on a couple of known constants:
  • Moon mining is fixed at 100 units per hour, this gives a maximum weekly extraction of 16,800 units
  • Use of a tower with the Sovereignty bonus yields a 25% reduction in fuel use 
With these constants we can calculate both moon goo output and fuel usage based on the tower types selected by the user.

Example Mercury Moon Extraction

Let's take a hypothetical system that I'm interested in I want to extract Mercury from a moon using a Large Tower. I input the number 1 into the white cell under AL to indciate I want to use a Large Amarr Tower:

Moon Goo extraction using an single Amarr Large Tower

Well that isn't a good start, using a Large Amarr Tower will result in a loss of 30m ISK per week or 122m over a months use if we just take what we mine and sell in Jita at current prices.

What if we downsize to a Medium Amarr Tower?

Moon Goo extraction using an single Amarr Medium Tower

That's better, now we're showing a weekly profit of 27m using just a single Medium Amarr Tower with current prices. 

All we've done is change the tower we're using so the difference of 10 fuel blocks per hour (30ph vs 40ph) is what has turned our operation from loss to profit.

We're making money now but what if we used a Small Amarr Tower instead?

Moon Goo extraction using an single Amarr Small Tower

Just as I thought, we'll make a lot more money (~50% more) using a Small Amarr Tower instead of a Medium. If I don't want to use Amarr Towers I can change the value to the type of tower I want and see an instant change in the calculations.
One serious thing to consider when selecting the tower is security, using a small tower will result in larger profit but will be more vulnerable to attack as it has lower hit points than larger towers, and more importantly, less fitting for defensive modules such as guns and dissipation arrays.
Once you've determined any tower usage you need to go ahead and purchase/deploy your chosen tower and modules. Unfortunately I do not take that into account in this version, but it is on my wish-list for the future to include "asset costs" to work out how long until profitability for towers.


I would like to add information to the view to show current sell prices for moon goo/fuel blocks in Jita and I plan to allow public access to this in the, hopefully near, future. I'm currently improving the back-end to be a little more open and allow less complex formulas than I currently have. 

What I have works and I've already used it, successfully, to calculate tower deployments in a couple of null sec locations where I'm currently running towers.

I'm also working out the kinks with a version of this that works for Simple Reactions but that one is a little further from actually working.

I'll leave you with this final screenshot, it shows the profitability of running a single Small Amarr Tower on each of the different moon goo's. I think you'll agree that it's quite eye opening in what is/isn't worth the effort of extracting if you're not using it yourself and just want to sell it.

Moon Goo extraction profitability using an single Amarr Small Tower

No comments:

Post a Comment