Canadian Money Forum banner

How do you manage your global allocations?

3K views 16 replies 8 participants last post by  m3s 
#1 ·
I only started properly tracking and managing my global (across all accounts) asset allocations a year ago. Previously I managed accounts separately, but I really lost the "big picture" view doing that. This was something I was sorely missing. I'm curious how other people accomplish the global tracking?

Currently, I maintain a spreadsheet that has a list of accounts or broken-down accounts along with a code for the asset type (csh, sav, stk, bnd). For example:

Savings w/ interest ... $80,000 ... sav
iTrade: stocks ... $26,000 ... stk
iTrade: bonds ... $37,000 ... bnd
TD: RRSP isa ... $7,000 ... sav

The cells on the left can be populated from other spreadsheet cells I already have, for example just a sum of all the savings accounts. From the above list I can now generate asset allocation groupings with SUMIF. All savings can be now be seen with something like: =SUMIF(C$18:C$44,"sav",B$18:B$44) which would give $87,000 because it's a sum of all rows tagged with "sav"

All of this gives me a summary table that shows % and % in a global picture, across all accounts
 
See less See more
#2 ·
I actually get two summaries from this. First is an overall asset allocation:

stocks = all equities incl options and shorts
bonds = all bonds
cash = cash, savings accounts, and GICs (none of these can decline)
precious metals = gold, silver, bullion ETFs

Second, I get global summaries of important things I want to track, like total fixed income (sum of bnd, gic, sav) or total unproductive, non-interest earning cash (csh)
 
#3 ·
I have a Google Docs document with a bunch of sheets within it. Each account has a separate sheet. Within that there is a line for each holding. A separate sheet contains mapping which categorizes funds into Canada/US/UK/Emerging/Fixed or Assets. There is also a separate sheet for any purchases/sales, which is the only one I have to fill in. The rest of the information is populated automatically, including money- or time-weighted returns, split by category, by currency, trends, dividends, etc...
 
#4 ·
I use Quicken which lets me define not only if it is stock, bonds or cash, but if they are Cdn stocks, Int'l stocks, bonds, money market funds, cash, GICs, etc. ...across all accounts. Many people criticize Quicken and it has its warts, and they charge too much for it, but I have found it invaluable since 1995. And it give me all the reports I can think of if I want them. And it updates all my stock prices every day if I want to do that.
 
#5 ·
I've been using Quicken since before windows came out. The first version was Quicken for DOS running on an XT PC which was one of the first to have a hard drive. I still have that machine in my "PC museum" in my storage space. My data set in Quicken goes back to 1990. I agree with AltaRed. Quicken is too pricey, but its reporting capabilities are excellent and that's one of the main reasons I keep it.
 
#6 ·
The only time we were invested in some global assets was when we had a financial advisor and he got us into some global funds. After three years we dumped all of our funds and the advisor, and have never owned any global assets since. We began to be fully invested in stocks and only owned some US stocks in our rrsp\rrif. We do own 35oz of gold bought many years ago and they sit in our safety deposit box, earning Nothing. Yes, their value has risen, but we no long value our investments based on market value.

We've always tracked our investments in our NewViews Accounting program as well as with an Excel worksheet. The accounting program records all transactions and balances the cash\holdings with our broker account. In Excel we track each stock individually as well as by many sub worksheets to track dividends paid, dividend increases yearly, original holdings, shares bought by reinvestment and total shares, as well as income from our investments and the increase over previous years.
 
#7 ·
Currently, I maintain a spreadsheet that has a list of accounts or broken-down accounts along with a code for the asset type (csh, sav, stk, bnd).

The cells on the left can be populated from other spreadsheet cells I already have, for example just a sum of all the savings accounts. From the above list I can now generate asset allocation groupings with SUMIF. All savings can be now be seen with something like: =SUMIF(C$18:C$44,"sav",B$18:B$44) which would give $87,000 because it's a sum of all rows tagged with "sav"

All of this gives me a summary table that shows % and % in a global picture, across all accounts
A pivot table would let you accomplish this with less effort. Just sayin. :)

I do something similar, I have a Google spreadsheet with all my holdings and each line has a space for the type of investment it is. I use a pivot table to create an overall view and I have conditional formatting on the percentage so that when it needs to be rebalanced, it turns red so I can instantly notice that.
 
#9 ·
I use Vidm's PortfolioSlicer which uses pivot tables in excel. It's a bit of work to setup but he has excellent video tutorials on youtube and he answers questions very quickly

I also use Quicken but I find that PortfolioSlicer gives a much better visualization of relevant investment information including dividend yields and currencies impacts. On the "dashboard" it shows your global allocation vs your targets, allocation by sensitivity, sector and currency. It shows a lot more than that just on the dashboard (capital gains ytd/last, dividends ytd/last, exchance impacts, management fees) and then there is another sheet for yearly, monthly, daily, holdings, dividends, deposits, sales, fees.. Quicken can probably do most of the same but you'd have to customize it a lot and it isn't laid out as nicely to be honest. I use Quicken to manage the transactions and then I copy them over to PortfolioSlicer

Apparently now with PowerBi you can view this data from an excel workbook like PortfolioSlicer on your mobile devices. I haven't bothered with that yet.
 
#10 ·
James,

Maybe I am missing something....I could be.....why the need for the fancy pivot table?

Just a simple spreadsheet that says % tage in U.S. and international funds vs. Canadian funds should do the trick?

You could use some conditional formatting as Spudd indicated if you want to get sophisticated!
 
#12 ·
Maybe I'm not understanding the full issue? That is possible!

You have a spreadsheet that lists your holdings within various accounts.
Within spreadsheet you have market values for each holding.
Market values for each holding sum to a portfolio total, whereby X of stock or X of an ETF is a Y percentage of your portfolio total.

Some math, sure, that's where Excel can help and you can certainly use a pivot table as well - you can also use conditional formatting when your assets/holdings are out of whack based on your allocation model - but I guess I didn't see how managing global allocations was a challenge.

I guess maybe part of the reason I feel that way is because I keep buying ETFs like VTI or VXUS and I don't really get too concerned what the % is or is not. I don't sell assets to re-balance. Is my global part 15%? Is it 20%? It doesn't really matter than much because there is only so much money I can invest.

Again, maybe I'm missing the issue and I could be!
 
#15 ·
To me - Google Docs gives the most flexibility. You can assign any labels you want and the prices will be read in automatically from the web. You can get it to send emails when dividends are payed assets are out of wack by a certain percentage, assign any categories, currency conversion, tax calculation, show any plots you like - total freedom.

All you need to do manually is to make sure you put in the data when you buy and sell + allocation targets.
 
#17 ·
I use Google Drive almost exclusively.. except for work files and Portfolio Slicer (the files themselves live on Google drive though) Although everything you said can be done on Portfolio Slicer (prices are loaded automatically, you label each holding as you wish, all you really need to do is enter transactions and targets etc) The advantage to Google Drive is that it is backed up in the cloud.. web based rather than locked into an OS.. and it's free from Micro$ofts grubby hands. Maybe the CMF community could collaborate to build the ultimate Google Sheet for investment monitoring
 
This is an older thread, you may not receive a response, and could be reviving an old thread. Please consider creating a new thread.
Top