spotmarket Graph Sample

What’s data without some graphs? Here is a small snip of the graphing that is currently in the spotmarket project.

Data

Jumps
2016-01-13 to 2016-06-07
17,802,640 Rows

Kill
2016-01-13 to 2016-06-07
9,819,421 Rows

Graphs

Jumps in Trade Hubs

2016-06-06_trade_hubs

Jumps and NPC Activity in The Northern Regions

2016-06-06_world_war_bee

Gurista Ratting Rates

2016-06-06_guristas

NPC Universe Overview

2016-06-06_universe_kills

Outer Ring

First pass at a map using D3.js inspired by HelicityBoson at http://www.machine9.net/?p=1111.

2016-06-06_outer_ring

Advertisements

spotmarket – 0.4

Overview

Whew, this was a big step in the right direction. I added a lot of sanity (keep everything in UTC), usability, and cleanup to the existing design. We’ve got moongoo, the beginnings of indexes, and CREST killmail data now flowing in to the growing dataset. The backend was improved by migrating to PostgreSQL 9.5.1 and I also cleaned up the install directions to try to make it easier.

0.4 Release

[bug] Change timestamp for zkillboard and markethistory consumer to use UTC for mental sanity
[enhancement] Add 404 error page
[enhancement] Change to postgres 9.5.1 to support jsonb
[enhancement] Parameterize graph functions
[enhancement] Change date format in graphs to ISO for mental sanity
[enhancement] Standardize table formatting
[enhancement] Supervisor to make Flask web service persistent
[enhancement] Add jsonb index on data.killmails for typeID and solarSystemID
[enhancement] Add paging to zKillboard consumer
[enhancement] Add check to resume from last recorded page for zKillboard consumer
[enhancement] Add basic exception handling to zKillboard consumer
[new] Creation of ship index report

 

2016-03-07_jumps2016-03-07_pirate_index2016-03-07_region_fountain2016-03-07_market_pilot_services

CREST Verified Moon Minerals

It is no secret that I have an affinity for finding accurate moon data. In a previous post, I’ve gone so far as to chart the regional density of each moon mineral, showing that there were built in skews as to where each type was placed for each class (R8, R16, etc).

In the Rubicon expansion, CCP introduced siphon units, which can be anchored near a POS to slowly leech items from within the shields. When an extractor is destroyed the killmail will show what moon mineral was in it. This, combined with the x,y,z location data that started to be exposed after the Parallax expansion, can be used to locate the nearest celestial object to give us a verified report of what moon mineral is contained in the object.

I gathered some examples for anyalisis, and over a long flight, I started to put some code together to use killmail x,y,z coordinates to parse CREST killmails. After implementation and testing, it has proven to be accurate so I’ve included the feature in this release. There is a manual step to get the moon mineral data into a table where it is used in the web frontend; I wanted to keep them separate to isolate the two datasets.

PostgreSQL Upgrade to 9.5.1

Rather than writing a parser for CREST killmails, I decided to store the JSON itself for simplicity. Developing on 9.3 with the json datatype proved to be a path that I did not want to go down. 9.3 is not the prefered version and lacked support for a fancy datatype inherit to 9.4+ and above. The following blog post also convinced me to migrate.

The proof is in the numbers so here’s a side-by-side comparison of the same query on the same dataset with only the datatype being different. The two virtual machines also had access to the same amount of CPU and RAM to control the results.

2016-03-13_postgres_9_5_performance_1

This query was proof enough that I made the right choice. Later that night while checking my logging table, I also noticed an improvement in insert speed.

9.3 (json) [kills] insert 2982 @ 93.81 rec/sec
9.5.1 (jsonb) [kills] insert 2850 @ 124.44 rec/sec

Not bad for about 45 minutes of testing and correcting install instructions.

0.5 Release

What’s up for the next release? Check out the TODO.md for a full list of items slated for each release. The main focus is going to be putting more control in the web front end, letting you enable/disable import items, add item to the market/zKillboard watch list, etc.


