Advice/tips on tracking large portfolios?

gawws

Does there exist a wiki, sticky-post, or guidance document on using Quicken for Windows to track investment transactions of stocks, bonds and funds across multiple custodians? I have seen advice elsewhere to use the Personal Capital application instead. I have been using Quicken for two decades for personal finance but I am unsure how the tool will perform for managing a large portfolio.

Jim_Harman

See this Support article
https://www.quicken.com/support/size-or-capacity-limitations-quicken-data-file

Quicken tends to slow down if you have more than several thousand transactions in an investing account. There are ways to work around this if it is an issue for you.

If you can be more specific about what is large about your portfolio - number of holdings, number of accounts, frequency of trading, etc. we can give more specific advice.

Also you should check with your financial institutions to see if they support downloading to Quicken. If not, you will need to enter the transactions manually, which could get tedious if there a lot of them per month.

gawws

Thanks for the response. The largest is roughly 300 equities and index funds and 30 bond issues in five account buckets/types (i.e., taxable, IRA, Roth, trust, etc.) spread over five custodians. Infrequent trading but plenty of dividend activity.

q_lurker

Do each of the 'custodians' support downloading into Quicken?  Those that do should be pretty easy to manage.  Those that don't may still be very manageable if it is just manual entry of quarterly dividends and semi-annual interest payments received.  The initial setup may have some challenges depending on the depth of accuracy you want.  By that I mean do you want the basis of each lot that you might have had reinvested or can you live with an initial composite representation of your holding?  

The other side of the picture is -- What info do you want to get out of Quicken from this process?  If setup correctly, you should be able to get an overall investment performance number (Average Annual Return aka internal rate of return) on individual accounts or on the entire portfolio.  Some depth of asset allocation is possible, though in my opinion there are some weaknesses there.

FWIW, I am working with perhaps 150-200 equities in 10 or so accounts with three financial institutions.  I find Quicken helpful for bringing together the bigger picture.    

Jim_Harman

To be clear, when you say "spread over five custodians" is that 5 financial institutions (FIs)?

Re: terminology, an account in Quicken corresponds to an actual account at a FI. Quicken supports taxable investing accounts, IRAs, Roths, 401(k)s, etc. and you can have multiple accounts of each type and multiple FIs.  

If you are the Trustee for trusts that have their own tax ID number, they generally require special accounting for the cash and asset portions of the trust, and each trust should have its own Quicken data file. It is possible to do trust accounting in Quicken, but it is messy. You might want to consider special trust accounting software for that purpose.

Are these accounts for multiple people? If so, you should set up a different Quicken data file for each taxable entity, i.e. married couple or individual.

With that many securities, you will definitely want to be able to download transactions.

 

gawws

I have already successfully downloaded from Vanguard and TDA and I suspect the others support as well. As to goals - yes on performance IRR plus income/expense tracking and aggregation. Asset allocation is important, but I don't necessarily need the depth you refer to (assuming that means sector breakouts and such). I don't need pretty reports - I am handy with Excel.

Your 200 holdings provides good feedback, but...

(1) Is Quicken robust enough given it is a desktop, proprietary flat-file database?
(2) On a related note, does the organization truly support the functionality? Outside of seeing you post some configuration settings that have worked for you, are there any best practice guides? And restoring a backup cannot be the one-size-fits-all fix from tech support.
(3) Assuming positive answers to #1 and #2, is it worth the effort? Is a tool like [removed - no soliciting] better suited?

gawws

Yes "custodians" = "financial institutions", e.g. Vanguard, Fidelity, etc. I am not worried about trust accounting - just investment management.

I do have another client that is a private foundation but that's another thread.

Jim_Harman

I think "robust enough" is something you will have to decide for yourself. I have been using Quicken's investing features for over 20 years and I have never permanently lost any data. On a few occasions I have had to restore a backup file, causing minimal disruption.

In terms of best practices, I have the backup settings set to automatically save a copy every time I exit and also have the manual backup reminder set to remind me each time I exit the program. I send that backup to a USB drive. The USB backup has saved me a couple of times when I had hard disk failures.

It is also important to store your working data file(s) on a local hard drive, not on Dropbox, a network drive, or a cloud storage system.

Chris_QPW

I will note a few things about performance.  Quicken loads accounts separately and caches them in memory once you open them.  It also seems to do some calculations every time you enter a transaction having to do with securities/security lots.  These facts mean that what is most important is how many transactions/securities/security lots there is in any given account (only active ones).

