Eve Excel Addin Formulas: Characters connected to the Addin and Character Location

I would have thought that the formula to find out the location of one of my characters would be very easy.

It can be done, it is not easy, it is in the second section two thirds of the way down in this post.  There has to be an easier way of doing this.

But first lets look at the Characters connected to the Addin given this is a key requirement.

Very simple formula

=EVEONLINE.ACTIVE_CHARACTER()

This will list all Characters that are connected to the Addin.

adding the .id method will give all the character IDs, a useful way of seeing all your character IDs.

=EVEONLINE.ACTIVE_CHARACTER().id

and to get a list of where all these characters are currently located firstly get the location id and then use the EVEONLINE.STATION formula to get the station name.

so

=EVEONLINE.ACTIVE_CHARACTER().location_station_id

gives the list of station_ids where each character is located.

And to therefore get what station each is logged into i need to reference each individual cell in the above column using the EVEONLINE.STATION formula.

and so

=EVEONLINE.STATION(G4) where cell G4 is the location_station_id from above, G5 the next one etc

So, how to get a character location in one formula?

It can be done but it seems very complicated.

Assume the character name is on cell D4 (which can be found using the formula EVEONLINE.CHARACTER(A4) where cell A4 is the character ID), then the formula to find the character location is:

=EVEONLINE.STATION(SUM(IFERROR(EVEONLINE.ACTIVE_CHARACTER().location_station_id,0) * (EVEONLINE.ACTIVE_CHARACTER().name=D4.name)))

Lets break this down - three are 3 parts to all this:

EVEONLINE.ACTIVE_CHARACTER().location_station_id

This is as above, it returns a list of the station IDs where each character connected to the Addin is.

Some of these stations, if player owned, will be an error and so throwing an IFERROR around it will return zero if this happens, hence:

IFERROR(EVEONLINE.ACTIVE_CHARACTER().location_station_id,0)

the second part:

EVEONLINE.ACTIVE_CHARACTER().name=D4.name

this compares the name (in plain text) for every character connected to the addin with the name in plain text for the character you are looking it and returns a list of TRUE and FALSE, where only TRUE is for the character you are looking at. (So, this is a list with only one TRUE in it and the rest of the items are FALSE).

In excel terms, TRUE = 1 and FALSE = 0 and therefore multiplying EVEONLINE.ACTIVE_CHARACTER().location_station_id to EVEONLINE.ACTIVE_CHARACTER().name=D4.name will return a list of zeros and one station ID where your character is.

So throwing a SUM around all that adds up the list of zeros and the one Station ID to give the Station ID.

So the final part is to use the EVEONLINE.STATION formula to get the station your character is logged into.

That was complicated!

Footnote

And it is weird.  the range of methods that can be used on the EVEONLINE.ACTIVE_CHARACTER() formula are different to the range of methods that can be used on the EVEONLINE.CHARACTER() formula.  Thats what makes it all complicated.  The EVEONLINE.CHARACTER() formula does not give the option to find the station location of the character.

Eve Excel Addin Formulas: Character Name, Skill Points.

Two simple but almost always required formulas.

Assume the Character ID is in cell B9.

Character Name

=EVEONLINE.CHARACTER(B9)

Very simple, nothing more to say.

Total Skill Points

=SUM(EVEONLINE.CHARACTER_SKILLS(B9).skillpoints_in_skill)

lets break this down.

EVEONLINE.CHARACTER_SKILLS(B9) will list all the skills a character has

adding the method .skillpoints_in_skill will then show the skillpoints in each skill

(for what its worth, i think it shows the skill Advanced Planetology with zero skill points because i once had it but extracted all the skill points in a skill farming routine.)

and the final part is to sum this list:

SUM(EVEONLINE.CHARACTER_SKILLS(B9).skillpoints_in_skill)

Eve Excel Addin Formulas: Buy and Sell Prices in various Regions

A very useful formula is to look at what the maximum priced Buy Order and Minimum priced Sell Order is in a Regional Market, typically Jita.

In all cases, the Cell B5 is the item name we are looking up.  I.e obelisk.

The Minimum Jita Sell Price

=EVEONLINE.MARKET_ORDERS_STATS(10000002,EVEONLINE.INVENTORYSEARCH(B5,1).id).sell.min

Lets break this down.

EVEONLINE.INVENTORYSEARCH(B5,1) will show the name of the item you are looking up.

The Cell B5 has the name we are searching for.