spotmarket on Github

initial commit

I published the initial code to GitHub with install directions for Ubuntu. The project so far is a Python script that consumes map API jump and kill activity and inserts them into a PostgreSQL database. Details as to why I picked these two items as starting points to come later.

https://github.com/brentnowak/spotmarket

So far this has been a fun learning project; I’m getting more familiar with Python datatypes/iterators/quirks, have been leveraging arrow for any datetime work, am getting more comfortable with pandas, and have a handle on the basic differences between MySQL and PostgreSQL.

Growing Laundry List

  • Frontend – The previous project was written in a PHP framework and I have some basic JavaScript under my belt, but I am historically more of a backend/server focused person so web presentation is a weakness of mine. Django has been mentioned to me so perhaps I will do some research.
  • Implement more efficient UPSERT to improve database insertion efficiency.
  • Figure out what CREST market data to record.
  • Start working on putting the pieces together for the first analysis — more on that later.

Screenshots

Jita Jumps

2016-01-21_backup_instance_jita

Presenting data with Python Pandas.

2015-01-14_venal_ratting_df

Manually extracting the data to a csv and charting it in D3.js for now.

2016-01-18_npc_graph_1


Burn Jita 3: Day 1

Burn Jita got off to a great start with freighter kills coming in one after another. I was able to watch a few hours of NickFuzzeh’s stream yesterday and was able to get some nice screencaputres before it went offline; image credits go to him.

If you are looking to keep up to date on the kills, head on over to use isjitaburning.com.

Screenshot 2014-04-25 13.28.00

Screenshot 2014-04-25 13.28.15

Screenshot 2014-04-25 13.30.04

Screenshot 2014-04-25 13.30.50

Screenshot 2014-04-25 14.46.15

Screenshot 2014-04-25 15.25.53

Screenshot 2014-04-25 15.26.13


Historical Profits by Solar System

Overview

CCP has released two out of the six Devblog posts aimed at industrialists detailing changes for the upcoming Summer expansion. We’re seeing sweeping changes to the way logistics are done for capital ships, station research, POS anchoring limitations, BPO security concerns, and how inventors are going to be given a boost with BPC copy rates. Lockefox has a great summary of the concerns in his Everything Is Changing post.

Given the ideology coming out of the Dev Blogs to empower Nullsec industrialists while kicking Lowsec in the knees, I wanted to see how much of my industrial gameplay occurs in Lowsec — Is CCP killing my game?

Data Data

Summary of profit grouped by solar system with profit numbers obfuscated.

— Get Profit per Station By Solar System including Region
SELECT SUM(profit), mapDenormalize.itemName, mapSolarSystems.solarSystemName, mapRegions.regionName, AVG(mapDenormalize.security)
FROM wallet
JOIN mapDenormalize
ON (wallet.stationID = mapDenormalize.itemID)
JOIN mapRegions
ON (mapDenormalize.regionID = mapRegions.regionID)
JOIN mapSolarSystems
ON (mapDenormalize.solarSystemID = mapSolarSystems.solarSystemID)
GROUP BY wallet.stationID
ORDER BY sum(profit) DESC

2014-04-18_profits_by_solar_system

It turns out that Lowsec only accounts for 7% of our profits to date so I can’t complain about the nerf that is going to hit Lowsec capital builders given the upcoming compression changes.

2014-04-18_profits_by_solar_security

Conclusion

The changes to compression are a welcomed change, even if it means retiring or heavily modifying the logistical chain for Lowsec capital production. I have a feeling that there are going to be more major changes in the next four upcoming Devblog posts. Stay tuned.


Nullsec Trade Hub Profiteering

Overview

The best way that I have found to make money for the lest amount of effort is stocking nullsec hubs. Forget mining, mission running, invention, or any type of item or ship production. If you spend time doing market research, planing your logistics, reducing your risk, and monitor sales you can make far much more than any other type of activity in Eve.

