This weekend I finally got around to looking at API calls and whether I would have enough intelligence to be able to work out what to do to actually use them.
First of all, I looked up Eve-Central’s API and worked out (through extensive use of Google) how to import XML into Microsoft Excel 2007 as a table. I was shocked at how smoothly this actually worked, and after playing around for a while, I had set up my main manufacturing spreadsheet to import buy and sell prices from Jita and Dodixie for the materials I consume via Eve-Central’s API calls, refreshing the data each time the sheet is opened.
The only limitation I found was the 255 character limit when setting up the API calls to import the XML. This meant that, for a number of things, I either needed multiple connections within Excel for the same group (e.g. P1 and P2 planetary materials), or I had to drop some things from the list as I know that I never use them. This was a bit annoying, but as I had completely surprised myself that I could actually do this in the first place, I just ignored the issue and pressed on with updating my spreadsheet.
To ensure that everything looked nice, I also imported a couple of tables from the Eve Static Dump so I could do look up’s and add the names of things next to the prices. All in all, I’m very pleased with it so far, and although I have quite a bit of work to do to get it to where I want (comparing prices across several trade hubs etc.), it’s already at the point where I’m using it to increase my profitability.
Spurred on by this success, I decided to tackle the biggie… the actual Eve API itself.
One thing that we’ve always wanted to look at more closely as the corp leadership is the income we get from ratting bounties. It’s always been a bit of an unknown, and I decided that now was the time to try and tackle this question once and for all. If I could do this, then I could reasonably do most things that I would want to do with the API.
I logged in and created an API key with the relevant permissions and clicked on the “from web” button on the data ribbon in Excel. Using the Eve Development Network guidelines, I pasted the API address for Corp Wallet Journals, added the various required arguments such as my vCode and keyID and hit go and quickly closed my eyes. I counted to 5 and opened my eyes expecting to see an error message, but instead I saw lots of XML.
I hit the import button, confirmed where the table should go and watched the sheet fill up with data. Selecting the table I made a Pivot Table to show me ratting income only (ID type 85), nameID of the character who paid the tax, date of payment and the amount paid. I then tried to group it by month, which failed miserably.
On investigating the data, it had imported the date as text, so I simply added a column, put a formula to convert the text date to an Excel date, watched Excel autofill the table column and then when back to the Pivot Table, selected my new date column instead of the imported text date column and then grouped by month. It worked perfectly and I could now see the amount of tax paid due to ratting bounties by all the ratters in the corp for July, as well as for August to date. It was brilliant, and an Evemail to the CEO followed quickly with a link to the sheet for him to download and look at.
Following on from this, I now want to go further with this and play a bit more with using a CRON job to import some things into a MySQL database and then see what I can do with the Joomla! CMS and some nice chart and report extensions I have for it to see if I can start to combine the information from the main corp and the bank corp so we can get a much better picture of cash flow, revenue streams and expenditure.
Now I’m sure (and in some cases I know) that there are tools out there that can probably do some / most / all of these things, but for me it’s more about the journey of learning how, rather than simply the end result.