I leave for two weeks and you all accidentally the market. I was not able the participate in the Odyssey speculation, but I am enjoying catching up on it.
The wild swings that we are seeing in the Moon and Ice markets due to Odyssey adjustments made me reflect on the stability of the items that I trade.
I used my historical database to come up with a report to provide quantitative insight to help give values to the items that I instinctively know are high risk. Here is a snapshot of the lower and upper items listed in my Stability Report that covers three years of trading sorted by standard deviation (σ).
Here are some general observations about the items listed:
- Basic minerals, ammo, and modules are the most stable.
- Capital modules weigh in somewhere in the middle.
- Tech2 ships fill the upper band of the report.
- Tech3 hulls and associated Subsystems are randomly spread over the middle band.
- Certain battleships are high on the report because their popularity has changed over time due to shifting fleet doctrines.
- The Procurer BPO was an item that I speculated on heavily during the last patch, which netted very positive results.
- Capital ships are subject to large swings due to mineral price changes.
A tenant of becoming a good trader is minimizing your risk while trying to maximize your rewards. My advice for new traders is to work with more stable items as shown in the shaded green area of my report; they are subject to less swing and therefore will help minimize risk. As your ISK resources grow, you can move into riskier items that will [hopefully] yield larger returns.
CCP Tallest announced that Capital size rigs are being introduced in the Odyssey expansion with build requirements that are 5x that of Large rigs. With these changes the coveted Trimark II’s that Super and Titan pilots use are going to increase drastically in price.
Now is the time to put Tech 2 rigs into your capital before the expansion hits as they will not be unfitted after the patch. The Jita market was quick to adjust given the new build requirements with Large Trimark II’s climbing to 488.9 from around 300 M.
Trimark rigs have served me well over the past few years as a constant source of income given that Large rigs were put in both Battleship and Capital class hulls.
I also wouldn’t be surprised if there were changes to salvage components or the salvage mechanic given the exploration theme of Odyssey. Hopefully further details will be revealed at Fanfest 2013 that will give me more insight into market changes.
The Capital construction project has been running for 163.5 days and so far has generated 18.06 B profit with a hull moving every 2.48 days on average.
April and November are excluded from the average calculation as November was our start-up month and the books for April are still open. The average number of hulls we have been moving has been rather steady, coming in at 13.25/month.
As expected the Thanatos and Archon are more popular followed by the Chimera. I wish I could package tears for our brave Nidhoggur pilots, as it is the worst racial Carrier.
We started Dreadnought production in January-February so I’m not surprised that their movement has been low. Given the impending changes to the Naglfar, that BPO has been put into production and will hopefully become a strong source of income #nag2013.
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.
Wallets from multiple characters are stored into a database table.
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.
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.
– Total Profit per Item
SELECT typeName, typeID, SUM(profit)
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
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
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
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
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
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.
If you have ever owned a POS you know the joy of fueling — or should I say continually ignoring the fuel alerts until your POS is about to go offline.
Since my main characters are pushing 90, 60, and 50 M skillpoints, I forgot what it is like to not be able to perform basic functions like fly a Badger, use a Expanded Cargohold module, or Cargohold Optimization Rigs.
My research alt in the POS owning corp is a character with around 2 M skillpoints focused for ME/PE and remote laboratory work. When I logged on to fuel the POS, I thought the operation was going to be a simple one. Grab a Badger, some fuel, and warp to the POS.
Error. A Badger requires Caldari Industrial to I. After attempting to add that skill, I got a warning that I needed Caldari Frigate III. Uf.
Fine, I’ll queue up all the Frigate skills to III so I can add Racial Industrial to I.
Since the POS was going to go offline in a matter of hours, my solution was to use a Frigate with Expanded Cargohold I modules to temporarily alleviate the problem until a proper Industrial ship could be flown.
I present to you my hauling Imicus. This beast is capable of hauling 129 Fuel Blocks (just over 6 hours of fuel time for a Medium POS), no DPS, and terrible align times.
Quite the humbling experience that brought me back to my first year in Eve when I ‘couldn’t do anything’.
If I had to pick a word to describe the past quarter, I would say “uninspired”.
The Retribution expansion brought little to no change that affected my industrial operations or new styles of play. Hopefully there will be industrial changes in the newly announced Odyssey expansion to bring new life to this area of Eve.
I’ve been very hands off with the Capital construction division, as Raath has taken complete control of the production and sale cycles. Personal commitments have taken over my allotted Eve time. Q1 of the year means a return to a workout cycle that prepares me for summer Triathlon and Open Water Swimming competitions. These sessions increase in intensity and duration leaving me in a wobbly, static state by the time I get home and power up the computer.
Slow and Steady
Daily trading has all but ceased as I’m relying on the Capital construction division to drive revenue. Right now our operation is performing at around +3.3 B/month, which is below my record of 8 B/month. Not bad for passive income, but not ideal.
In late February a stealth change to NPC prices hit the live server. I have not added the 10-11% increase in value to my net worth yet. Since I own around 85 B in blueprints, this will be a nice increase in net worth.
Interesting to see moon minerals on one the Odyssey announcement slides at PAX East 2013. I hope that a serious look at industry balancing is going to take place now that the general population seems pacified by the content in the Crucible, Inferno, and Retribution expansions.
The icons shown are for R64 moons, which have concentrations skewed towards galactic quadrants as shown by data analysis. I can only imagine what they have planned as they have stating their intention is to remove passive static resource fountains such as the current moon mining system.