This is why you will see on here many statements that Quicken isn't designed for "active traders".
But in reality this goes into more than just purely how many transactions there are.  I would bet that you can have tons of "cash" transactions and not run into performance problems, but the moment a security lot is involved I think the performance will "edge down".

This has ramifications for "buy and hold" investors too.  If there isn't any selling in the account they will build up very long security lot lists.

So in fact having your securities in different financial institutions or even different accounts should help the performance because Quicken doesn't load in accounts that you are actively working with.

Note that when the number of transactions/security lots gets large and performance goes down the recommended procedure is to move closed lots to another account.  Clearly if you have never sold anything you have no closed lots, so this technic won't work in that case.

gawws

Thanks for the input. I typically keep the data file on a server so I can access from different workstations. I have never had any problems but I am aware Quicken support doesn't like that. I will plan to store locally and just replicate in order to eliminate the potential issues.

Have you seen any published guidelines on how to setup files or accounts? I have had access to the Intuit and third-party books over the years and there isnt much guidance.

Chris_QPW

On using a server.  If you run Quicken on the server, and then remote desktop (or other such software) to it then you are still using a "local drive".

q_lurker

gawws said:

...

I do have another client that is a private foundation but that's another thread.

Time out here!  If you are doing accounting, bookkeeping, financial management, or something like that for other parties in a professional ('client') manner, I suggest you need to look somewhere other than Quicken.  In my opinion, Quicken is not professional grade.  Too easy to manipulate with no form of an audit trail.  Great for personal work; not for professional.      

Jim_Harman

On accounts and files:

One file per tax entity

One account per real life account.

Or maybe by "accounts" you mean it in the standard accounting terminology - types of income and expenses. Quicken calls these Categories. These will vary a lot from one family to another and one stage of life to another, and may take some experimenting. You may do well to start with Quicken's built in Categories, but if you want to do some customizing, my guidelines are 
1. Things I need to track for tax purposes
2. Things that are meaningful but will not require much splitting of receipts or credit card charges. For example I categorize everything we buy at the supermarket as Groceries. 
3. Not too granular. In general I choose categories that will have at least $1000 in spending per year.

There are 17 Income categories that I have used significantly over the years and 66 expense categories.

gawws

@q_lurker - certainly agree on accounting. I wonder if Quicken is suitable for investment tracking as well. Do you have a recommendation for another tool?

gawws

@Jim_Harman - referring to financial institution accounts. Quicken is great for personal finance. I just wonder how many customers use it at the level of investment management we are discussing. It seems there would be some documentation from the vendor for best practices in that area.

Jim_Harman

I would agree with @q_lurker. Quicken is designed, marketed, and supported as personal finance software.

It works great for me, but if you are a professional money manager, you need software that is designed for that application. I suggest you Google "financial advisor software"

Jim_Harman

Not to negate my earlier comments, but one option to consider to simplify tracking when you have a large number of holdings in a taxable account is to not have dividends reinvested. If an account has 100 securities and each pays dividends quarterly, that creates 400 small tax lots per year even if you do not do any trading.

Consult your tax advisor to see if that option is right for you. 

gawws

Thanks. I am seeing exactly that - lots of extraneous transactions. Again, I would think the vendor would have a best practices document with ideas like you have put forth.

Also, I am not a professional money manager, but I have consulted a few and I have not been impressed with their applications. eMoney seems to be a common one, and I think Personal Capital has better functionality.

BTW, saw this on the blog from CEO: https://www.quicken.com/blog/investments-planning-tips/how-eric-dunn-uses-quicken

Jim_Harman

Thanks for posting the link to Eric Dunn's post. I had not seen that before and it all makes sense to me, except where he says

"I have Quicken set to automatically download investment transactions without the "compare to register" step, because the majority of the investment transactions are dividends which originate with the broker."

It sounds like he means that he has Quicken set to automatically add investing transactions to his transaction list and does not check each downloaded transaction before accepting it. The common wisdom here is that this is a bad idea, because for example mismatched securities may go undetected.

Speaking of investment settings in Quicken to use or avoid, here are some of my picks. These are all in Edit > Preferences

-- In Investment transactions,

• check Show hidden transactions. This makes any Placeholders visible. Pople who are interested in performance and cost basis tracking should avoid Placeholders like the plague.
• If you just care about tracking account balances and not the individual transactions, consider enabling Simple investing. This is a new feature, and may still have some kinks.

-- In Downloaded Transactions,

• un-check Automatically add to investment transaction lists as discussed above
• Enable Compare account portfolio after download. This will have Quicken compare its share balances to those downloaded by the FI at the end of each download. If there are discrepancies, you should understand and correct them if necessary rather than letting Quicken force the balances to be equal by inserting Placeholders.

