A big blunder in my favour

Seems blunders cut both ways . . . . . doubtless i will be “repaying” this blunder back at some point in the future!

 

I logged on and noticed that my Dodixie trader had made sales of 2.4bn since i last checked and the biggest single sale was a Pith A-Type Explosive Deflection Field which sold for 1.55bn ISK.

 

Upon investigation, i discovered that the sales price for this item was actually only 34m ISK where as the best buy order was sitting at 15.5m ISK.



 

 

Hence, what happened here i suspect is that the Buyer meant  to put on a buy order for 15.5m ISK but added an extra “0” into the order and so created a buy order for 1.55bn ISK - and therefore bought one of my sell orders where was up for a mere 34m ISK.  Painful.

 

And just to add to the matter – the character that bought the item had been playing since September 2008!

 

Excel reading prices from Eve-Central - step by step guide

I have had lots of questions referring to my post on automatically updating Jita prices in my excel spreadsheets where I pointed readers in the direction of the Fuzzwork blog written by Fuzzysteve where he talks about this exact same subject and gives examples.

So, for completeness of my blog below is my step-by-step guide on how to import prices from Eve-Central onto your spreadsheet - and hopefully this will work for those who don't like to use Excel beyond its basic functions!


The Aim:

To build a spreadsheet on Excel 2007 to download the prices of 1 item in Jita from the Eve-Central website.


The Set-up:

The Workbook (in the lingo - a workbook contains a number of worksheets, we would call "tabs") will have 3 worksheets

a worksheet with all the item IDs (known as typeid) - call it "Type Ids"

a worksheet that downloads the prices from Jita - call it "downloaded data"

a worksheet that selects the pricing from the data that is downloaded - call it "workings"


Step 1: create the worksheet with all the typeid's

this step need only ever be done once - or when new items are added to the game.

the Eve-Central database, and indeed every Eve Online data base works off typeid's, not their name.

Therefore, we need first to download the Items Names and IDs so that we can determine the typeid if we are given the Item Name.

You could just look the item number up everytime online - but that is very time consuming given this step is quick to do and needs only be done once.

Fuzzysteve has provided an excel spreadsheet that can be downloaded - right click the link and "save as" to your desktop, and open it.

The downloaded spreadsheet will look in part like this:

copy and paste the entire spreadsheet onto your worksheet called "Type Ids".

as you can see on the spreadsheet - the typeid is in column A and the item name is in column C.


Step 2: create the worksheet that downloads the prices from Jita

This is the where the action happens - and call this worksheet "downloaded data"

For this example we will download the price of Tritanium.

there are 2 parts to this step - the first part is deriving the URL to download the data, the second part is downloading the data.


Deriving the URL:

What we are aiming for is the URL http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

where "typeid=34" is the typeid for Tritanium
and "regionlimit=1000002" is The Forge Region

. . . . . in other words, we will be downloading the price of Tritanium from The Forge region.

Given we will always be looking at prices in The Forge (which contains Jita) then the only piece of the URL that will change is the typeid.

How you insert the correct typeid is up to you but I use the excel MATCH and INDEX functions.

on my worksheet "downloaded data", in cell F137 is the name of the item I am looking for, in this case Tritanium.

and then in cell E137 is this formula:

=INDEX('Type Ids'!A:A,MATCH(F137,'Type Ids'!C:C,0))

. . . . .what this does is look down column C in the worksheet "Type Ids" for the name in cell F137 (in this case Tritanium) and then returns the value that is in the same row that is in column A on that worksheet.  I.e. MATCH looks up the row number and INDEX then reads the value in the other column which is on that row.

Then in cell D133 I have the formula
 
"http://api.eve-central.com/api/marketstat?typeid="&E137&"&regionlimit=10000002"

. . . . in other words, it returns the URL
http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

the final step in this part is to then copy that URL and paste it in the line below as a "value" - in other words, copy the value it gives to another cell, not the formula.

downloading the data:

