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.
No comments:
Post a Comment