Portfolio > ETFs > Broad Market

Keeping Track of Client Accounts Efficiently

Your article was successfully shared with the contacts you provided.

Every advisor has a responsibility to keep a watchful eye on client accounts. As the number of accounts increases, this task becomes more challenging. In this post, I’ll discuss a process I am implementing to accomplish this with greater efficiency. In short, I will be able to push a button and view an updated allocation for all accounts in a single report. 

Years ago it was said that, “An investment account is like a bar of soap: The more you handle it, the smaller it gets.” This may have been true during the 1980s and 90s when a simple buy-and-hold strategy was sufficient. However, the past 15 years have been much more volatile which has increased the need for active monitoring.

Here’s the challenge in a nutshell: How do you create a report of all client accounts (or those you select) so you can easily view the percentage in stocks, bonds, cash, alternatives, and other? Here’s what I’ve done to solve this problem. 

My custodian, TradePMR, has a feature which exports data from multiple accounts into an XML format. From there, the XML file can be converted to Excel. I’ve already created a separate Excel workbook for each individual account. Each workbook categorizes the securities held in the account by their respective sub categories (i.e. large value, mid-cap growth, etc.).

It also shows the total dollar amount and percentage in each sub category and includes a graph of the accounts’ broad allocation (i.e. stocks, bonds, cash, alternative, and other).

Let’s refer to the sample broad allocation graph below. Notice that it includes three columns for each broad category (current, new, and target). Column one is the current allocation and column three is the target.

As you can see that at the moment we are 10% underweight in stocks (40% versus 50%). I entered $50,000 into a stock subcategory which represents 5% of the total account value. When I did, the middle column rose by 5% to 45% and the cash percentage fell by the same percentage. It also works when a broad category is overweight by entering a negative number.

Click to enlarge

This has been a great way to keep up to date on my client’s allocation, especially since the financial markets have been in turmoil. So what’s the problem, you ask? 

If I needed an updated view of an account, I had to export an Excel sheet on the account from my custodian, then copy and paste it into the account workbook. This process takes about one minute per account and was getting labor intensive. From now on, it will be automated, thanks to my oldest son, an electrical engineer who is familiar with VBA code. Now, when I export an XML file of multiple accounts from my custodian, each individual client workbook will automatically update.

How do I get the broad allocation of all accounts in one report? I recently created another Excel workbook that is linked to each individual account workbook. This overview report contains the current broad allocation, compares it to the target, and shows the variance, for each account. Since each account workbook will now update automatically, the overview report will updated with it.

Adding the VBA code will save me a great deal of time, but more importantly, it will provide me with a highly efficient process to view the allocation of all accounts. This is one of the most exciting changes I’ve ever implemented. I’ll keep you posted. 

Until next time, thanks for reading and have a great week!