Investment Tracking Spreadsheets

Introduction

Investment Tracking Spreadsheets has two workbooks. The "Basic After the Fact" write up tool designed to assist the DIY investor with income tax preparation and the "Advanced", which is a full featured stock portfolio tracking workbook. The Advanced version is designed to assist the DIY investor to manage their portfolio. While the Basic (After the Fact) has a DB_lite being a simple dashboard presenting the security positions of each account, together with continuity and trading reports, the Advanced has a full featured presentation of the securties held and introduces a number of additional trading reports. The Advanced is linked to a comprehensive watch list. The watch list includes several tabs, one of which is a list of Canadian and US Dividend Aristocrat stocks. This tool allows the DIY Investor to keep an eye on a great number of stocks, and highlights a name when it approaches a predefined buy or sell zone.

Both workbooks allow for two broker accounts. Most likely one account, but having a Canadian and US side.

Three colors are used in the workbooks to reflect if the cell is updated from data elsewhere in the workbook (gray) or governed by a lookup table and its associated drop down box (green) or an actual input cell (buff).

An internet search reveals several similar worksheets, but they appear to track only transactions pertaining to a particular security, and are limited to buys, sales and dividends. These worksheets do not track all the other entries that are reflected on a broker statement, nor are they balanced to the broker's statement. They are not based on the double entry bookkeeping system that has been with us since the Phoenicians and first documented by the Italians in 650 AD. After 1400 years of continued use, I see no need to abandon it now.

Investment Tracking Spreadsheet - Basic After the Fact Write Up

Clicking on the screen shot will open the DB_lite spreadehsst of Basic after the fact version of the investment tracking spreadsheet, and allows users to view it and all supporting spreadsheets as web pages. Hopefully, being able to view the complete workbook will make the following outline more meaningful.
The Instruction tab shows that the data entry is minimal and can be done quickly. The Reports are all automatically updated from information extracted from the transaction journals.


REPORTS
The Continuity Reports show the opening cash position in that broker's account, the total deposits to and disbursements from the account and the closing balance. The second section of the report reflects the opening security position and the buys and sells during the period and the closing position for each. The cost of the security, the market value, gain or loss as at the date of the report, and dividends received. You can add as many rows as you need, but don't add or delete columns. Note that each security is presented only once, and the days held reflect the total days from the original purchase. If the stock is bought and sold a number of times, the line will present totals for the stock, not individual trades

The Trading Reports show each individual trade separately, and shows the gain or loss on each trade.

GL tab sets out a journal entry should you wish to incorporate the data into an entity's general ledger. It confirms that the workbook is balanced, and sets out the counts pertaining to each type of transaction. This data is used to guide you as to the number of rows each report requires. This sheet is gray. The general ledger balances agree with the gains and losses on the Trading Reports and the Adjusted Cost Base of the holdings and cash position agree with the Continuity Reports.

Note that all reports are gray, with the exception of one or two drop down boxes.

DATA ENTRY
The data entry is set out on the instruction tab, and limited to transactions, Symbols (Ticker Tab), and limited structural data to be reviewed and possibly updated on the LT tab (lookup tables). The LT tab sets out ranges that formulae throughout the workbook use in the workbook's "indirect" function, and only advanced users should attempt to modify these. The instruction tab has been laid out for easy reference, and is easily followed.

Investment Tracking Spreadsheet - Advanced

A brief discussion of this workbook follows and the screenshot is a link to view this workbook as a web page. A review of the workbook and all supporting spreadsheets as web pages may make the outline more meaningful. The Instruction tab shows that the data entry is minimal and can be done quickly. The difference between the two versions of this Investment Tracking Spreadsheet is the addition of a full featured Dashboard and some reports. All reports are automatically updated from the transaction journals, and the data entered on the workbook's companion spreadsheet the "Dividend Aristocrat and Other Watch lists," as that data is imported into this workbook. Instructions on completing that workbook are set out on its own tab.

REPORTS
The Advanced Investment Tracking Workbook introduces the full features Dashboard Report which sets out the holdings in each broker account, their cost, market value, days held, dividends received, holding gains or losses and stock fundamentals. The whole report is grey. The data in respect to cost is automatically updated from the "BK_CDN" and "BK_US" transaction sheets, while the market data is imported to the worksheet from the workbook "Dividend Aristocrats and Other Watch lists". This market data and fundamentals are presented on the "TICKER" tab. It is not formatted and looks terrible, but like Buckley's cough Syrup it works! A Discussion of the Dividend Aristocrat and other Watch lists is set out on its own tab, as this workbook can be used alone, or as a companion to the Dashboard Portfolio Tracking Workbook.

The Guru report (G_Report) is similar to the Trading Report (T_Report) but records the gain and losses by how the investor was influenced. Was the position entered into on the advice of your broker, TV program or some other source. The various sources are listed on the "LT" tab, and imported with the market data on the "TICKER" tab.

DATA ENTRY
The data entry other than the imported information is the same as in the basic, and is set out on the Instruction tab of the workbook.

SAMPLE DATA
Trades made by EZl are reflected as sample data in the Dashboard Portfolio Tracking Workbook. This sample data is presented to show how the workbook is structured, and should not be considered investment advice.

DOWNLOAD INSTRUCTIONS
Click on the following links to make a copy of these spreadsheets.

Trades made by EZl are reflected as sample data in the Dashboard Portfolio Tracking Workbook. This sample data is presented to show how the workbook is structured, and should not be considered investment advice.

Useful Links