Need help with simple investment income report

Unknown

I am trying to generate a simple YTD investment transactions report to export to Excel.  The standard Investment Transactions report does not facilitate analysis in Excel because, for most transactions (e.g., ReinvDiv, DivX), the details of the transactions are split over two rows.  I just need the information (including Security, Symbol, Amount) on one line so that I don't have to combine the two lines into one in Excel.
Is there a way to generate such a report in Quicken Premier 2018?

Jim_Harman

Have you tried the Banking Transaction report?

Or even better:
--click the gear at the top right of the transaction list (register) then pick Print transactions
--select the date range
--In the print dialog, choose Export to and .PRN (this will create a CSV file)
--give the exported file a .csv extension

You should be able to open this file directly in Excel.

Jim_Harman

Jim Harman said:

Have you tried the Banking Transaction report?

Or even better:
--click the gear at the top right of the transaction list (register) then pick Print transactions
--select the date range
--In the print dialog, choose Export to and .PRN (this will create a CSV file)
--give the exported file a .csv extension

You should be able to open this file directly in Excel.

The exported file will have the name of the security but not the ticker. You could make a security table and  do a VLOOKUP in Excel to add the ticker if necessary.

Unknown

Jim Harman said:

Have you tried the Banking Transaction report?

Or even better:
--click the gear at the top right of the transaction list (register) then pick Print transactions
--select the date range
--In the print dialog, choose Export to and .PRN (this will create a CSV file)
--give the exported file a .csv extension

You should be able to open this file directly in Excel.

Jim: thanks for your quick response. 

The Banking Transactions report does indeed include the Security Name in the Description column that could be used in a VLOOKUP solution.  But the security name is preceded, in some instances -primarily for ReinvDiv transactions, by the number of shares in the transaction.  For example, for 20.032 shares of Verizon acquired as a part of dividend reinvestment, the Description reads 20.032 VERIZON COMMUNICATIONS.  I was hoping to get a "clean" description that includes only the security name. 

NotACPA

Jim Harman said:

Have you tried the Banking Transaction report?

Or even better:
--click the gear at the top right of the transaction list (register) then pick Print transactions
--select the date range
--In the print dialog, choose Export to and .PRN (this will create a CSV file)
--give the exported file a .csv extension

You should be able to open this file directly in Excel.

What you're describing sounds like a parsing error on the import into Excel.

Open up the CSV file in NotePad or WordPad (NOT MS Word or Excel) and see if there is a comma prior to the security name.

q_lurker

Jim Harman said:

Have you tried the Banking Transaction report?

Or even better:
--click the gear at the top right of the transaction list (register) then pick Print transactions
--select the date range
--In the print dialog, choose Export to and .PRN (this will create a CSV file)
--give the exported file a .csv extension

You should be able to open this file directly in Excel.

What you're describing sounds like a parsing error on the import into Excel.

Not at all.  The description field for an investment account, Reinv___ transaction is formed as XX.XXX SecurityName.  Also applies to Bought and Sold transactions (and maybe a few others).  You can see it that way in the original report in Quicken.

One could use an Excel formula to locate the first period in the field, and then strip off the requisite number of text characters to get down to the security name only.  

I suspect Jim's suggestion to 'print' the transaction list to a csv file is the better option.     

Jim_Harman

The second method I suggested, using the transaction list report exported to a file, has a clean version of the security name, with the price, shares, and amount in separate columns.

Unknown

Thanks all for weighing in.  Jim: I ran the report as you suggested but I still get the prefixed numbers in the Description

Unknown

I've decided to use the VLOOKUP (using a wildcard -*- option) to remove the unneeded prefix.  Thanks again for all the input.

q_lurker

Ken said:

Thanks all for weighing in.  Jim: I ran the report as you suggested but I still get the prefixed numbers in the Description

Dare I say, No you didn't.  

Jim's suggestion in more detail was to 

• go to the account transaction list
• Ctrl-P (or choose File / Print (account)
• Choose the date range of interest and report title
• Click Print
• Choose the Export to: option as PRN File
• Click Export
• Choose the location and name for the file including the .csv file name extension
• Click Save
• Open that csv file with Excel.

That csv file will NOT have the share count pre-pended to the security name.  

That process is different than exporting the banking or investment transaction report to a file.

Unknown

Ken said:

Thanks all for weighing in.  Jim: I ran the report as you suggested but I still get the prefixed numbers in the Description

Thanks - I took more time to complete the task this time.  The ReinvDiv description now shows without the prefixed numbers.  However, each DivX transactions are showing over two rows within excel instead of one.  So, I still can't work with the data.  I'm likely screwing up something simple and I'm open to any input to correct this.

q_lurker

Ken said:

Thanks all for weighing in.  Jim: I ran the report as you suggested but I still get the prefixed numbers in the Description

Yes, the ___X transactions include a second line with the transfer account and the amount. If you don't need that detail, delete those lines. You may choose to consider those lines as a separate transaction -withdrawing or sing to the account in which case you might want to fill out those lines with added detail.


That is as close as I can get you to a one-line presentation.

Jim_Harman

Here's another option that may work for you: If you don't need all the transaction details, just the total YTD income for each security, you can customize an Investing/Portfolio view to include the Income column.

You can show just selected accounts or securities, and you can have it group by account or security. For a YTD listing, click on Options at the top right, pick Portfolio preferences, and set the starting date to 1/1/2018.

Note if your account holds cash that pays dividends in the form of a security where you hold zero shares (as TD Ameritrade does) or if you have sold all your shares in a security, you must also include the "Show closed lots" option.

You can export this to a CSV file just like the Transaction List export.