The ",1" in the formula means that the the name and only the name is searched for.  If i had ",0" i would get all items that included the word "obelisk" in it - in this case some 33 items, hence we need to nail it down with the ",1"

using the .id method then brings up the id which is generally what we need to go further.

EVEONLINE.INVENTORYSEARCH(B5,1).id

we can then put that id in the formula to look up market orders:

EVEONLINE.MARKET_ORDERS_STATS(10000002,EVEONLINE.INVENTORYSEARCH(B5,1).id)

10000002 is the Regional Code for Jita.

and we add the .sell method to show the Sell Orders only.

and finally the .min method to show the minimum Sell Order.

The Regional Codes for other Regions:

Dodixie 10000032

Amarr 10000043

Rens 10000030

Hek 10000042

The Maximum Jita Buy Price

=EVEONLINE.MARKET_ORDERS_STATS(10000002,EVEONLINE.INVENTORYSEARCH(B5,1).id).buy.max

Which is a very similar explanation to the Sell Order except using the method .buy to bring up Buy Orders and we are looking for the maximum value,

Eve Excel Addin Formulas: Isk in wallet and in Corporation

Perhaps the most common question any Trader will have is what is the ISK balance for a given character and / or Corporation.

Using the Eve Excel Addin:

In the formulas below, Cell B9 is the character id number

Character ISK Balance

 

This is perhaps one of the most straight forward formulas.

=EVEONLINE.CHARACTER_WALLET_BALANCE(B9)

Nothing more to say.

Corporation ISK Balance

=SUM(EVEONLINE.CORPORATION_WALLETS(B9).balance)

lets break this down:

This is a little more complicated.

Firstly, you will be aware that a Corporation Wallet has a number of Divisions.

Secondly, the data is held at character level.  Hence, you don't use the Corporation ID, you use the CEO id (technically you can use any character that has access to all the wallet slots, but the CEO seems the most obvious to me).

EVEONLINE.CORPORATION_WALLETS(B9) will give a list of the Divisions with the ISK amount in each division.

The first step is to use the .balance method to call up the isk balance in each division.

EVEONLINE.CORPORATION_WALLETS(B9).balance

And then use the excel SUM function to add those isk balances up:

=SUM(EVEONLINE.CORPORATION_WALLETS(B9).balance)

Eve Excel Addin Formulas: Total Market Order Slots and Number of Market Orders Placed

Taking a look at some Excel Eve Addin formulas that are useful.

A common item on my Excel Spreadsheets shows me how many Market Orders a given character has free, which is the difference between Total Market Orders possible less Orders Placed.

In this case, my character has the maximum possible Market Orders available of 305 and currently has 258 Orders on the Market which leaves 47 free market slots.

In all cases $C$2 is the cell with the character id number.

Total Market Order Slots

The formula to show how many Market Order Slots a character has:

=5 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*4 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Retail")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*8 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Wholesale")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*16 + SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Tycoon")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*32

Lets break this down:

The formula is working out the skill level in each of the relevant Trade Skills of Trade, Retail, Wholesale and Tycoon.  Each skill level of Trade gives 5 Order slots , Retail gives 8 slots, Wholesale 16, and Tycoon 32, and of course a character starts with 5 slots anyway.

looking at the part of the formula that works out the Trade Skill slots:

