Investment Portfolio for Singapore stocks

If you fancy a free Investment Portfolio for Singapore stocks (it will work for other exchange stocks too), here is one that I’ve created using Google Sheet. One thing that I’ve struggled with using Google Sheet previously is getting Singapore stock data from Google Finance. Any Singapore stock data will be retrieved via XML Import instead of Google Finance.

For more information on using XML Import instead of Google Finance, check out my other post.

How to use the Investment Portfolio for Singapore stocks?

The portfolio is broken down into 3 main parts.

StockName sheet

This is where you enter exchange, stock name and the stock symbol. This will be referenced by other sheets. You should not make any changes to the column ordering.

• Exchange – only enter this if it is for SGX. For other exchanges that are supported by googlefinance, you can leave this blank.
• Name – Name of stock
• Stock symbol/Code – Note for SGX stock, you only need to stock code. For others supported by GoogleFinance, you need to enter in this format [exchange]:[stockcode]

Ledger sheet

What is a ledger? If you study accounting, this is where all the transactions are recorded. It’s the same in this tracker. When you buy/sell a stock, enter the record it here. When you receive dividend money, record it here. The ones in Red are mandatory columns.

• BuySell Date/ Dividend Ex-Date
• Type – Stock or Dividend
• Stock Name – this is generated from the data you entered from the StockName sheet.
• Account Type– this can be Cash account, CPF, Margin etc… if you don’t have any different account, you just put it under Cash.
• TX (transaction) Type – Buy, Sell, Dividend
• Trade currency
• QTY – number of shares you buy/sell or the number of shares that will be paying out dividend
• Price / Dividend per unit
• Fees – if any
• FX rate – enter 1 for your default currency (in this case SGD)
• Total Base Currency – automatically calculated based on QTY, Price/Dividend per unit, Fees and TX Type. You can also overwrite this if you have the total transaction amount. And leave QTY, Price/Dividend per unit, Fees columns empty.
• Total (SGD) – FX rate * Total Base Currency

Portfolio sheet

The portfolio works off data from the ledger. You only need to enter the Account Type and Stock Name column, and all the other columns will be calculated automatically. You can create as many portfolio as you want. You split your portfolio by currency or by exchanges.

Just note that currently, all the calculations are based on the currency of the stock. So ideally you will want to split you portfolio by currency. There are no FX conversion on the portfolio.

The ones in Red are mandatory columns.

• Account Type – This should match the account type you have in your Ledger.
• Stock Name – The list is generated of the Stock Name sheet.
• Currency – currency of the stock
• Symbol – auto-generated based on the Stock Name selected and symbol entered on Stock Name sheet
• Quantity – auto-generated based on the transactions from the ledger
• % Portfolio – auto-generated
• Cost per unit – auto-generated based on the transactions from the ledger. This will include any fees incurred
• Current Price – this is not real time price. For SGX stocks, this is imported from WSJ. Everything else is from Google Finance.
• Total Cost – auto-generated based on Quantity * Cost Per Unit
• Current Total – auto-generated based on Quantity * Current Price
• Unrealised Profit/Loss – auto-generated based on Total Cost – Current Total. This will not be generated if Quantity is 0.
• Unrealised Profit/Loss% – auto-generated based on Unrealised Profit/Loss / Total Cost. This will not be generated if Quantity is 0.
• Total Dividend collected – auto-generated based on the transactions from the ledger.
• RealisedProfit/Loss – auto-generated based on the transactions from the ledger. This will only generate if Quantity is 0.

Overview Sheet (Optional)

Lastly, you can always create your own overview sheet based off the portfolios so that you get an overview on your portfolio. I have added a simple one onto this sample.

You can get the free investment portfolio tracker for Singapore Stocks from here. The tracker is in view only mode. You should Make a copy and save it in your own google drive in order to edit it.