now copy the URL that was derived, i.e:

http://api.eve-central.com/api/marketstat?typeid=34&regionlimit=10000002

on the same excel worksheet called "downloaded data":

1. select the work tab "Data" from the top row of menu items
2. make sure the cell selected is where you want to download the data to (so for the first one in cell A1)
3. press "From Web" which should be the second item along below the menu items at the top
4. This will open up your home webpage on the spreadsheet (for me google)
5. replace the URL with the URL you have saved - as shown in the above picture
6. Press "Go" or Enter / Return

This will then change the webpage to:

Now press "Import" to download the data for the first time, and you will be presented with this box:

the cell $A$1 is the position of the current selected cell, so change that if you want to, then press "OK"

that will then download the data which will fill the row from column A to column X.

what each column represents, assuming you started in column A is:

A	version
B	method
C	id
D	volume
E	avg
F	max
G	min
H	stddev
I	median
J	percentile
K	volume2
L	avg3
M	max4
N	min5
O	stddev6
P	median7
Q	percentile8
R	volume9
S	avg10
T	max11
U	min12
V	stddev13
W	median14
X	percentile15

The sales price I look for is in column Q "percentile8".


Step 3: create the worksheet that selects the pricing from the downloaded data

and this is the final part

on the third worksheet in your workbook called "workings" you make a formula to select the cell in column Q,

again, I use the INDEX and MATCH excel functions to find what I am looking for.

=INDEX('Downloaded Data!Q:Q,MATCH(B153,'Downloaded Data!C:C,0))

where the cell B153 on my "Workings" worksheet has the typeid for Tritanium (which is 34) and the MATCH function would then find it in column C of the "Downloaded Data" worksheet in row 1 and the INDEX function would then read the value in row 1 in column Q of the "Downloaded Data" worksheet.

. . . . .and there we are.


From here on in, to update this data press the Refresh button at the top - that will then update all your downloaded data points:

To add more items to download merely go through Steps 2 and 3 again!

A collection of useful youtube videos for making ISK

The links below are to youtube videos that I have found helpful in my quest to make ISK.


Trading

Draze Swift Products has a good long 1hr 20mins video on station trading in his Eve online Videos in this play list.

A good Station Trading video from TubravTV

Three trading videos from Caleb Ayrania in this play list.



Manufacturing

Pretty much all Eve online videos by andrewthered1 in this play list.

A manufacturing video from delonewolf

A Tech 2 manufacturing guide from speedytuning1 and some further manufacturing videos in his playlist



General business / industrial activities

A 20 minutes video by delonewolf talking about how he made his ISK

A general ISK making video by KensCrazyGaming on how he makes his ISK

In amongst all these videos, there are some good ISK making videos from SeamusDonohueEVE

Manufacturing - 2 months in

Its been a while since I have written exclusively on my manufacturing alt.

I have been very pleasantly surprised at how profitable manufacturing is compared to my initial expectations.  October was my first full month running at speed and I made profits of 2bn ISK whilst spending only 10 minutes per day at the most on it.

I am halfway through the current month and I have made 1.7bn ISK profits.

Of all my activities I believe manufacturing is the leasst time consuming time of financing the purchase of Plex.

My strategy is similar to that I adopt in trading: I focus on the low volume, high margin items.  The 0.01 players leave these alone given the low volume of sales per day.  I don't focus at all on the high volume items such as ammunition.

I have a spreadsheet set up on Excel 2007 which downloads the Jita prices (from Eve Central) of the items I am selling and all the raw materials to compare the profit margins and so indicate to me which I should be producing.  In all, this spreadsheet monitors 49 items so far, and the list is growing.

At present I regularly sell 26 items which at the present time is making me profits of 2bn ISK per month - hopefully 3bn this month but we will see, I am away for over half the month on business.  If I sell 3 items a day giving me 35m ISK profit each then I am a happy pilot.