What follows is a guide for stating up your own operation to stock the warchests of eager PVP pilots (at a profit of course).

I’m using three years of trade and logistics experience to help me outline the operation. Your style may differ, but here is the outlay that has worked for me time and time again despite political change.

If you can parse information in the form of Dotlan map statistics, notice price disparity between two locations, have a basic understanding of jump mechanics, learn to avoid common ganking techniques, and maintain a basic level of proficiency in Nullsec politics you can easily break into this style of gameplay.

tl;dr

Buy low in Jita, move things to nullsec, and sell for a profit. To grow bigger take the profits generated and reinvest in your operation to increase its product scope and logistical power.

Disclaimer

In order to properly reach several places and reduce the amount of midpoint jumps, you will need Jump Drive Calibration V. If you don’t have this yet, go train it as it is one of the most valuable skills for jump capable pilots.

A trade alt should have polished skills to reduce taxes and increase capacity, Accounting and Broker Relations to V and then work on increasing the number by getting Tycoon IV.

Start-up capital may vary. I started my operation with 2 B ISK and have grown from there so tailor the scope of the operation to your level.

Objectives

  1. Always look to reduce risk. This extends from not putting too much ISK into the operation in case it fails, not getting a hauling ship ganked, keeping assets under your control, or even not telling people about your timetable and route for logistics.
  2. Because time is money you want to reduce time sinks. The end goal for this operation is to spend as little time as possible managing it and more time doing other activities. For me that meant more market research and blogging, but for the majority of people I imagine funding endless PVP activity is the goal.
  3. Improve sales by using a program to produce reporting and business intelligence out of your data. EVE HQ, Eve Mentat, or EMMA are all good solutions, but I spent a lot of time flexing my PHP muscle to build my own wallet manager. Goonswarm has built their own import tool called Goonmetrics for people that lack the technical expertise of setting up databases and operating code.

Station Selection

Finding a suitable hub depends on many factors including traffic, location, and the ease of logistics getting into the destination system.

  1. To maximize your ISK velocity, find a place with a lot of traffic. Use the filters on Dotlan and the in-game map to discover numbers for the number of pilots docked and  jumps per 24 hours. Major nullsec hubs will standout on the filter with thousands of jumps per day.
  2. Find an Alliance and location that is politically stable. When assets are moved into a conquerable station, you run the risk of loosing access to that station. If this happens, you will need to keep your trade alt docked and either slowly sell off your items or firesale them to quickly liquidate. Do keep in mind that without notice major shift in power can occur; look up the history of Band of Brothers or GoonSwarm to discover how powerful the click of a mouse button can be.
  3. Consider the logistical hurdles to getting to the destination station. More midpoints means more time spent doing logistics and the lack of midpoint stations can mean the use of alternative jumping methods as outlined in the Jump Technique #2 section.
  4. Check cynojammer status for your destination and midpoint systems. Some alliances will jam their system to reduce the likely hood of enemy capitals entering. Others alliances won’t care and have found joy in the opportunity of a large-scale capital engagement in their home system (ie Titan battle of August 15 in K-6K16).
  5. Alliances have many CTAs so get on Jabber, look at forum posts, or talk to PVP people about where they are staging. You want to stock where things are blowing up. Note: stocking a staging station for a campaign is very risky and I would not recommend this type of stocking for people that lack heavy logistics to move goods out at a moments notice due to the sometimes fickle nature of alliance leadership decisions and campaign politics.

Hub Ranking

Using a number of tools, you can find and target the best Nullsec trade hub. Element43 has a market filter that displays active orders per station.

If you have basic knowledge of Nullsec politics, the following systems will standout immediately: VFK-IV, K-8SQS, K-6K16, HIX4-H, and 6VDT-H. These are home systems for many of the major alliances.

Character Placement

