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 main trading sheet.
A prior post talked about the workbook i used to find new items to consider selling.
Eve Excel Addin
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 Main Trading Sheet
As with the prior post, get yourself a cold drink, a spare 30 minutes and some patience to follow all this . . . . .
The purpose of this worksheet is to keep track of what you have for sale, keep track of the spare slots you need to fill and to identify items to Buy (from Sell Orders) in Jita to sell in Dodixie.
This is where the real work is done and the one i spent most time looking at daily.
There are five parts to this worksheet
Part 1) The list if items i have for currently sale in Dodixie
Part 2) A cheeky manual part that takes this list a makes it readable in a worksheet
Part 3) The universe of items i can sell (fed by the prior worksheet)
Part 4) A list of items derived from Part 3 to buy from Jita
Part 5) My current wealth
Part 1) The list if items i have for currently sale in Dodixie
I need this list to firstly check which items need to be adjusted if undercut but also to show how many slots i have free.
As ever, for any excel addin details of a character we need the character ID which can be found on the character screen ingame.
On the ingame screen, press the character face to bring up the character window. Right click the name to bring up the menu and select <copy>.
Paste this somewhere = <a href=showinfo:1373//2124084989>James Macdonell</a>.
In this case the 2124084989 is the character ID.
Using this we can get the items each character has for sale and related details such as volume and price.
In my case the character ID (2124084989) was in cell W4.
Column 1 = EVEONLINE.CHARACTER_ORDERS(W4).type. This will bring down all items the character has for sale.
Column 2= EVEONLINE.CHARACTER_ORDERS(W4).volume_remain. This will bring down the volume of all these items.
Column 3 = EVEONLINE.CHARACTER_ORDERS(W4).price. This will bring down the price for each item for sale.
Column 4 = column 2 x column 3.
This list can be copied and pasted into the quick bar as an easy way to go through each item to check to see if the price needs to be updated.
Part 2) A cheeky manual part that takes this list a makes it readable in a worksheet
I need to separate list of column 1 in normal text
OK, those good with spreadsheets can probably do this automatically. But i need to take column 1 above and convert into a normal list in the workbook to be read by other parts of the workbook.
Hence, if i copy the list in Column 1 above and paste it somewhere else. I can then highlight it, right click and select Data Type and then select Convert to Text to give:
Part 3) The universe of items i can sell (fed by the prior worksheet)
OK, this the where the magic happens.
The above shows:
Column B shows with an "x" any item that i have currently for sale in Dodixie and therefore should not be purchased in Jita.
Column C shows all the potential items i could buy from Jita, taken from the workbook i used to find new items to consider selling.
Column D then converts it into Excel Addin format
Column E shows the Jita Sell Price
Column F shows the Dodixie Sell Price (the 10000032 is the ID for the Dodixie region)
Column G shows the profit = Column F - Column E
Column H then shows the margin = Column G / Column F
Column I then shows any item where the margin is over 20% (or whatever i had in yellow in cell i2) and where the price is over the minimum selected (thats the 0 in yellow in cell i1) and which i don't currently have for sale (the x in Column B). Such items are shown with an x and are items i could consider buying from Jita to sell in Dodixie.
The formulas are (looking at row 4):
Column B: this is an Index Match formula that sees if the item in Column C is in the list in Part 2 above. It if is, then it gets an x which means i ignore it.
Column C: this is a manual list of items that came from the prior workbook. By the time i had finished the project it was some 177 items long.
Column D: EVEONLINE.INVENTORYSEARCH(C4,1) where C4 is the item in Column C. Technically we need to expand this in case the item as a ' at the start so becomes IFERROR(EVEONLINE.INVENTORYSEARCH(C4,1),EVEONLINE.INVENTORYSEARCH("'"&C4,1))
Column E: EVEONLINE.MARKET_ORDERS_STATS(10000002,D4.id).sell.min where D4 is the item in Column D
Column F: EVEONLINE.MARKET_ORDERS_STATS($F$2,D4.id).sell.min where $F$2 is the Dodixie Region ID in this case 10000032.
Column G: F4-E4
What this will show are all the items (shown by a x in column I) that i could buy from Jita to put up for sale in Dodixie.
Part 4) A list of items derived from Part 3 to buy from Jita
Column I is as above, where x shows i can buy the item to be sold.
What i then did in Column J is put an x beside the column I x to agree to buy it.
The placing of that X brings up the Jita Sell Price in Column k, the profit in Column L and importantly the item name from Column C. These are all simple spreadsheet formulas.
Now the idea is to select enough items to buy to fill up all spare sale slots in Dodixie and to have enough ISK left over to contract out the items to be couriered from Jita to Dodixie and then have enough ISK to pay the Broker Fees to put them up for sale in Dodixie,
You can select the whole of Column M and copy and paste them into the Quickbar for fast buying. And use the Multibuy button ingame to really speed that up in Jita, as i showed in this post.
Part 5) My current wealth
And finally, to keep track of my wealth:
Looking at row 4:
Column W is the character ID i manually put in.
Column X is the character name. =EVEONLINE.CHARACTER(W4)
Column Y is the ISK on that character. =EVEONLINE.CHARACTER_WALLET_BALANCE(W4)
Column Z is the sum of all sell orders which is the sum of the Sell Orders in Part 1 above,
Column AA is the 20% provision i take against each Sell Order to cover transaction taxes and price cuts
Column AB was for any spare inventory i had
Column AC for the rare occasion i had a courier contract on the go and 20% provided for =IFERROR(SUM(EVEONLINE.CHARACTER_CONTRACTS(W4,,"outstanding").collateral),0)*80% + IFERROR(SUM(EVEONLINE.CHARACTER_CONTRACTS(W4,,"in_progress").collateral),0)*80%
In AD was the value of all Plex i held: =EVEONLINE.MARKET_ORDERS_STATS(19000001, 44992).sell.min
And then Column AE was the total of it all.
No comments:
Post a Comment