I own over 1bn ISK of Blueprints (which I class as expenses and hence the 2bn Profits in October covered the costs of these Blueprints).

I sell in Jita and I manufacture in the Lonetrek region.  The Lonetrek region is right next door to Jita and there are always plenty of spare manufacturing slots.

I am expanding my operations into other regions to see if I can enhance my sales - so far it has started slowly but it is early days.

When I started manufacturing it was industrial ships - as of today, they would all be loss making.  Has been over a month since I sold my last manufactured ship.

The great part about manufacturing is that so far it is very low maintenance.  Even assuming I need to buy materials to manufacture 11 items + ship them to my manufacturing station + set up the manufacturing operations + get them to Jita to sell + update all sell orders then at the most it is 10 minutes.

I greatly suspect I could have financed the ongoing purchase of Plex if I had started in manufacturing - but that is not as certain as trading.  The time taken to learn the required skills is perhaps more of a hurdle earlier in the process.

Going forwards I am merely plan to expand the number items I manufacture for now.  I also am learning Spaceship Command skills given I suspect this alt will be the alt that hauls items around once my manufacturing operations expand into other regions.



My current relevant skills are:

• Mass Production 5 and Advanced Mass Production 5 allows me to manufacture 11 product items at once.  Many blogs I read don't learn Advanced Mass Production 5 given the time it takes - but the one downside on being away on business a lot is that time is available to set an alt up to lean a skill that takes 30 days!

• Production Efficiency 5 allows me to use less materials to manufacture items

• Industry 5 allows me to manufacture items quicker and is required to progress in other skills

• Supply Chain Management 5 allows me to start a manufacturing job from anywhere in the same region - I am not really using this at present but I expect to use to more when I move my manufacturing operations away from Jita.

• I then have various skills that allow me to manufacture certain items such as Mechanics 5, Hull Upgrades 2, CPU Management 5, various Rigging skills, Science 5,

• Contracting 2 - allows me to set up 11 contracts to haul my items around

and because this alt places its sell orders to sell its items I have also trained in:

• Daytrading 3 - allows me to modify my sell orders up to 11 jumps away in the same Region

• Marketing 3 - allows me to place sell orders up to 10 jumps away and is required to learn other skills

• Retail 4 and Trade 4 - allows me to place a total of 53 Buy and Sell orders

• Cybernetics 4 - allows me to get the Standard implants for faster learning

Someone's blunder is my gain

I see these types of blunders a lot.

. . . . . as a primer, I am not posting these to mock the people making the mistakes, I make enough myself to last a lifetime . . . . .

as shown in the market window for this Eifyr implant the sell orders are mostly around the 1,288,997 mark and there is a buy order at 260,000.03 ISK.

However, there is a sell order at 260,000.04 ISK.  Clearly, this "seller" meant to post the order as a buy order, and the best buy order at that.  However, they blundered and posted as a sell instead - which is not hard to do if you are in a hurry.

Alternatively, they meant to sell a sell order that was posted at 260,000.04 which maybe had 3 item at that price and the seller put on a sell order for 8 items at 260,000.04 but of course only 3 were filled leaving 5 to go.

 

of course, I took advantage of this blunder and bought these 5 items for 260,000.04 to resell at the current selling price.  a nice and easy 5m ISK profit for no work at all.

 



A week away - not too much of a dent

I came back from a trip that kept me away from Eve for 5 nights and my wealth had increased by 1.6bn ISK (=183m ISK per day) to 32.3bn ISK.  I considered that a good result.

Good sales came from across my alts in Dodixie and Hek (my Regional traders) and my manufacturing alt sitting in Jita.

My Manufacturing Alt is expanding its operation.  It can now have 11 items in production at any one time and it is selling 26 items for good profit margins.  As an experiment I am looking into selling into new Regions to see how that goes.  I am now training this alt up on Space Ship command.  I suspect in time this will be the alt I use to transport items around where I am unable to use third parties.