The more cyno alts the better. Moving characters around will cost time and slow you down. In my operation I have 5 cyno capable alts around the galaxy that shift depend on where I am currently operating.

A trade alt will remain pertinently docked in the Nullsec destination system and only exit station to light the occasional cyno.

I have a character in Jita that holds ISK and does all of the purchasing. This helps isolate all the purchases to one character to keep my bookkeeping tidy.

Logistics Overview

The goal of  the operation is to move items from a Highsec trade hub, to a Highsec station system next to a Lowsec station system, and then into Nullsec by means of either zero or many midpoints.

2012-11-30_heavy_logistics_example

This illustration shows two legs on the operation. The Highsec and Lowsec activity can be handled by a NPC character while the Midpoint and Nullsec end is dedicated to your Alliance character.

I use Jump Freighters in this example, but based on your capital the number and types of ships, each setup will vary. When I started my operation I used standard Industrial ships to get around Highsec, a Blockade Runner to get into Lowsec, and a Carrier to perform the cyno operations into the destination system.

Logistics Stage 1: Product Selection

There are many ways to find profitable items and I’ve already published some of the things that are very volatile in nullsec in this post.

Logistics Stage 2: Getting out of Jita

A proper Jita insta-undock bookmark is a valuable things. Using this will reduce a large amount of your risk as it will place you off of the Jita undock grid within seconds.

Logistics Stage 3: Getting Across Highsec

There are many options for transporting across Highsec that include doing it yourself or outsourcing with services such as Red Frog Freight. The volume, time, available capital, and risk will dictate what method to use.

If you are just beginning your operation, you will have to be frugal and move items with a Industrial. Later you can move on to Blockade Runners (Crane, Prorator, Viator or Prowler) since their cargo area is unscannable as of the Retribution patch.

Though scannable, an Orca has a large tank and can be used to transport larger amounts of goods than Blockade Runners. Freighters do have a large amount of capacity, but they are often targets for ganking activity. If money is no object, choose to use a NPC Jump Freighter to move items as they offer a larger amount of HPs and align faster than normal Freighters.

Logistics Stage 4: Getting from Highsec to Lowsec

This move can be done with standard Industrials, Blockade Runners, Orcas, Freighters or Jump Freighters. The riskier methods involve scouting the gate on the Lowsec end and moving goods only when clear.

The fastest m3 per time method is to have your goods staged in Highsec in the adjacent system, light a cyno on the Lowsec station, jump to Lowsec, dock, unload, undock, align to the Highsec gate, and enter warp when you reach velocity returning to the Highsec system to begin the cycle again.

Note that I stated to align and not Warp To as a method for killing Jump Freighters is to bump or web reducing the maximum speed below the current speed, effectively locking you in the Warp operation. Read up on the ‘Vindicator Triple Web’ exploit if want to know more about this exploit. If you don’t issue the Warp To command and are aligning, at any sign of trouble you can dock.

Logistics Stage 5: Getting Across Lowsec

Use the Station to Station technique outlined later.

Logistics Stage 6: Getting Across Nullsec

Use the Station to Station technique outlined later.

Logistics Stage 7: Destination System

Getting into the destination system is the last leg in the journey and the technique used will depend on the Alliance’s use of a Cynosural System Jammer or  Cynosural Generator Array  POS module.

The D2-HOS system in Pure Blind was FA’s capital system and jammed. The best route in was jumping into a adjacent system and scouting the gate. Higher risk, but at the time it was worth it.

Before Widly Innapropirate were absorbed into GoonSwarm, they homed in G8AD in Cloud Ring.  They had an active beacon. If you scouted the system and had Carrier backup, your risk was reduced.

The best method is to find a major Nullsec hub that lacks a jammer so your trade alt can undock, light a cyno within docking range, and let your ship land right within docking range. This technique reduces your risk to almost nothing if done properly.

Jump Technique 1: Station to Station Jumps

