Trade Tracking Spreadsheet

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

Cheers,

Andrew.

And here is some fancy stuff you can do when you click add trade and select "Other" for direction:



As you can see with Direction = Other, If you type a number in the "# Opened" field, then that gets put in the Funding Column in the Closed Trades Worksheet. At this stage I don't do any calculations on this field, but could be good if I later want to add Closed Trade Statistics. If you leave the "# Opened" field blank then you can fill in the Commission and Financing fields, which can come in handy if you are still in a trade at the end of the financial year, but need to include these expenses into your previous financial tax year or if you just want to dump your expenses/interests for the whole year into one row . Financing column is also good if you want to add bank interest, data charges or even share cfd dividends (where you have no rights). The Commission and Financing fields are included in calculations for Tax as seen in diagram. Funding is not used and actual share Dividends with Franking credits should be done in a separate spreadsheet altogether.

Wow, very impressed!!!
Thanks for sharing this.

I'm guessing you trade forex, Andrew?

No worries Greasy.

Thought I'd add 2 Position Size Calculator examples.

Below we have us stock "ORCL" and currency pair "USDJPY"

Lets assume we do our tax in Australia and are trading with $5000AUD in a CFD broker account and wish to calculate a 2% risk long trade for securities ORCL and USDJPY. So risk amount is 0.02 x $5000AUD = $100AUD (as calculated below). Since ORCL is a us stock, the quote currency is USD and so our CER=AUDUSD and is valued around 0.75 US dollars. The quote currency for USDJPY is JPY so our CER=AUDJPY and is valued around 86 Yen. So given the below entry and stop-loss prices for the 2 securities, the quantity to buy is calculated. For ORCL the number of securities displayed (150) is the actual number to buy on broker platform. For currency trades, 1 standard lot = 100,000 units so 17,200/100,000 = 0.17 lots and this is the number we would use on most broker platforms.



Since we are trading cfd's, we can set the max position value to a much higher value.

Futures and Commodity trades are a little bit trickier because of different dollars per pip, so might need to add or subtract an extra zero here and there to get the desired result.

Cheers,

Andrew.

ajcode said:

Another Project I've been working on is a Trade Tracking Spreadsheet.

...................................

Note: I probably went a bit overboard on the Open Net Trade Statistics

Cheers,

Andrew.

Click to expand...

Fantastic job !

Thanks

Gday,

I have now added two buttons to my spreadsheet called "Run Cmd" and "Update Last Prices". As can be seen in the following image:



As you can see, above the buttons is a non editable cell labelled "Shell Cmd" and next to it is an editable cell containing the words "python.exe lpasx.py".

When you click on the "Run Cmd button", the following takes place:

First, all the symbols from the open trades you have added are written to a file called "Symbols.csv" (One symbol per line).

Next, the VBA macro/code executes a shell command containing the text in the above editable cell (if cell empty, no shell command is executed) which looks like this:

Shell("python.exe lpasx.py", 1)

So basically what happens next is python script "lpasx.py" gets executed asynchronously, which means that excel does not wait for script to finish. This is okay because we can simply wait for script to finish before continuing on with the next part of the process. Now what script "lpasx.py" actually does is reads the symbols in from file "Symbols.csv" (created above) and stores them in a list. The list of symbols is then iterated through to get their corresponding Last Prices from the asx web site (20 minute delayed data). The script then creates the file "LPrices.csv" and on each line, writes the symbol and the last price separated by a comma. You can easily check this by opening the file "LPrices.csv" with a simple text editor. Hopefully script works! For now anyway!

When you click on "Update Last Prices" button, the VBA code reads the file "LPrices.csv" line by line and tries to update all the Prices in the Last Price column corresponding to each case sensitive symbol and also updates the corresponding Last Date cell with today's date. If the case sensitive symbol does not match any of the symbols read in from file or it's corresponding last price is invalid (i.e. not numeric or non-positive), it will ignore that symbol's price and date and continue on with the next cell's symbol.

At the moment, if ASX website can't find symbol, then this is a problem for script "lpasx.py" as each troubled symbol's data is discarded hence stuffing up which price belongs to which symbol. This can be fixed by looking for the symbol in the downloaded web page as well, but if ASX change their web page html then that would be a waste of time. That seems to be the problem these days with using html web page data. Constantly updating scripts!

Attached are files TTS20190325.zip and lpasx20190325.zip which contain the following files:

TTS.xls - Clean Version of Trade Tracking Spreadsheet
TTSSample.xls - Trade Tracking Spreadsheet with sample ASX trades from Official March 2019 Stock Tipping Competition.
lpasx.py - Python script for downloading symbols from ASX website (Requires installation of any standard python).

The extracted files are to be all stored in the same directory location.

I might also have a look at some alternative websites for getting last prices. There seems to be some good recommendations in thread "Full ASX Excel Spreadsheet" like "Alpha Vantage" and "BigCharts".

Cheers,

Andrew.