My Research Alt in Jita, which currently acts as my buyer for my traders in Dodixie and Hek, is moving towards the end game in getting up to speed to start Invention.  To be honest, I could have gone down this route 3 weeks ago but RL is taking up too much time.  This alt is now learning Mechanics 5 and will then learn CPU Management to level 5 (the old Electronics skill) before seeking a place to call home to do Invention.  I expect to do that towards the end of November or early December.  And of course I have purchased another Plex to finance this account for a further 30 days.

In readiness for that day I am starting a new alt - given I will be away for several periods during November it could take a while for me event to complete the starter missions!

Whilst I have found some more expensive items to sell via by traders in Dodixie and Hek my biggest issue remains that I have almost 9bn uninvested ISK in my wallet which is acting as a drag to my growth in wealth.

I only spend 30 to 45 minutes per weekday on Eve - hence I am looking for new items to sell or projects that can use up this ISK for a good return with minimal effort on my behalf.

CCP Financials - Eve Online (ex China) Revenues

This post continues my series looking at the CCP financial position.  The published CCP Accounts are on this page, and my forecasts are on this page.

In this post, I am looking at the revenues of Eve Online (ex China).

. . . . . my game related conclusion would be that would lead me to conclude that we should expect a lot more effort by CCP to keep new players, including enhanced "carebear" protection.

Any analysis of CCP today will always start with the revenues of Eve Online (ex China).  In 2012 Eve Online (ex China) accounted for 98% of the total revenues of CCP.  For now, Eve Online is the business of CCP.

The revenues of Eve Online is somewhat more complicated that subscribers multiplied by price paid.  Indeed, the main drivers of revenues that I have identified are:

1. the number of subscribers per year: it is not clear what CCP discloses but I suspect in their presentations they disclose the number of subscribers during the year (or the number of subscribers present at the year-end).  And for good measure I had to try and read these of a graph.

2. the average number of months each subscriber stays for: whilst many players have been subscribing for many years I suspect there are many many more who subscribe for 1 or 3 months and then give up.  Therefore, the average number of months each subscriber stays for during a year is important.  Its all very well having 355,000 subscribers but if 100,000 of them are only around for 1 month then that is 100,000 new subscribers that have to be found the following month to keep numbers up.  I have not found any indication from CCP how this looks - so I had to analyse it myself.

3. the price for a 1, 3, 6, 12 month subscription: there are different price points depending on the length of the subscription.  These price points are known.

4. the proportion of 1, 3, 6, 12 month subscriptions purchased by the subscribers: follows on from the above point, we need to determine what proportion of the subscribers take each length of subscription.  I have not found any indication from CCP how this looks - so I had to analyse it myself.

5. the mix of revenues that are Plex sales: Plex sales are in part additional revenues to CCP but they also perhaps replace subscriptions.  The ultimate end buyer of a Plex may start a new account that would not otherwise have been started (so no loss to CCP) or may replace their subscription by buying Plex (like I do) in which case CCP is only slightly better off (by Plex price less Subscription price).  CCP has disclosed that 20 - 25% of revenues are from Plex in recent times.  Plex sales started in November 2008.

So, starting with the Plex - it does seem the easiest: given the sales started in 2008 I assumed that 10% of revenues in 2008 came from Plex sales (bumper sales in November and December 2008), in 2009 I assumed that Plex sales accounted for 15% of revenues, in 2010 22% of revenues, in 2011 23% of revenues and then in 2012 24% of revenues - as shown in the table below.  I don't have anyway of knowing this for sure.  CCP tells us that Plex accounts for 20 to 25% of revenues.

Eve Online - ex China
	2008	2009	2010	2011	2012
Sales mix
Subscriptions	90%	85%	78%	77%	76%
Plex sales	10%	15%	22%	23%	24%
	100%	100%	100%	100%	100%
