Working With API Wallet Data

Overview

The audience of this post is a person that has a database table that is populated from the /char/WalletTransactions.xml.aspx API and wishes to use queries to produce meaningful, intelligent results that can be used to help focus trading operations.

Even with limited database query knowledge, we can derive meaningful Business Intelligence out of our dataset. Basic MySQL/SQL knowledge is recommended as values or parts of an example query may need to be modified to suit your environment or table structure.

I won’t cover the design on how to get API data into your database table here as I want to save that topic for its own post. If you need an answer to populating your database from the API, search the Eve Forums as there are a many projects that can help.

Prerequisite

Wallets from multiple characters are stored into a database table.

phpMyAdmin or SQL Server Management Studio for running and manipulating queries.

Business Intelligence from Numbers

Knowing that a Drake sold for 1.1 M profit one time doesn’t tell you much in the larger scheme of your business as we need to extract more intelligence out of our numbers. Getting further metrics out of the wallet data can help focus your operation and deliver better results.

Consider these starter questions: How fast have Drakes been selling (item velocity)? Are the the monthly profit from Drakes worth your time or should you be working with another item? Is working with Tech1 Battlecruisers proving profitable?

These are examples of the types of questions that you can answer with a few database queries and some spreadsheet analysis. The more robust developer will build these queries into a reporting application, but for the purpose of this post, I will stick to running a query and working with the output.

Table Structure

I have added a few unique columns to my table to help me filter my data. Here is my ‘wallet’ table structure for reference.