SUM((EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade")*EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level)*4

the formula EVEONLINE.CHARACTER_SKILLS($C$2) would list all the skills the character has.

adding the .name method gives the skill name in text form (an important step).

EVEONLINE.CHARACTER_SKILLS($C$2).name

Therefore, introducing a logic function EVEONLINE.CHARACTER_SKILLS($C$2).name = "Trade" will give the list of FALSE with one TRUE for the Trade Skill.  In excel terms, FALSE = 0 and TRUE = 1.

EVEONLINE.CHARACTER_SKILLS($C$2).trained_skill_level will show the skill level for every skill that the character has trained.

Therefore, multiplying those two parts together will give a list of zeros except for the Trade Skill which will given 1 x Trade Skill.

And so adding that list up gives the Trade Skill achieved.

and multiplying that by 4 gives the Order Slots for the Trade Skill.

Doing the same for Retail, Wholesale and Tycoon to get the total Order Slots a character has.

Number of Market Orders Placed

=COUNTA(EVEONLINE.CHARACTER_ORDERS($C$2))

Lets break this down:

EVEONLINE.CHARACTER_ORDERS($C$2) will give a list of all orders in the form of name, price, amount left, amount original

And so to determine the total number of orders placed we put that into the COUNTA function to count the number of items in the list.

My Sales Strategy within my Regional Trading Business

My Sales Strategy within my Regional Trading Business

Regional Trading is about 80%-90% of my Eve Online business.  Other Revenue streams are growing faster but Regional Trading remains my bread and butter.

It is how i started and i would expect will easily never fall below two-thirds of my revenues.

The Eve Excel Addin and the item identification process

I redesigned how i operate each evening now that i use the Eve Excel Addin fully within my business.  This was a game changer.

I have a spreadsheet that means it takes 10 minutes to go through my trading locations to work out what needs to be bought from Jita to be sold and also what Sell Orders need to be updated.

It takes 2 minutes to buy the items from Jita and set up courier contracts - the items to buy are given by the above spreadsheet and then using the multibuy (discussed as part of the tips in taking an account from Alpha to Omega) function speeds all this up.

And then using the sell orders identified as needing updated i spend 10 minutes to adjust all the noted Sell Orders.  Longer if competition is high, shorter if lower.

And then 5 minutes to put the couriered items up for Sale when they arrive later.  And most of the time is taken logging characters in and out!

What used to take 45 minutes now takes around 25 minutes.

And then once a weak i do a deeper dive into each trading location to see what new items i can sell.  That can take a full hour as i ponder the wider issues in what i want to sell.

Overall Strategy

My overall strategy remains to Buy from Sell Orders in Jita to sell in the trading hubs in other Regions.  This has not changed since 2012!

Within Regional Trading I don't sell consumable items such as ammunition given they tend to be high volume and very competitive.  That includes ships.

I rarely sell items used as components in manufacturing because the end buyer tends to be cost conscious (and armed with spreadsheets) and able to manufacture the items themselves if market prices are too high.

I sell items that are in low supply and sell very slowly.  These items tend to have very low competition - sellers want to get cash quickly and so don't bother in these items.  If i put an item on the market and it sells in 15 days that is fine with me.

These items also tend to be price insensitive - players will buy the item almost whatever the price.  The Buyer is much more interested in getting the item to use now rather than setting off to Jita to get it cheaper.

I sell items that are typically bought to kit a ship as a player progresses or has to replace a destroyed ship / pod.

Each month i sell 10-20% of what i had to sell at the start of the month.

Some side rules

I have a rule that the minimum profits have to be 100m isk.  It is no use me buying items for 5m to sell them for 15m.  The profitability may be great but that won't move the dials.  That said, it is surprising how many items i can buy for 120m from Jita Sell Orders and sell for 220m in the other trading hubs.  At that isk level, players re-equipping don't care about spending an additional 100m isk to save time.

And in the secondary trading hubs Amarr / Dodixie / Rens / Hek buyers will not travel even one jump to buy a cheaper item.  For example, i can post an item for sale in the main Rens Trading up for 300m, and even if one jump away the same item is for sale for 250m the item in the main Trading Hub will still be the item sold.

Therefore, i mostly sell Implants, Blueprints, ship equipment and some structure modules.

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.

Eve Excel Addin and the transformation of by Eve business

The summary of the below is that the use of the Eve Excel Addin is transforming my business activities: it is now quicker to update my orders each night; it is quicker to find new items to sell; and 4 characters are about to be freed up to do something else.

How i used to operate

Each of my 6 omega accounts has one character in Jita that buys the items from Sell Orders on the Jita market to be couriered out the other two characters that sit in Trade Hubs to put these items up as Sell Orders.

I use third party couriers.

The Daily Mechanics of all that

I had got the mechanics of this down to a fine art.  Each of the two Selling characters had the items they were selling in their quickbar and each night i would run through these items and update any where they had been undercut by competition and move any that had been sold into another folder on the quickbar.

For the items sold i would check via EveTycoon if it was profitable to buy them from Sell Orders in Jita to relist and I would also cycle through other items that i had listed in other folders on the quickbar to see if they would be profitable to be sold using EveTycoon.

The Jita Character would then buy everything in that folder to be couriered.

That all took me 45 minutes to cycle through half my business and the next night i would cycle through the the other half.

And a lot of using arrow keys and clicking.

And so it went on

How i operate now

All of that is gone.

I have a number of spreadsheets which almost all use the the Excel Addin to do all that, and more.

The "Sales Orders to update" spreadsheet: by being able to list what sell orders a character has up and by being able to compare to the minimum selling price in the Region i can now instantly get a list of sell orders that have been undercut.  I can then copy that list to the quickbar and quickly cycle through them and update the prices.

The "replace and add more" spreadsheet: by being able to list what items have been sold by my character i can determine if it is profitable to buy them again from Jita Sell Orders to sell again at the sales hub.  I can also look at a list of around 2000 items to see if it is profitable to also buy from Jita Sell Orders to sell at the sales hub.  I just keep a temporary list of items on a spreadsheet that i can then use the simple mechanic of copying from spreadsheet to Market Quickbar to allow one character in Jita to do all the buying.

The "put up the sell orders" spreadsheet: by listing what i have bought recently and increasing the purchase price by 4/3 (that gives me the sales price) i can instantly see what price i need to list an item for sale that has been couriered from Jita.

The "monitoring the skill farm" spreadsheet: for all my characters involved in skill farming i can see where all my Skill Extractors and Skill Injectors are located + therefore how many of each i have  + which skill injector Sell Orders have been undercut + last few days of Skill Injector sales + Skill queues so that i can tell who needs skills points extracted.

This means two things

Firstly, i can go through my entire business in 30-45 minutes.  It is fast and accurate.

Secondly, because i am not relying on the Quickbar in the client which is shared by characters in the same account - i don't therefore need a buyer in Jita for every account.  I just need one buyer in Jita.  Therefore, this will free up 4 high skilled characters in Jita to go elsewhere.

. . . . and i suspect they will start in the Manufacturing business.

Eve Excel Add In - a run through of my sheet to calculate my wealth, Part 2

This is the second part on my series looking at the spreadsheets which use the Eve Online Excel Add-in to calculate my net wealth.

This sheet focuses on the assets i hold in hangers and is quite annoying in that i have not yet managed to work out how i can do this in one line per character.

i.e. in the prior sheet for each character it only takes one line to calculate the total Sell Orders for example.

For the assets in hangers i need to list them all out and then make sure i am not counting Blue Print Copies.

The basics of the Spreadsheet is that for each character i list out all the assets in the hangers and then value them off Jita prices.  This will also capture all Blueprint Copies which i need to eliminate.  I then list all the Blueprints and if they are a copy i add them all up and take them away from the first list.  All will be clear on the spreadsheet below.

I also do something similar for the Corporate which captures everything in Corporate Hangers.  I.e. items delivered from courier contracts.

Lets go through the first character:

Cell D4 picks up the character ID from the list i input in column D.  See the prior post in where to get the character IDs.  There are two ways.

Cells E4, F4 and G4 get the character name, system and Region:

=EVEONLINE.CHARACTER(D4),  =EVEONLINE.SOLARSYSTEM(MODE(EVEONLINE.CHARACTER_ASSETS(D4).final_location.solar_system_id)).solar_system_name,  =EVEONLINE.REGION(MODE(EVEONLINE.CHARACTER_ASSETS(D4).final_location.region_id)).name)

The real work starts in cell D9 with the formula =EVEONLINE.CHARACTER_ASSETS(D4)

This lists all the assets held in hangers for the character where D4 is the cell containing the character ID.  It will form a list.

Column E will then take each item in column D and get the quantity =D9.quantity

Column F will then get the minimum Sell Price in Jita for each item in Column D =EVEONLINE.MARKET_ORDERS_STATS(10000002,D9.type.id).sell.min

10000002 is the id for The Forge which is the Region where Jita is.

The way this formula works is to bring up all the type.id for each asset item, and then gets the enter market order book in The Forge for this item, and then at the end gets the Sell prices only and then only the minimum.

Column G then multiplies these Columns together to get the value of each asset and then this is all summed up in Cell G7.

But we must now eliminate the value of all Blueprint Copies (which are given the Blueprint Original price).

So, Column I lists all the Blueprints owned by the character =EVEONLINE.CHARACTER_BLUEPRINTS(D4)

Column J then shows how many runs are left for each Blueprint =I9.runs.  If the runs left are -1 then that is a Blueprint Original, otherwise it is a Copy.

Column K then lists the value of only the Copies which in this case instead of multiplying the columns together i just used a new formula =IFERROR(IF(J9<>-1,EVEONLINE.MARKET_ORDERS_STATS(10000002,I9.type.id).sell.min,0),0)

Cell K7 then sums all these Blueprint Copies.

And therefore the sum of all the assets owned is Cell G7 - Cell K7.

Finally, look carefully at how it is done for the Corporation Hangers in columns GB onwards.

Eve Excel Add In - a run through of my sheet to calculate my wealth

I have been using the excel Add-In since it came out to calculate my wealth.

The aim of the excel sheet is to avoid making lists of all the Sell Orders and Buy Orders i have but to try and get as much of it into a one line formula if possible.

Using the Add-In is much quicker than the old days and quite easy to use, though with a reasonably steep learning curve.

Knowledge of excel is a must.

The Spreadsheet

Below is my Excel Spreadsheet (as at the point in time i posted it) and I explain the formulas used.  It is the formulas that count and how they are put together more than anything else.

I will assume that everyone knows how to get the add-in up and running.  If not, a suitable explanation is given by Oz in a youtube video.

In the Sheet itself, every Add-In formula i use is written above the column it is used it.

Items in yellow is data you need to input yourself.

The way to update this sheet if you have it in excel is ctrl + shift + alt + F9

Going through the columns on the main part

Column B - Character ID:

Every character in Eve Online has its own ID.  There are two ways to find this out.

1) Look at your character (or Corporation) on Eve Who and in the URL is your character ID