Plex sales	4,166,889	7,767,364	12,470,378	14,293,372	15,062,430
change		86.4%	60.5%	14.6%	5.4%
Price per Plex	19.95	19.95	19.95	19.95	19.95
Number of Plex sold	208,867	389,342	625,082	716,460	755,009
Number per month	17,406	32,445	52,090	59,705	62,917
Additional "accounts"		11%	16%	17%	18%

However, I can do some sense checks.  If I assume the price for a Plex is $19.95 (I know, they can be discounted) then using my assumptions of sales from Plex I can determine the number of Plex sold in 2008 to 2012 and so get a feel for the number per month which would give an indication of the additional accounts that Plex finances (assumes all Plex goes to new accounts in that year - in reality there will be buffer stock and other uses).  In 2010 it looks like 52,090 additional accounts per month = 16% additional accounts, in 2011 59,705 additional accounts = 17% additional accounts and in 2012 62,917 additional accounts = 18% additional accounts.  That seems to look sensible.


and now lets look at the subscription revenues: the table below shows my thinking with my full discussion below it.  The chart below also adds in the Plex sales from above.  Worth remembering, we know the Eve Online revenues for 2008 to 2012, all we are trying to do is break down how the above drivers fit in so we can then extrapolate forwards to 2013 and 2014.

Eve Online - ex China
	2008	2009	2010	2011	2012
Number of subscribers	225,000	300,000	320,000	355,000	355,000
change	28.6%	33.3%	6.7%	10.9%	0.0%
average length per subscriber (mths)	12.0	11.0	10.4	10.1	10.1
Proportion of payment plans taken
Price per subscription for 1 mth	70%	60%	60%	60%	60%
Price per subscription for 3 mths	30%	30%	30%	30%	30%
Price per subscription for 6 mths	0%	10%	10%	10%	10%
	100%	100%	100%	100%	100%
Price per month per plan $
Price per subscription for 1 mth	14.95	14.95	14.95	14.95	14.95
Price per subscription for 3 mths	11.36	11.36	11.36	11.36	11.36
Price per subscription for 6 mths	9.55	9.55	9.55	9.55	9.55
Average subscription	13.87	13.33	13.33	13.33	13.33
Subscription revenues	37,502,004	44,015,060	44,213,158	47,851,724	47,697,693
change		17.4%	0.5%	8.2%	-0.3%
Sales mix
Subscriptions	90%	85%	78%	77%	76%
Plex sales	10%	15%	22%	23%	24%
	100%	100%	100%	100%	100%
Plex sales	4,166,889	7,767,364	12,470,378	14,293,372	15,062,430
change		86.4%	60.5%	14.6%	5.4%
Price per Plex	19.95	19.95	19.95	19.95	19.95
Number of Plex sold	208,867	389,342	625,082	716,460	755,009
Number per month	17,406	32,445	52,090	59,705	62,917
Additional "accounts"		11%	16%	17%	18%
Total Revenues	41,668,893	51,782,424	56,683,536	62,145,096	62,760,123
change		24.3%	9.5%	9.6%	1.0%

Number of subscribers - we know from the chart issued by CCP, assuming I am reading it correctly!

Again, assuming my Plex sales mixes are correct then there are three key steps to get from Subscriber numbers to revenues: average length of a subscriber x proportion of subscribers going for 1 / 3 / 6 month payment plans x the price of the 1 / 3 / 6 month payment plans.  I have assumed that too small a proportion to matter goes for 12 month plans.

Of course, I don't know the answer to this and I cant find anywhere that CCP has disclosed this so I had to make assumptions.  Furthermore, I assumed the introduction of Plex has altered the proportion of payment plans taken up.  I have assumed that players that were long standing but paid monthly were more likely to buy Plex (they probably gather more ISK in game and so have the most to benefit from using Plex to extend their game time) and so the proportion of payment plans for 1 month fell after 2008.

