I've been needing this over and over on many occasions in which I am
trying to understand where something went wrong.
That would be a report of the register of a single investment in a
single account (which might have other investments in it), showing all
transactions and a running total of shares balance and investment
Actually a cash balance isn't as critical, but if included would
probably be ACCOUNT cash balance...not cash balance due to that
Does anyone know how I can generate such a report in Quicken? If you
do, and you share it, I'd really appreciate it!
I know of no report in Quicken that would report running share
totals or running market value or running cash balance.
I think your only hope is to export to Excel and compute the
totals in Excel, and I don't think it will necessarily be a
To start, you can export a Quicken Investment Transaction report
to Excel, and add an Excel column with a formula to compute the
running share totals from the number-of-shares in each
transaction. [This alone may be enough to solve many problems,
and is the easiest part of the exercise.]
If the security is a mutual fund, you could use the price/share
from the Investment Transaction report to have Excel compute the
running market value.
If the security is a stock, the Quicken price/share for a
transaction is not likely to be the closing price for that day,
so you would probably need a separate source of closing quotes
to import to Excel, then an Excel procedure to get the imported
quotes assigned to the correct Excel rows for the date of the
You can export prices from Quicken to a tab delimited file which
can be input to Excel. [To export: Investing > Security Detail
View > (select security) > Update > Edit Price History > Print]
You can get a comma delimited file of all Quicken security
prices (or the prices for a given security, plus several other
options) using the free program QPH File Processor (Google for
I'm guessing someone with Excel expertise could get the tab
delimited or comma delimited files into Excel and get the prices
for each date assigned to the row that contained the transaction
for that date. Then Excel could compute the market value for
that date. [There's a newsgroup for Excel which I believe would
have the expertise, if no one here does.]
The Investment Transaction report has the cash amount of each
transaction, so its running total could also be computed in
Excel. But it would be the cash amount related only to the
single security whose transactions you exported.
First initial underscore Last name at mchsi dot com
Thanks, John, for this idea. This way of looking at data, using
running totals, etc., is so fundamental to my thinking about
investments, checking and savings, that I would probably want to
generate a separate report for each type of holding in each account.
The amount of work it would take is prohibitive. Not having the
ability to get this in Quicken is a major shortcoming of the product.
When you go to, for example, the Vanguard site, you always see
transaction reports with running totals in shares owned. Same thing
for other investment sites and for bank sites.
I seem to remember that certain kinds of accounts in Quicken show
running totals in the register, and I'll look for them. If there's a
way I can re-design my entire portfolio to give accounts to look like
this, I will do it.
I - and I believe others - have suggested to Intuit that they
modify the Investment transaction report to optionally allow the
display of running share totals. You could add your voice to
the list by going to the Intuit Support web site and making a
You include all desired investment accounts in the Investment
Transaction report, sub-total it by account, and export/import
that to Excel.
Getting the running share balance in Excel would not be that
Your original question only asked about investment accounts, I
figured you already knew that non-investment account "registers"
displayed a running balance. And that you can print a "check
register" report for non-investment accounts which shows a
Also, I mis-spoke in my previous post: you can also print a
"register" report for an investment account which shows a
running cash balance ... but there is no option to control the
contents except for a date range.
The only investment accounts that I know that provide a running
share balance are Quicken Single Mutual Fund accounts ... and I
personally suggest not using Single Mutual Fund accounts, they
are two restrictive (and you won't be able to use them if you
download and your fi doesn't allow each security to be
downloaded to a separate Quicken account).
First initial underscore Last name at mchsi dot com
No such report that I know of. But you may be able to use Portfolio
(Investing -> Portfolio or Ctrl-U) to find when your share balance went
The following method (known as binary convergence) works quite well if you
have brokerage statements that go back to the date or dates where the
Set Show:Holdings and Group by:Accounts and As of:<date of interest> to see
your share balance, cost basis, etc. on that date. You can customize this
report to add or remove columns.
Then find a date G where the balance was good, a date B where it is bad and
a date M about midway between G and B.
If the balance on M is bad the error is between G and M, so divide that
interval in half and repeat the process. If the balance on M is good divide
the interval between M and B. Keep dividing until you find the date where
the error occurred.
I forgot to mention that if you have Group by:Accounts in Portfolio you can
click the "+" to the left of the security name to see the lots of the
security that you held on the As of: date. This may provide a further clue
as to what's wrong.
BeanSmart.com is a site by and for consumers of financial services and advice. We are not affiliated with any of the banks, financial services or software manufacturers discussed here.
All logos and trade names are the property of their respective owners.
Tax and financial advice you come across on this site is freely given by your peers and professionals on their own time and out of the kindness of their hearts. We can guarantee
neither accuracy of such advice nor its applicability for your situation. Simply put, you are fully responsible for the results of using information from this site in real life situations.