Another Project I've been working on is a Trade Tracking Spreadsheet. Probably more suited to EOD to Medium term traders. This ones a little bit different from others posted on the forum as I have designed it from an Accounting/Tax point of view, but I have added a currency exchange rate for non-local trading. I am defining the currency exchange rate (CER) as (Currency you do your tax in)/(quote currency). So if you do your tax in Australia and you are trading Google or EUR/USD, then your CER is AUD/USD as USD is the quote currency for these two securities.
Here is a diagram of what my spreadsheet looks like when first opened up:
All the fields shown in circles are editable fields. You need to type in at least one broker in the green broker section and when you click add trade, all the brokers you typed in are shown in a drop down list. Direction and Action are also drop down lists when add trade button is pressed/clicked on. Direction can be Long, Short or Other (I will discuss other in later posts). Action contains the values None (does nothing), Close (close trade), SplitOrRev (share split) or Delete (removes the whole trade by deleting the row). When Close is selected, the field "# to Close" contains the quantity of securities you want to close (i.e. can do partial trades).
In the Position Size Calculator, if you were trading the EUR/USD, then your Entry price, Stop price and Stop Size would be in USD, so your CER would be AUD/USD which is about 0.686 at the moment (15/01/2016). Everything else is in AUD (change if this is not the currency your doing your tax in). There is also a Max pos. value to keep all the Van Tharp fans happy .
The Currency Exchange Rate Calculator is the reverse, but if your broker can display the Gross profit in AUD when the Entry price and Exit price are in another currency (say USD) then you can calculate the exact CER for this trade provided Entry Price not equal to Exit Price (otherwise you have to estimate CER). The formula for CER is ABS(((Exit Price - Entry Price) x # Opened)/Gross Profit). You need this so you can get an accurate value for your Entry and Last/Exit Values. These are used for tax in my Tax Worksheet.
I have attached file TTS20160116.zip below which contains 2 files:
TTS.xls - Clean Version of Trade Tracking Spreadsheet
TTS_Sample1.xls - Trade Tracking Spreadsheet with sample trades
Have a play around with TTS_Sample1.xls to get a bit of a feel for how the spreadsheet works. For example click process trades and have a look in the Closed Trades Worksheet. Then in the Tax Worksheet, click generate tax report . I have tried this and got it working successfully on Excel 2003 and Excel 2013 64 bit. Let me know if you think its good, doesn't work on your version or would be good with a few enhancements/bug fixes
Note: I probably went a bit overboard on the Open Net Trade Statistics