Monday, October 8, 2012

Previous Tools

As I work to generate the very first draft kind of tools, I'm filling this blog with various higher-level topics.  Once I can start showing off the innards, expect the topics to become more technical.

Today, I'll walk through the iterations leading up to this point.  Mostly the gdoc spreadsheets, since I don't want to host the original excel sheets.  Each of the headings below is a link to the project.

Initial Industry sheet

This is the very-latest revision of this tool.  The entire thing is extremely kludgey.  Originally the thought process centered around a few different problems/ideas:
  1. "Group" products: many blueprints of a type share datacore types.  No need to train ALL the sci skills, just focus on grouped types
  2. Can't make a shopping list in a spreadsheet.  There are so many extra types of materials, there is no quick way to boil down inputs
  3. Dependent Products: Need moon materials to build components, need T1 to build T2
The end result is this first draft tool.  Constantly needing to paste one more thing is why it's completely unreadable under the hood.  Furthermore, the main interface was extremely confusing and took forever to explain to people.

Even worse was for nearly 2 years, this tool was not automated.  So, despite providing a mostly-useful shopping list, not all kits were used every week.  This lead to manually adding the values I actually needed (since I didn't want to delete the "mixes" I had made up).  Also, all price data had to be added by hand.  This gave no insight to actual profitability, only cost.  Only after I joined Aideron Robotics did I add automation through eve-central.  Price automation made the tool tolerable, but we quickly outgrew its effectiveness.

Build All the Things Sheet

This is the culmination of a team effort between TheAhmosis and myself to get a way to look over the ENTIRE T2 market in a few quick clicks.  Beyond just price automation, we devised new metrics to fine tune the product selection process.  
  • Throughput: What one inventor can build in 6days.  This is rounded in such a way to estimate human-capabilities
    • 16hrs to build: no one will wake up early or log in from work to flip.  Round up to 24 hours
    • 26hrs to build: same problem as 16 hours, better to round up to 48hrs
  • %Throughput: throughput/volume.  Margin means nothing if you can't sell what you build.
  • %profit: Isk efficiency.
  • Revenue: Throughput * Profit.  What is pocketed for the work
  • Slot Cost: up front costs to build
  • ISK/hr and adjusted ISK/hr: ISK/hr is just regular build time, adjusted accounts for throughput rounding
Also, locking down the under-the-hood calculations allows me to share this sheet with anyone and protect the work I've done.  This way I can save others from building similar tools, and allow a central place for number checking.  I still use this sheet to pick my products.

The downside is there is a LOT of data working in the background, and it's not optimized for efficiency.  This results in the price estimates falling over on bad XML calls, working slowly from sheet-to-sheet, and sometimes timing out.  Thankfully, the complexity seems to be within Google's bounds, so despite being slow, it's 90% effective.

EVE_Market-crunch Perl Project

This was an exercise in XML tied to some IRL work I was having trouble with.  This has a lot of power, but being Perl, is pretty hard to iterate.  This was the initial framework I tried to build for the goals of Prosper.  A quick run down of functionality:
  • Priceparse.pl: processes market data to build an XML list of base material prices
  • Marketcrunch.pl: uses the manufacture.xml list to build price data (like priceparse)
  • Kitbuilder.pl: uses manufacture.xml and producers.xml to make a general kit list
  • Manufacture.xml: all the T2 material requirements for everything (except rigs)
  • Component.xml: All the materials for components (cap and normal) + RAM
  • t1.xml: all the mineral requirements for t1 building (assumes researched)
  • kits.xml: output of kits.  Contains per-char material list as well as a total shopping cart
This suffers the same fate as many of my first attempt code.  It's very kludgey and half-formed.  I ended up having to abandon development due to wanting to help Marcel on his updates.  Both projects fell apart, and I'm stuck with a mediocre tool set here in Perl.  I still use the kit builder to manage my week-to-week building.  Unfortunately, maintaining the build requirements in XML is incredibly time consuming and not exactly stable or sustainable.

FW LP Calculator 2.0

This was another large undertaking that suited itself decently to a spreadsheet.  There are a few interesting features going on here:
  • Pseudo-DB of rewards: the All Rewards sheet is exactly that.  I can then use QUERY() on those values to do some interesting features that would not be possible otherwise
  • Shopping list: Currently broken.  This allows people to pick their rewards and quantities to get info on both expected income and tag requirements
Unfortunately, this is pushing right against the complexity limits allowed by google.  As such, it was only possible to add Minmatar rewards to this sheet.  The lesson taken away from this project is making complicated spreadsheets modular.  Using the importRange() function, each background tool can be isolated and compartmentalized.  Also, that means I can have a central ALL THE PRICES database and import small parts of what I actually need.

I did start a newer version with all the rewards and a lot more efficiency, but with the faction warfare changes to LP coming in December, it is no longer a priority.  Also, the hope is to add LP functionality to the Prosper project once industry tools are finished.

After talking with Cameron Zero at length about RVB's fitted ship program, he shared their sheet with me.  Though he was automating price data, asset stockpiles were still being entered by hand.  I had already had a half desire to have EVE API data in gdoc, so I jumped on trying to work something up for him
  • CharList(keyID, Vcode): Returns character name, charID, corp name, corpID for a given API key
  • stationAssets(keyID,vCode,charID,stationID): Returns all assets for one character at one station (Default Jita 4-4)
This is only the start of what I would like to provide for the EVE community in Google Spreadsheets.  But with CREST on the horizon (and a lack of simple ways to handle id->name translation inside app script), this project is on hold.