Can I run a report of one investment in one account showing running totals?

Posted by Gary on July 12, 2008, 3:33 pm

Please log in for more thread options

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
value.

Actually a cash balance isn't as critical, but if included would
probably be ACCOUNT cash balance...not cash balance due to that
particular investment.

Does anyone know how I can generate such a report in Quicken? If you
do, and you share it, I'd really appreciate it!

Posted by John Pollard on July 13, 2008, 12:42 pm

Please log in for more thread options

Gary wrote:

> 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

> value.

> Actually a cash balance isn't as critical, but if included

> would

> probably be ACCOUNT cash balance...not cash balance due to

> that

> particular investment.

> 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
trivial exercise.

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
transaction.

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
it).

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.

--

John Pollard
First initial underscore Last name at mchsi dot com
Please reply to newsgroup

Posted by Gary on July 14, 2008, 8:57 am

Please log in for more thread options

wrote:

>Gary wrote:

>> 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

>> value.

>> Actually a cash balance isn't as critical, but if included

>> would

>> probably be ACCOUNT cash balance...not cash balance due to

>> that

>> particular investment.

>> 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

>trivial exercise.

>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

>transaction.

>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

>it).

>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.

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.

Posted by John Pollard on July 14, 2008, 9:26 am

Please log in for more thread options

Gary wrote:

> On Sun, 13 Jul 2008 16:42:23 GMT, "John Pollard"

> wrote:

>> Gary wrote:

>>> 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

>>> value.

>>>

>>> Actually a cash balance isn't as critical, but if included

>>> would

>>> probably be ACCOUNT cash balance...not cash balance due to

>>> that

>>> particular investment.

>>>

>>> 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

>> trivial exercise.

>> 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

>> transaction.

>> 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

>> it).

>> 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.

> Thanks, John, for this idea.

> This way of looking at data, using

> running totals, etc., is so fundamental to my thinking about

> investments,

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
"suggestion".

> checking and savings, that

> I would probably want to

> generate a separate report for each type of holding in each

> account.

You include all desired investment accounts in the Investment
Transaction report, sub-total it by account, and export/import
that to Excel.

> The amount of work it would take is prohibitive.

Getting the running share balance in Excel would not be that
much work.

> 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.

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
running balance.

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).

> If there's a

> way I can re-design my entire portfolio to give accounts to

> look like

> this, I will do it.

--

John Pollard
First initial underscore Last name at mchsi dot com
Please reply to newsgroup

Posted by Gary on July 14, 2008, 12:18 pm

Please log in for more thread options

On Mon, 14 Jul 2008 08:26:27 -0500, "John Pollard"

>Gary wrote:

>> On Sun, 13 Jul 2008 16:42:23 GMT, "John Pollard"

>> wrote:

>>> Gary wrote:

>>>> 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

>>>> value.

>>>>

>>>> Actually a cash balance isn't as critical, but if included

>>>> would

>>>> probably be ACCOUNT cash balance...not cash balance due to

>>>> that

>>>> particular investment.

>>>>

>>>> 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

>>> trivial exercise.

>>>

>>> 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

>>> transaction.

>>>

>>> 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

>>> it).

>>>

>>> 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.

>> Thanks, John, for this idea.

>> This way of looking at data, using

>> running totals, etc., is so fundamental to my thinking about

>> investments,

>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

>"suggestion".

>> checking and savings, that

>> I would probably want to

>> generate a separate report for each type of holding in each

>> account.

>You include all desired investment accounts in the Investment

>Transaction report, sub-total it by account, and export/import

>that to Excel.

>> The amount of work it would take is prohibitive.

>Getting the running share balance in Excel would not be that

>much work.

>> 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.

>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

>running balance.

>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).

>> If there's a

>> way I can re-design my entire portfolio to give accounts to

>> look like

>> this, I will do it.

I have also submitted the suggestion.