Trade Tracking Spreadsheet - Aussie Stock Forums

Results 1 to 5 of 5
  1. #1

    Red face 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


    Attached Files

  2. #2

    Smile Re: Trade Tracking Spreadsheet

    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.

  3. #3

    Default Re: Trade Tracking Spreadsheet

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

    I'm guessing you trade forex, Andrew?
    Disclaimer: I am not a licensed financial advisor or professional.
    Please do your own research before trading or investing with real money.

  4. #4

    Lightbulb Re: Trade Tracking Spreadsheet

    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.



  5. #5

    Thumbs up Re: Trade Tracking Spreadsheet

    Quote Originally Posted by ajcode View Post
    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



    Fantastic job !


Similar Threads

  1. Index tracking trades with tax free returns?
    By amateur in forum Beginner's Lounge
    Replies: 10
    Last Post: 15th-September-2009, 12:50 PM
  2. How to keep track of my trading?
    By mark07 in forum Beginner's Lounge
    Replies: 6
    Last Post: 14th-August-2009, 06:28 PM
  3. Quicken alternative to track Trades and Cap Gains?
    By Nicks in forum Software and Data
    Replies: 4
    Last Post: 28th-December-2008, 08:28 PM
  4. Tracking Institutional Trades
    By HRL in forum Trading/Investing Resources
    Replies: 9
    Last Post: 11th-December-2007, 02:52 PM
  5. Keeping track of trades
    By ROE in forum Trading/Investing Resources
    Replies: 2
    Last Post: 15th-October-2007, 01:36 PM

Tags for this Thread


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
Aussie Stock Forums