2) use the formula <=EVEONLINE.ACTIVE_CHARACTER().id> which will list all the ID's of characters you have on the addin.

Either way, we need these character IDs to run the spreadsheet.

Column C - name:

This is an easy formula of <=EVEONLINE.CHARACTER(B9)> where B9 is the cell with a character ID in it.

This will give the name of the character.

<=EVEONLINE.CORPORATION(B14)> gives the name of the Corporation where B14 is the Corporation ID.

Column D - solar system:

Ignore for now, not important and there may be a simpler way.  I will deal with this at the bottom of the post.

Column E - region:

Ignore for now, not important and there may be a simpler way.  I will deal with this at the bottom of the post.

Column F - wallet:

For characters the formula is <=EVEONLINE.CHARACTER_WALLET_BALANCE(B9)> where B9 is the cell with a character ID in it.

For the corporation, you still have to link it to a character with access to the bank accounts (linking it to the corporation will not work).  Also for Corporations there are various parts of the wallet, so they all need to be added up.

So, this is where is gets interesting and we start to use the Excel functionality.

I could use a formula to list out all the balances in all the Corporation accounts which would be <CORPORATION_WALLETS(B9).balance>.  This would list out all the bank balances in the corporation.

But i want one number which is the sum of these hence, the formula is <=SUM(EVEONLINE.CORPORATION_WALLETS(B9).balance)> where B9 is the cell with the ID of a character with access to the Corporation bank accounts.

