Hi! This Current Portfolio is to show you my FREE Google Spreadsheet that enables you to track your Stock/Asset portfolio by Transactions.
As a demonstration, I am using my actual portfolio.
What you do is specify the Stock Name and Quote/Symbol for the individual stock in the Stock Summary sheet and then you key in the transactions in the Transactions sheet. The Stock Summary sheet will then aggregate the average cost per unit, realised gains/losses and dividends collected of the transactions in the Transactions sheet.
I will explain the various features and customization you can do with this spreadsheet.
To get this spreadsheet you can follow the instructions here
If you like the spreadsheet, find it useful, do let others know and you would know how to thank me!
The table above shows the Stock Summary sheet for my SGD holdings. You can choose to pull data in Auto Quote from different sources you can find, be it scraping Yahoo prices, Google prices, taking from IEX, Quandl API. You can also choose to enter the prices Manually. You key in ONLY the Stock Name, Last DPU here. The rest of the data will be pulled from the Transactions sheet.
Stock Summary will aggregate the number of units, average unit cost, realized gains/losses, dividend income collected based on transactions.
The table above show the Transactions sheet, that is linked to the previous Stock Summary. After you created the Stock, you can key in your buy, sell, dividend, stock split, capital reduction transactions, in chronological order. They will be aggregated back to Stock Summary
The table above show the Stock Summary for HKD holdings. I split the holdings based on currency so that the spreadsheet will not be confusing. So there is a corresponding Transactions HKD (which is not shown here).
The table above show the Stock Summary for USD holdings. I split the holdings based on currency so that the spreadsheet will not be confusing. So there is a corresponding Transactions USD (which is not shown here).
The table above shows a Portfolio Summary for the whole portfolio. This will aggregate some of the values in Stock Summary base currency, HKD and USD. You can follow what I did to pull data from other sheets. This is up to your imagination and frankly it will not always work the best for you. Observe that you can have a way to list your unlisted holdings as well.
There are 4 charts shown above. The first one shows the Unrealized Gains/Losses. The second below shows the Cumulative Realized Gains/Losses. The third one shows the current portfolio value and cash level. The last chart shows the expected dividend income. With data, you can form your own chart reports. These 4 are used as an example and by all means create your own.