The most efficient, low risk method of moving items between Lowsec <-> Lowsec, Lowsec <-> Nullsec, or Nullsec <-> Nullsec system is to have two characters in two systems light cynos within docking range of each respective station.

The Jump Freighter will jump in, dock, unload, undock, and jump back to the original cyno. If you are quick, you can get 5-6 jump cycles under one cyno timer. Done efficiently this method can move around 1,750,000 m3 per cyno depending on your Freighter skill level and racial Jump Freighter, both which affect the ship’s cargo capacity.

Jump Technique 2: POS Container Jumping

If you absolutely cannot get a station system as one of your midpoints, you can use a riskier method using an anchored container and a POS with a Cynosural Generator Array module. The goal with this method is to jump in and warp to 100 km to the anchored container, landing you inside the POS shields.

warp_point1

If done correctly, you will only be vulnerable for a few seconds as your ship aligns and warps withing the POS shields. I do, however, recommend that you only jump into the system when it is empty with a character in the system to monitor activity. There is still a chance of an enemy logging off next to the Cynosural Generator Array and logging back in as they see you leave the originating system, but it is low.

When I would perform this type of operation I had a Carrier on standby within jump range of the POS system. If anything went not according to plan, the Carrier came into system to serve as support while the POS guns fended off the attacker or Alliance reinforcements arrived.

Jump Technique 3: Right Out of Jita

A quick, lowrisk method for moving billions out of Jita involves bypassing Highsec altogether as a large number of systems are jump accessible right out of the Jita undock as seen here.

Using this technique, you can put a large amount of ISK into your NPC Jump Freighter and bypass the deadly gank systems of Uedama and Niarja.

Concrete Examples

Here are two route examples of when I would stock for the former Northern Coalition. The three Lowsec systems of Chardalane, Maut, and Oulley served as three options for extending myself into Nullsec as they were each Lowsec station systems with a connection to contiguous Highsec. If either system was busy with cyno popping pirates, I would switch to one of the other two options.

Operation Details

  1. Buy things in Jita and place 5-10 B in a Courier Wrapped Freighter Container on my Jita purchasing character.
  2. Undock with a NPC freighter character and move to Adacyne, the Highsec system next to Chardalane.
  3. Use a NPC Jump Freighter to move goods from Adacyne into Chardalane Lowsec.
  4. Use a Alliance Jump Freighter to jump into D2-HOS or G8AD-C.
  5. Hand off goods to my Trade alt in the station and price items for sale.

2012.02.09.00.32.06

2011.11.26.01.34.202011.11.26.01.34.52

Route for D2-HOS in Pure Blind for Fidelas Constans before they moved to Branch.

jita_to_d2

Here is the route for G8AD-C in Cloud Ring for Wildly Inappropriate before they were absorbed into Goonswarm Federation.

jita_to_g8

Results 

If you look back at any of my monthly reports, you can review the success of my efforts. My recommendations for people that want to get involved with Nullsec trade and production is to always keep one eye on Nullsec politics. Take notice of who is attacking, defending, advancing, and maintaining homogeneity; the key to your success is situational awareness.


War Declaration Profession

Our Highsec industrial operation had a minor setback last week as we logged on after a few days out of game to discover an active and then closed war declaration.

2013.02.14.15.39.12-1

We had a Highsec POS setup a few jumps out from Jita and it looks like we were targeted by a group of people looking to clear our tower. It seems that there is a profession out there where you hop corporations clearing POSes from Highsec.

After researching their activity on Eve Who, Eve-Kill, and strike pattern on Dotlan, I haven’t found any reason that we were specifically targeted besides our proximity to Jita. I’ve also been unable to link a sale of a open Highsec moon on the forums to them.

2013.02.14.15.39.53-combined

Always remember to write off the cost of a POS as soon as you purchase it. Assume you will not be recovering it as random acts like this can happen at any time.

Nothing of value besides the POS and modules was lost as all BPOs were kept in the Corporation office in a station and researched remotely.