Note we now have the Excel function <SUM> come in which sums up the list that was produced before into one line.

Column G - Sell Orders:

This is where we really start to use the functionality of the Add-In.

The formula i use to get the sum of all the Sell Orders for a character is <=IFERROR(SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B8).volume_remain) - SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain),0)>

B8 is the cell with the character ID in it.

Lets break this down.

<=EVEONLINE.CHARACTER_ORDERS(B9)> will list all the Sell and Buy orders for a character including the price and quantity initially put on and what is left.  That is no use - we need to isolate the quantity left to sell, the price and whether it is a Buy or Sell order.

<EVEONLINE.CHARACTER_ORDERS(B9).price> will list the price that the Sell or Buy order is listed at

<EVEONLINE.CHARACTER_ORDERS(B9).volume_remain> will list all the remaining quantity to Sell or Buy.

And this is the beauty of the Add-In and Excel, the formula <=EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain> will list the value of all the Sell and Buy orders.  It will be a list that multiples the price of each order by the remaining quantity.

Therefore, i can use the Excel SUM function to get the total value of all the Sell and Orders on one line by doing <SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain)>

However, this is the combined value of the Sell and Buy Orders.

The Add-In only gives us the ability to isolate the Buy Orders and so i need to take this total value and take away the value of the Buy Orders.

the formula <EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order> gives a True or False for each Order if it is a Buy or Sell Order.

Therefore, i can use the same formula as before but Add-In this bit to give me a list of all Buy Order Values <EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain>

and as before i can use the Excel SUM function to get the total value on one line <SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain)>

So, i only now need to take one SUM away from the other to get the total Sell Orders.

And add an IFERROR function at the beginning to give the answer "0" if there are not and Buy or Sell Orders.

Column H - Buy Orders:

<=IFERROR(SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain),0)>

