Spreadsheets used in the alpha to omega challenge - the item universe finder sheet

I used two spreadsheets in my challenge (technically two worksheets on the same spreadsheet) that both made heavy use of the Eve Excel Addin.

The purpose of the spreadsheet was to keep a track with what i had for sale, what had been sold, what spare Sale slots i had and what items i should buy in Jita to put up for sale in Dodixie.

In this post i will talk about the Universe Finder Sheet.

Eve Excel Addin

I have previously covered using the Eve Excel Addin in other posts - so i wont do it here.  So for now i wont talk about how to install it but i will go through the formulas i used.

Suffice to say, it installs like any other excel Addin and you need to connect your characters to it using the easy to follow and intuitively labeled button "Add a Character".  You need to do this for all characters you want data on, not merely Accounts only.

The Universe Finder Sheet

Get yourself a cold drink, a spare 30 minutes and some patience to follow all this . . . . . 

The purpose of this worksheet is to find items for sale in Jita that cost up to a certain amount (so 1 million isk initially and then up to 400 million isk by the end of the project) that also could be sold in Dodixie for a margin of at least 20%.

It is this worksheet that created the universe of items i would then review every night to decide what to buy in Jita to sell in dodixie.

Therefore, there are three parts to this worksheet.

Part 1) a list of potential items taken from the ingame market to assess.

Part 2) few columns using the excel Eve Addin that shows the sell price in Jita, the sell price in Dodixie and therefore the margin

Part 3) a final column that selected all items from Part 2) that cost up to the maxium limit (say 1 million, or 400 million, or whatever) that also achieved a 20% margin but also sold enough volume to make it worth while.  Enough Volume = at least 1 item sold per day at the start, or 1 item sold every 4 days at the end.

Getting the list of potential items for Part 1)

Quite easy, comes from the ingame market browser.

What we need to do is use the copy and paste ability from the Quickbar to the Spreadsheet.

Firstly, clear your Quickbar by clicking the bin button on it.

And then go to the "Browse" tab and lets assume we are pulling down all the blueprints to assess.

In this case, right-click on the Blueprint item and select "Add Group to Quickbar Root"

This will give you the Blueprint item in the Quickbar:

And then it gets quite tedious, you need to single click on each horizonal arrow to expand all the folders and sub folders such that every single blueprint is showing.

And then select all (ctrl A) and then copy and paste it all into a spreadsheet (ctrl C, ctrl V).  You will find that only the items are copied over, not the folder names.

Assessing the items to determine the sales price in Jita, sales price in Dodixie, and therefore the profit margin for Part 2)

6 columns, the first being the list of items we will assess.  The second being the Eve Online readable version we need for the Addin to pickup the prices.  The third being the sell price in Jita.  The fourth being the sell price in Dodixie.  The fifth being the profit (= Dodixie Sell price - Jita Sell price).  The sixth being the profit margin = profit / Dodixie Sell Price = (Dodixie Sell price - Jita Sell price) / (Dodixie Sell Price)

The number in Yellow is the Dodixie id number.  I could have hard coded it into the Dodixie formula.

Column C = list of items we got in Part 1.

Column D used the formula =IFERROR(EVEONLINE.INVENTORYSEARCH(C4,1),EVEONLINE.INVENTORYSEARCH("'"&C4,1))

A few things in this formula.  C4 is the item in cell C4.  The 1 after the C4 is just telling the formula to find the exact item in C4 and not all strings.

The second part of the IFERROR statement addresses the matter where some items start with an " ' " where an excel spreadsheet struggle to deal with initially.

Column E then uses Column D to get the minimum Sell price in Jita.

=EVEONLINE.MARKET_ORDERS_STATS(10000002,D4.id).sell.min

so, the 10000002 is the id for the region "The Forge".  and so this formula technically pulls up a list of all Sale prices of the relevant item in The Forge, and then the final part ".min" selects the lowest sale price.

Column F does the same for Dodixie.

=IFERROR(EVEONLINE.MARKET_ORDERS_STATS($F$2,D4.id).sell.min,"")

where $F$2 refers to the number in yellow which is the Sinq Laison region id which is where Dodixie is.

And so Column G and H are mathematical as explained above.  G = F-E, H = G/F.

From this getting the list of items to use as potential ideas to buy in Jita

This part is pure spreadsheet stuff.

My inputs in yellow: the top cell in yellow in this case was the minimum Jita sale price i was looking at, the next cell was the maximum sale price in Jita i was looking at, and then the % number in yellow was the minimum market i was considering.

So in this example i am looking for all items that sold for more than 10 million and less than 400 million with a profit margin of at least 20%.

And therefore in Column L i am merely listing all items from Columns C to H that meet the above criteria.

And quite simply i was using Column I to identy all items that met this criteria

=IFERROR(IF(AND(E4>$I$1,E4<$I$2,H4>=$J$1),1+MAX($I$3:I3),0),0)

and so Column L to list all items based on a simple number list in Column K
=IFERROR(INDEX(C:C,MATCH(K4,I:I,0)),"")

In the case of the 587 blueprints that i assessed, there were 30 that sold between 10m to 400m in Jita and had a profit margin of at least 20% if sold in Dodixie.

the final step is to then put those 30 into the Quickbar (copy and paste, Ctrl C then Ctrl V) and cycle through them all looking for items that sell once a day, or later on in the project once every 3 or 4 days, and that have very little competition.

Delete items from the Quickbar that fail the volume test and keep the rest in another list on your spreadsheet as a potential source of items to consider to buy from Jita to sell into Dodixie.