— Table structure for table `wallet`
CREATE TABLE IF NOT EXISTS `wallet` (
`transactionDateTime` datetime NOT NULL,
`transactionID` bigint(20) unsigned NOT NULL,
`quantity` bigint(20) unsigned NOT NULL,
`typeName` varchar(255) CHARACTER SET latin1 NOT NULL,
`typeID` int(11) NOT NULL,
`price` double unsigned NOT NULL,
`clientID` bigint(20) unsigned NOT NULL,
`clientName` varchar(255) CHARACTER SET latin1 NOT NULL,
`characterID` int(11) NOT NULL,
`stationID` bigint(20) unsigned NOT NULL,
`stationName` varchar(255) CHARACTER SET latin1 NOT NULL,
`transactionType` varchar(4) CHARACTER SET latin1 NOT NULL,
`personal` tinyint(1) NOT NULL DEFAULT ‘0’,
`profit` double NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`transactionID`),
KEY `characterID` (`characterID`),
KEY `transactionDateTime` (`transactionDateTime`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

characterID, personal, and profit are unique columns that I use to help track items for reporting purposes.

  • characterID – I have multiple character wallets writting to this table so I have a ID for each one stored in a characters table.
  • personal – this is a 0/1 flag that I use to note if an item in the wallet is for personal use. Examples are fuel for jumping capitals or ammo for PVP adventures.
  • profit – calculated profit of the item when sold minus fees.

Examples

— Total Profit per Item
SELECT typeName, typeID, SUM(profit)
FROM wallet
AND personal = 0
GROUP BY typeID
ORDER BY SUM(profit) DESC

— Price and Profit sums per Month in 2012
SELECT MONTH(transactionDateTime) AS calMonth, SUM(price) AS price, SUM(profit) AS profit
FROM wallet
WHERE transactionDateTime LIKE ‘2012-%’
AND personal = 0
GROUP BY MONTH(transactionDateTime)

— Total Profit and Sales per day
SELECT DATE(transactionDateTime) as date1, SUM(profit) as totalProfit, SUM(price) as totalPrice
FROM wallet
AND personal = 0
GROUP BY date1
ORDER BY date1

— Average Margin and Total Quantity per Meta Group over the past 365 days
SELECT invMetaTypes.metaGroupID, invMetaGroups.metaGroupName, AVG((profit / (price * quantity))) * 100 as averageMargin, SUM(quantity) AS quantity
FROM wallet
JOIN invMetaTypes ON (wallet.typeID = invMetaTypes.typeID)
JOIN invMetaGroups ON (invMetaTypes.metaGroupID = invMetaGroups.metaGroupID)
WHERE transactionDateTime > DATE_SUB(CURDATE(),INTERVAL 360 DAY)
AND transactionType = “sell”
GROUP BY invMetaTypes.metaGroupID
ORDER BY metaGroupID

–Market Groups
SELECT wallet.typeID, wallet.typeName, SUM(profit) as totalProfit, SUM(quantity), SUM(price), b.marketGroupID, a.marketGroupName, b.marketGroupName as parentName1, c.marketGroupName as parentName2, d.marketGroupName as parentName3
FROM wallet
JOIN (invTypes, invMarketGroups as a, invMarketGroups as b, invMarketGroups as c, invMarketGroups as d)
ON (wallet.typeID = invTypes.typeID
AND invTypes.marketGroupID = a.marketGroupID
AND a.parentGroupID = b.marketGroupID
AND b.parentGroupID = c.marketGroupID
AND c.parentGroupID = d.marketGroupID)
AND personal = 0
AND d.marketGroupID = 11
GROUP BY wallet.typeID
ORDER BY totalProfit DESC

Flex Your Database Muscle

These examples should serve as a branching off point as you will want to tailor them to your operation. You may want to replace SUM with AVG or COUNT, modify the date range to take a look at a specific trading period, or hopefully roll these types of queries into a reporting engine to really drive your business intelligence.


Working with Eve Static Data

More Power to the People

The propose of this short guide is to empower a person that has limited or no experience working with the Eve dataset to perform queries and extract useful information.

Eve has two main data sets. The first is the static data that is published by CCP and updated with each expansion. This set contains information that doesn’t change such as stargate coordinates, number of Moons in VFK-IV, type of research agents in the Forge, or the minerals requirements for a Drake.

The other set is the dynamic data, which is exposed through the API. This data contains the types of assets you own, wallet transactions, or kill statics for a particular system — things that change and things that go boom.

In this post I won’t be covering the dymanic data, as the Eve Development Network as an excellent introduction guide and full list of everything exposed.

More Data, More Problems

With every new release, CCP publishes a Microsoft SQL database backup file of their static data. You can find the most up to date file on the Community Toolkit website.

Along with the database backup, you will find other useful downloads such as images for every item in Eve — very important when trying to create something presentable out of boring database tables if you are designing a 3rd Party application.

Some people do not use Microsoft SQL and opt to run their database with MySQL, the most popular opensource database package. This format is not released by CCP and thus enterprising players routinely convert the data from Microsoft SQL to MySQL format. Nothing is lost, just converted.

Usually a few hours after a new file has been posted on the Community Toolkit site, people have conversions up for download. If you hang out in the Eve Technology Lab sub-forum, people will usually post links once available. I have been getting my releases from this site: http://zofu.no-ip.de/.

Install Procedure

Noise. has written an excellent guide on how to install the free version of Microsoft SQL Server and work with CCP’s database backup file in order to get to a point where you can run a database query.

Table Prefix Nomenclature

The naming format of the tables is very intuitive and can be picked up fairly quickly.

Example Tables

agt all about agents both mission and research
chr character information such as bloodlines, factions, and races
crp NPC corporation divisions
crt certificates
dmg damage attributes, effects, dogma (attribute system)
eve basic information for icons and units
inv market groups, item details, item reactions. I do a lot of work with these tables
map xyz data, landmarks, regions, solar systems connections, and general universe data
planet planetary interaction schematics
ram industry, manufacturing, refine
sta station services, xyz docking locations
trn multilingual stuff
war faction warfare

Tips and Tricks

An item’s typeID is a very important thing to know. This is a unique number that identifies a type of object in Eve.

Keep in mind that referring to a Drake (typeID 24698 in the static dataset) will be different than referring to your Drake (unique itemID 1002615059065 in the API feed). The itemID value is a unique number that identifies every object in Eve (1).

Eve Queries 101

Drake Details

SELECT *
FROM [dbo].[invTypes]
WHERE typeID = 24698
GO

List all Battlecruisers

SELECT *
FROM [dbo].[invTypes]
WHERE groupID = 419
GO

Eve Queries 201

PI Schematics are contained in the planetSchematicsTypeMap self referencing table. This means that the build requirements for P0, P1, P2, and P3 are all contained in one table. The isInput column is used to list build requirements when set to 1.

1. Listing all P0 items using the isInput value set to 0.

SELECT planetSchematicsTypeMap.*, invTypes.typeName FROM planetSchematicsTypeMap
JOIN invTypes ON (planetSchematicsTypeMap.typeID = invTypes.typeID)
WHERE planetSchematicsTypeMap.isInput = 0
AND invTypes.marketGroupID = 1335
ORDER BY invTypes.typeName

2. Listing items that go into Construction Blocks, a P1 with typeID 3828 and schematicID 74.

SELECT planetSchematicsTypeMap.*, invTypes.typeName
FROM planetSchematicsTypeMap, invTypes
WHERE planetSchematicsTypeMap.typeID = invTypes.typeID
AND schematicID = 74
AND isInput = 1

Eve Queries 301

CCP’s dataset does not contain a table with Tech2 build requirements. We have to create a new table that combines information from a number of tables and then work off the new table.

1. Run this pastebin query once to create your new typeBuildReqs table. If successful, you will get a message like this:

(15791 row(s) affected).

2. Find the build requirements for a Cap Recharge II with input typeID of 2033 (Cap Recharge II Blueprint). The NOT IN statement excludes the skills required to build the item.

SELECT typeBuildReqs.*, invTypes.typeID, invTypes.typeName FROM typeBuildReqs
 JOIN invTypes ON (invTypes.typeID = typeBuildReqs.requiredTypeID)
 WHERE typeBuildReqs.typeID = 2033
 AND invTypes.groupID NOT IN (268,269,270)
 AND activityID = 1

Moar!

If you want some more example queries, comment or find me on twitter.

(1) If you remember that old Devblog post about Drake missile lag, one major component to the lag was that every missile fired had to have a unique itemID created and destroyed after it exploded. This database operation ate up a lot of time, which is critical to a simulation trying to play through a battle.


K162 Trade Index

As a trader you continually ask yourself, what are the best items that I need to be working with?

This is a hard question to answer as there are many ways of using statistics to derive an answer. A common method is to use weights on criteria to come up with an index. The better this number, the better the item performs.

My trading partner James and I put weight on an items total profit, profit per transaction, profit per itemvolumeopportunity cost. We have 35,852 transactions dating back to October of 2010, but I wanted a current snapshot of my trading performance so we limited the interval to 3 months of history.

$sql = ('SELECT SUM(wallet.quantity) as totalQuantity, invTypes.volume, wallet.typeName, wallet.typeID,
 (SUM(wallet.profit) / 100000000) +
 (AVG(wallet.profit) / 100000000) +
 ((SUM(wallet.profit) / SUM(wallet.quantity)) / 40000000) +
 ((SUM(wallet.profit) / (SUM(wallet.quantity) * invTypes.volume)) / 1000000) -
 (((SUM(wallet.price) - SUM(wallet.profit)) / SUM(wallet.quantity)) / 500000000)
 as ticketWeight
 FROM wallet
 JOIN invTypes ON (wallet.typeID = invTypes.typeID)
 WHERE wallet.transactionDateTime > DATE_SUB(CURDATE(), INTERVAL 120 DAY)
 AND transactionType = "sell"
 GROUP BY wallet.typeID
 ORDER BY ticketWeight DESC');

We’re open to critiques of our method, as this is our first attempt to come up with more intelligence beyond ‘what items have the most profit on average per time period’.

K162 2012 Q1 Trade Index 50

Note that PLEX is very over-weighted due to its extremely small size (0.01 m3). We are not currently eliminating outliers.


Stackable Blueprints

When I started doing industry jobs, I found it very annoying to constantly look up the item details for each blueprint and I often lost my BPOs in a sea of BPCs.

After looking on the forums, I found that many people were asking for a icon change. CCP’s response was that their look-up query for blueprints was very database heavy (costs a lot of resources) and there would need to be some major reworking of their database structure and query code to give the client the ability to quickly differentiate blueprints.

Here I was in 2008 getting annoyed while I found forum posts dating back to 2004 asking for this change!

With Incursion 1.5 (May 2011), the icon change for BPO/BPCs hit Tranquility and I rejoiced because my industrial life was forever changed. Simple change with a large impact. I believe than when they transitioned to 64-bit numbers for items, some work was done to rectify the database query and code was cleaned up.

I would like to rally all industry people for another change. For any person that does industry, you work with a large number of blueprints. I would love if there was the ability to stack items of similar ME/PE.

Here is a mock-up of two versions of stacking for blueprints:

The version on the left seems to be easier on the eye as the information is displayed in a banner format at the top.

CCP, here is your Agile user story:

As a person that performs industry jobs, having a way to organize a large numbers of blueprints would declutter the UI and simplify working with them.

Put that in your backlog and work it into a Sprint planning meeting.


2011 Trading and Industry Reflection

Overview

Moved my operations completely out of wormhole space and into known space. Attempted to get into the 0.01 fast flip market in Jita and Amarr. My interested in Eve faded over the Summer with the lackluster expansion and Monoclegate sentiments. I eventually expanded into Capital production with the acquisition of Carrier and Capital Part BPOs. Later on in the year I attempted a Invention/T2 production line but ultimately decided it was far too click heavy for the effort.

Profit Summary

The start of the year, I found great market niches and was moving 35-40 B/month to achieve around 7-9 B/month profit. Later on in the year due to personal life commitments and a lack of interest in Eve, I wasn’t logging in as frequently; I found that I was moving about 15-20 B/month in order to turn a 3-4 B/month profit.

Top 30 Items of 2011

The image on the right shows the top 30 items by profit for the last year. This was definitely a breakout year for me as I experimented with the market to find what Ships, Modules, Implants and other items produce the best profit.

#1 Large CCC’s. The best performer as these rigs are used in Capital and Battleships to reduce the Capacitor recharge cycle time.

#2 Capital Shield Transporter I. Producing Capitals in lowsec and keeping these items on the market was a great cross sell.

#3/4 Large Rigs. No surprise here as these are put in every Battleship.

#5 Maelstrom. I was slow to move into the Maelstrom market but after looking at the standard 2011 nullsec Alpha fleet doctrine, I quickly realized that I needed to be trading these heavy hitters — pun intended.

#6 Ishtar. This HAC was a surprise performer for me as they kept getting sold. My theory is that they are great AFK mission ships.

#7/8. Capital ship and the popular module. Easy cross sell like the Shield Transporter.

#9 Noctis. Everyone wants one.

#14 Oxygen Isotopes. I did not make any profits from the GoonSwarm ice interdiction. I was working through a stockpile of about 2-3 M Isotopes before the announcement hit. I was trading all four racial Isotopes but due to the popularity of Gallente towers, Oxygen Isotopes traded better than the other three.

#19 Hulk. Surprisingly a good performer. The majority of Jita flips came in at 5-6 M profit each.

#21 Dominix. The Space Potato is a great mission ship.

#22 Anshar. I wanted to build one as the project was a end-game build for an Industrialist. Details about the build costs and profits can be found in this post.

#26/27/29 Blockade Runners. This item was also a nice discovery once I started trading them.

#30 Dramiel. With the nerf in Crucible, I have seen a slow down in sales.

#12/13/18/20/24/25/28 Implants. People die.

SQL Profit Query

If you have your own wallet table, here is the query I used to pull up the stats for the year. Granted I took the output and made a nice table with it using PHP, but you can easily work with this query.

$sql = ('SELECT typeID, typeName, sum(profit) AS totalProfit, sum(quantity) AS totalVolume
				FROM wallet
				WHERE DATE(transactionDateTime) > DATE_SUB( DATE( :eveDate ), INTERVAL 365
				DAY )
				AND personal = 0
				AND transactionType = "sell"
				GROUP BY typeID
				ORDER BY totalProfit DESC
				LIMIT 30');

2012 Prospects

I’ve found a new, painless method for moving around large amounts of minerals using compression techniques. The new Tier3 Battlecruisers have great market potential not only in the ship hull, but the associated Large guns and modules.

I have been considering shutting down the Capital operation and venturing into different areas. This will move about 20-25 B worth of BPOs into liquid ISK. More spreadsheets are needed to illuminate my path.


Capital Industry Calendar

The industry posts at Eve fail have inspired me to start producing Capitals and this has spawned some new requirements out of our Wallet Manager program.

Problem: Expensive Capital BPOs that are idle mean that they are not producing profitable items

Solution: Use the Corporation Industry Jobs API to display a visual aid of capital job production progress as to easily tell when BPOs will be idle

As always, James and I start off with a quick sketch on paper of what we think the final layout should look like. After some scribbling, debates about tables/CSS/javascript, and layout design we settled on a design.

Here is a shot of our paper sketch.

My knowledge of javascript is rather limited and James is usually the one spearheading new and innovative solutions when I present a design challenges.

Notes from James:

Blake often comes to me with a “I need this thing. Can you make this thing?”, to which I often pause and perform what I like to call shriveled programmer stare. This maneuver involves me scrunching my face as much as possible, raising one eyebrow, and giving a bit of a pained look. I’m quite sure I gave this look when the idea was first proposed. The basic idea actually is just a Gantt chart. I have x number of capital ships in production at any given time and I would like to know exactly, at a quick glance, when they come out of the oven so the next batch of parts will be ready. Simple enough, on paper. The real question is implementation.

A first idea was to use a table, and just change the background color if the job is still running on that day. However, we decided we would like to have the chart auto-scale, so that it would always display far enough out to have the end of the longest job on the chart. Doing this as a pure table would have been inelegant at best, with quite possibly a ludicrous amount of table columns needed for very long jobs. Instead, I took a more difficult, but ultimately more elegant approach; use a single row per job and utilize the margin-left and width css properties to control the positioning and length of each bar.

This, however, required that we have non-table-based vertical lines to delineate our days. For this I used Raphael. Raphael is a nifty little SVG canvas library with decent documentation and a good community base.


//Initialize our variables
var width = $this.width();
var height = $this.children('.industry-job-calendar-table').height();
var offset = globals.headerOffset;

var canvasDiv = $this.children('.industry-job-calendar-table').children('.industry-job-calendar-canvas')[0];

//Create the canvas
var paper = Raphael(canvasDiv, width, height + offset);

A lot of work went into getting our start and end dates, and anyone who’s worked with the JS Date object knows how painful operating in the non-native timezone (even if it is UTC) can be. After our fancy date math, we set the job bar properties, draw the canvas object behind the table rows, attach our tooltip hover() events to the bars, then do a pretty animation of the bars to their final widths.

All in all, a nice little idea that came in at around 325 lines of JS and 20 lines of PHP. And to use it is quite simple:

$('#industry-calendar').phdIndustryCalendar({url: './getCapitalJobs.php'});

And the final result is a clean looking display of our Capital production line.


Wallet Manager Security Issues

If you have been following this blog for a while, you know that my corpmate James and I have been working on a Wallet Manager site to help manage our Eve ventures. Over time it has grown into our all-encompassing-project-management-thing which now has a trading, manufacturing, invention, and cost analysis sections.

I wanted to disclose why this darn thing is not open to the public as the majority of the feedback that I have been hearing has been, “awesome, now when can I use it!?”

We have not made the site public because of security issues, specifically due to the numerous SQL injection abilities in our code.

Here is a common function that we use that takes the typeID of an item and returns its name. We use this so when we display a Cap Recharger II for example, you can see the name of the item and not just the ‘2032’ number identifier that is easier to work with from a programmability standpoint.

This PHP function retrieves the item name from an input of its typeID.

public function getName($typeID)
{
$sql = ‘SELECT typeName FROM invTypes WHERE typeID = ‘.$typeID.’‘;
$connection=Yii::app()->db;
$command=$connection->createCommand($sql);

//Run the query
$results = $command->query();
$itemName = $results->read();

return $itemName[‘typeName’];
}

The database query is highlighted in green and the terrible part has been highlighted in red.

What you are seeing is a database query that is fed a non-sanitized input. Good programmers will take the $typeID variable and sanitize it before putting it into the SQL query. A common check is to limit the variable to only have characters such as A-Z and 1-3 characters. This check will not allow any special characters such as  : ; ‘ ” $ that are used for SQL operations to be allowed in the query.

With our current function with the unsanitized input variable, you can plug in all sort of things into the query. You could inject code in place of the variable to read, drop, and modify the database,  something we obviously don’t want happening.

Sadly around half of our function were written in this fashion in order to get the pages up and working. Because it has been an internal project, the focus has been on the aesthetic result and not the security of the code behind it. If we were to release it to the public we would have to go over each function and check to make sure that it is secure.

Let me quote CCP and say Soon(tm) for the release.