Need help with simple investment income report

Unknown
Unknown Member
edited November 2018 in Investing (Windows)
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?

Comments

  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited November 2018
    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.
    QWin Premier subscription
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited August 2018

    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.
    QWin Premier subscription
  • Unknown
    Unknown Member
    edited August 2018

    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
    NotACPA SuperUser, Windows Beta Beta
    edited August 2018

    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 user since DOS version 5
    Now running Quicken Windows Subscription, Home & Business
    Retired "Certified Information Systems Auditor" & Bank Audit VP
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited August 2018

    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
    Jim_Harman SuperUser ✭✭✭✭✭
    edited November 2018
    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.
    QWin Premier subscription
  • Unknown
    Unknown Member
    edited August 2018
    Thanks all for weighing in.  Jim: I ran the report as you suggested but I still get the prefixed numbers in the Description
  • Unknown
    Unknown Member
    edited August 2018
    I've decided to use the VLOOKUP (using a wildcard -*- option) to remove the unneeded prefix.  Thanks again for all the input.
  • q_lurker
    q_lurker SuperUser ✭✭✭✭✭
    edited August 2018
    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
    Unknown Member
    edited August 2018
    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
    q_lurker SuperUser ✭✭✭✭✭
    edited August 2018
    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
    Jim_Harman SuperUser ✭✭✭✭✭
    edited November 2018
    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.
    QWin Premier subscription
This discussion has been closed.