In all, in 2008 I assumed 70% of subscribers paid monthly and 30% 3 monthly - and the average length of a subscriber was 12 months.  And then in 2009 onwards I assumed this moved to 60% monthly plans, 30% 3 monthly and 10% 6 monthly but that the average length of stay fell to 11 months in 2009, trending down to 10 months by 2012 reflecting the very steep learning curve as players give up.

Worth noting that given the subscriber numbers in 2008 and the price points I really struggled to make the revenues numbers add up - either prices were higher, or Plex was higher priced, or more Plex sold in 2008 than I thought.  I was surprised at how high I had to make the proportion of 1 month payment plans.

I have also not taken into account in price discounts given in pre-expansion periods etc.

Hence, by the end of 2012 I am assuming:

- that the average number of months a subscriber stays for is 10
- that 60% pay monthly / 30% 3 monthly / 10% 6 monthly
- that the monthly price for 1 month is $14.95, 3 months  $11.36 and 6 months $9.55
- that the proportion of revenues that are Plex sales costing $19.95 each is 76%

Therefore, to determine 2013 and 2014 I need to assess the likely subscriber growth and how the above changes.

We already know the 6 month numbers for 2013 and using these I therefore assume subscribers grow by 22% to 433,100 in 2013 followed by 10% growth in 2014 to 476,000.  I assume the average length of stay of a subscriber stays at 10 months as CCP makes efforts to lessen the steep learning curve and more attempts to protect new players.  I assume the pricing points and proportion of 1 / 3 / 6 monthly sales does not change.  And I assume Plex sales are 25% in both 2013 and 2014.  That, therefore, leads to the Eve Online revenues below which then feed into my forecasts on this page.

Eve Online - ex China
	2011	2012	2013F	2014F
Number of subscribers	355,000	355,000	433,100	476,410
change	10.9%	0.0%	22.0%	10.0%
average length per subscriber (mths)	10.1	10.1	10.1	10.1
mix
Price per subscription for 1 mth	60%	60%	60%	60%
Price per subscription for 3 mths	30%	30%	30%	30%
Price per subscription for 6 mths	10%	10%	10%	10%
	100%	100%	100%	100%
Price per month
Price per subscription for 1 mth	14.95	14.95	14.95	14.95
Price per subscription for 3 mths	11.36	11.36	11.36	11.36
Price per subscription for 6 mths	9.55	9.55	9.55	9.55
Average subscription	13.33	13.33	13.33	13.33
Subscription revenues	47,851,724	47,697,693	58,322,675	64,154,943
change	8.2%	-0.3%	22.3%	10.0%
Sales mix
Subscriptions	77%	76%	75%	75%
Plex sales	23%	24%	25%	25%
	100%	100%	100%	100%
Plex sales	14,293,372	15,062,430	19,440,892	21,384,981
change	14.6%	5.4%	29.1%	10.0%
Price per Plex	19.95	19.95	19.95	19.95
Number of Plex sold	716,460	755,009	974,481	1,071,929
Number per month	59,705	62,917	81,207	89,327
Additional "accounts"	17%	18%	19%	19%
Total Revenues	62,145,096	62,760,123	77,763,567	85,539,924
change	9.6%	1.0%	23.9%	10.0%

In all, I expect revenues for Eve Online to grow by 23.9% in 2014 to $77.8m and then by 10.0% in 2014 to $85.5m.

The main driver may look like subscriber growth.  For every additional 1% of subscribers the revenues rise by 0.8% (=$637k).

However, if CCP managed to increase the average subscription length by half a month to 10.6 months then revenues would rise by 5.0% (=$3.8m).

Indeed, if CCP managed to increase the proportion of Plex sales from 25% to 26% of revenues then total revenues would rise by 1.4% (=$1.1m).

Hence, subscriber growth is not the only focus - the more CCP can do to keep new players and convert players to starting new accounts with Plex then the better it is for CCP.

Keeping everyone for half a month longer is better than adding an additional 4,000 new subscribers.

. . . . . . that would lead me to conclude that we should expect a lot more effort by CCP to keep new players, including enhanced "carebear" protection.