This is merely the SUM of the Buy Orders we used above.

The Add-In gives us the ability to isolate the Buy Orders by using the formula <EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order> which gives a True or False for each Order if it is a Buy or Sell Order.

I also can list the price of each Buy Order <EVEONLINE.CHARACTER_ORDERS(B9).price>.

and i can list the quantity that remains to be bought <EVEONLINE.CHARACTER_ORDERS(B9).volume_remain>.

and so combining these altogether i can get a list of the Buy Orders value per Order <EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain>

and as before i can use the Excel SUM function to get the total value on one line <SUM(EVEONLINE.CHARACTER_ORDERS(B9).price * EVEONLINE.CHARACTER_ORDERS(B9).is_buy_order * EVEONLINE.CHARACTER_ORDERS(B9).volume_remain)>

and then wrap it into an IFERROR function to default to 0 if there is an error.

Column I - Inventory:

This is taken from another workbook and will be in a following post.  So far i am unable to nail the value of the inventory held by each character in a one line formula.  Hence i use another workbook to do the calculations.

Column J - Provision vs Sell Orders:

this is merely me taking a 20% provision against each sell order to recognise i will need to pay Transaction Tax when the sale goes through + i may need to list it at a lower value to get it sold.

So nothing to do with the Add-In.

Column K - Contracts outstanding or in progress:

<=IFERROR(SUM(FILTER(EVEONLINE.CHARACTER_CONTRACTS(B9),(EVEONLINE.CHARACTER_CONTRACTS(B9).status="in_progress") + (EVEONLINE.CHARACTER_CONTRACTS(B9).status="outstanding")).collateral),0)>

where B9 is the character ID.

Lets break this down.

I can list all the contracts i have that are "outstanding" and "in progress" and " finished" using the formula <EVEONLINE.CHARACTER_CONTRACTS(B9)>.

But what i am interested in are only those "outstanding" or "in progress".

The Add-In allows me to show the status of each contact using ".status".  So <EVEONLINE.CHARACTER_CONTRACTS(B9).status> will list the status of each contract.

Therefore, i can use the Excel FILTER function to isolate only those that are "outstanding" or "in progress".  The FILTER function works a bit like COUNTIF.  <FILTER(list, condition)>.

I can use multiple conditions and in my case i was either "outstanding" or "in progress".  "+" is the equivalent of "OR".

Therefore, to list only these contracts that are "outstanding" or "in progress" i use the formula <FILTER(EVEONLINE.CHARACTER_CONTRACTS(B9),(EVEONLINE.CHARACTER_CONTRACTS(B9).status="in_progress") + (EVEONLINE.CHARACTER_CONTRACTS(B9).status="outstanding"))>

And then to get the contract value i use ".collateral" to make the formula <FILTER(EVEONLINE.CHARACTER_CONTRACTS(B9),(EVEONLINE.CHARACTER_CONTRACTS(B9).status="in_progress") + (EVEONLINE.CHARACTER_CONTRACTS(B9).status="outstanding")).collateral>.

And of course i want this on one line so i use the SUM function to add it all up <SUM(FILTER(EVEONLINE.CHARACTER_CONTRACTS(B9),(EVEONLINE.CHARACTER_CONTRACTS(B9).status="in_progress") + (EVEONLINE.CHARACTER_CONTRACTS(B9).status="outstanding")).collateral)>

And wrap it in an IFERROR function to return "0" if there is an error.

Column L - Items in Corporate Delivery Hangers:

This is taken from another workbook and will be in a following post

Column M - Plex:

The Add-In can't access the Plex Wallet - so i have to manually input the quanity.

But the Jita minimum Sell price is the formula <=EVEONLINE.MARKET_ORDERS_STATS(10000002,44992).sell.min>

where "10000002" is the ID of The Forge region and "44992" is the ID of Plex.

Column D and E - System Location and Region Location:

These columns list the System and Region each character is in.  There must be na easier way of doing this but for now i cant work it out.  So i have looked for the location for each character where most of their assets are and used that as the assumed location of the character.

so, for System: <=EVEONLINE.SOLARSYSTEM(MODE(EVEONLINE.CHARACTER_ASSETS(B9).final_location.solar_system_id)).solar_system_name>

where B9 is the cell with the character ID.

and for Region: <=EVEONLINE.REGION(MODE(EVEONLINE.CHARACTER_ASSETS(B9).final_location.region_id)).name>

where B9 is the cell with the character ID.