Working With API Wallet Data
Posted: 2013-04-15 Filed under: eveonline, industry, market | Tags: marketgroupid, mysql, sql, typeID 3 CommentsOverview
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
Posted: 2012-04-13 Filed under: eveonline, industry | Tags: api, drake, eve, itemID, mysql, PI, sql, typeID 14 CommentsMore 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.
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.