For performance tracking, use the Investment Performance report and/or the Avg. Annual Return (%) columns in the Portfolio views. These provide accurate IRR calculations if your underlying data is correct. There has been extensive discussion in this forum of the strengths and weaknesses of these and Quicken's other performance measures.

[Adding...]
Pay special attention to entering in-kind transfers between accounts, mutual fund class conversions, splits, mergers, and acquisitions correctly. Downloaded transactions often cause Quicken to lose your cost basis history when these actions occur. 

I certainly invite others to share their ideas, pitfalls, and best practices.

q_lurker

gawws said:

Thanks. I am seeing exactly that - lots of extraneous transactions. Again, I would think the vendor would have a best practices document with ideas like you have put forth.

Also, I am not a professional money manager, but I have consulted a few and I have not been impressed with their applications. eMoney seems to be a common one, and I think Personal Capital has better functionality.

BTW, saw this on the blog from CEO: https://www.quicken.com/blog/investments-planning-tips/how-eric-dunn-uses-quicken

Extraneous transactions -- something in the eye of the beholder.  Personally, I want all those dividend transactions presented in a correct timely fashion for two reasons.  1) to get correct performance information on any desired time frame, and 2) to get proper tax related information.  That tax information can be to check the 1099 information at year end, and possibly to go after a more exact tax penalty calculation (rarely needed, but possible).

Also, I am not a professional money manager, 

But you used the phrase "I do have another client ...".  That certainly suggested to me some form a professional relationship for which you wanted to use Quicken for that client.  That is what I was saying I would not do in a professional role.  Now there are certainly numerous users who act as treasurers of HOAs or scout packs, etc. and use Quicken helpfully and effectively.  My opinion -- those users need to tread carefully.

Again, I would think the vendor would have a best practices document with ideas like you have put forth.

Maybe my view is skewed by my years of experience with the program, but I don't hold that expectation.  I tend to see Quicken as trying to put as many tools as possible in the hands of their users, and then letting them figure out how best to use them.  The breadth of needs and desires within the user base is so wide that, to me, there is no one (or two) "best practice".  The Eric Dunn use blog you found speaks to that, I think.  How he handles Muni bonds vs equities vs private holdings are each individualized.  While he chooses not to manage each individual bond, I choose to include each.  Neither choice is right or wrong.  One is not best and the other less.  My $0.02.   

Chris_QPW

I agree with @q_lurker there isn't any such thing as a "best practice" for Quicken.  There are things that long time users have learned to avoid because they have caused problems, but everyone uses Quicken a bit differently.  It would be like asking someone what is the best practices for using Excel.

And on that note I'm probably one of the few long time users that uses automatic transaction entry mode.  Note the default is for this to be on for non investment accounts and off for investment accounts.

I tried to use it for a long time in investment accounts, but there are too many bugs in it.  For instance if you put in a transaction and it is just slightly off of what downloads, like the date being a day off, if you have automatic transaction entry mode on there will be a blue dot on the account flagging the new transactions in the register, but when you go there nothing will show up.  The reason is that it puts a "Near Match" in the Downloaded Transactions tab so that you can approve it or not, but that tab is hidden, so you can't see it until you turn off automatic transaction entry mode.

What's more Eric Dunn's blog message shows me that even he doesn't know how the program exactly works.

When an investment account is in automatic transaction entry mode the comparing to number of shares downloaded is disabled no matter what the settings in the preferences are set to.  This is actually another bug that it doesn't honor the preference, but it results in what most people using automatic transaction entry mode want so this one that few (I think only I) would report.

There was a time that when you used automatic transaction entry mode, and the comparison flagged a difference it would put in a placeholder without asking (to force the number of shares to be the same in Quicken).  This was a BIG problem so after a lot of complaints they disabled the comparison (you have to run it manually, and then you can approve or not approval placeholders if needed).  There are many times where the comparison fails.  Vanguard send the buys at one point in the day and not update the balances till much later in the day for example.

Later they put in the preference so that one could control running the comparison per investment account, but they never changed the automatic transactions entry mode flow for this.

I'm in the process of consolidating accounts and as such might have transactions that trigger these bugs, so I have automatic transaction entry mode off, but once I get back to basically just accepting reinvestment dividends I might turn it back on.  With it on my normal process is review the downloaded transactions to make sure they are correct, run the comparison to make sure the number of shares is correct, and then run reconcile to reconcile the cash amounts.