export to excel - customize data

Jeanne
Jeanne Member ✭✭✭
edited December 2018 in Investing (Windows)
Before I load a lot of data into Quicken, I'm trying to figure whether I can do what I want to do. (I'd consider an upgrade from Deluxe if nec.) 

First, there is more info in the Security Detail than appears in any report. It doesn't seem like I can modify/customize any report to include industry or dividend yield (for example). Am I missing something?

Second, it seems like the closest I can get to a report with this additional info is in the PORTFOLIO display under the INVESTING tab. I could live with exporting this simple list to any spreadsheet format, but I can't. I cannot "select all" to copy & paste. I tried printing to a text file (FILE > PRINT PORTFOLIO). But while it looks like a fixed-width file, it isn't. Some columns are combined into a single column. It also exports by page rather than one line per security. (Together, these two things require more than little tweaking.) Is there really no reasonable way to export fuller info about one's securities in any spreadsheet format?

I've used Quicken manually for years but never loaded my investments. I'll continue using it, but it is looking like it is under-powered as a portfolio manager/analyzer.

Thanks for any insights/suggestions,
Jeanne


Comments

  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited December 2018
    You are on the right track with exporting an Investing > Portfolio view.

    The (non-obvious) trick is:

    --set up the view with the columns you want

    -- go to File >print portfolio

    -- select Export to and .PRN, click on Export

    --In the file name dialog, pick your location and give the file a CSV extension. This file should open directly in Excel


    QWin Premier subscription
  • Jeanne
    Jeanne Member ✭✭✭
    edited December 2018
    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

  • Unknown
    Unknown Member
    edited December 2018
    Jeanne said:

    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

    The PRN file is fixed width text

    No it isn't.
    image


    image

    Opening the file with a text editor.
    image

    Opening it in Excel (just double click because it is my default for CSV files):
    image

    Note if you make the "mistake" of selecting the "Excel" option you will be getting tab delimited fields and you will have to walk through Excel asking you about the delimitation.

    image
  • splasher
    splasher SuperUser ✭✭✭✭✭
    edited December 2018
    Jeanne said:

    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

    In my Excel 2010, on the Data menu, there is a Text to Columns command which allows you to break the information in a single cell into multiple columns.

    -splasher using Q continuously since 1996
    - Subscription Quicken - Win11 and QW2013 - Win11
    -Questions? Check out the Quicken Windows FAQ list

  • Jeanne
    Jeanne Member ✭✭✭
    edited December 2018
    My goodness.... if you happen to have this kind of software around: Print to a PDF and then convert PDF > Excel. Effortless. For some reason, the column headings are omitted, but that is small potatoes next to the effort required to make a *.PRN file usable as a spreadsheet.
  • Unknown
    Unknown Member
    edited December 2018
    BTW for YEARS I have tried to get Quicken Inc to drop the tab delimited one, and just take the code for the "Lotus/PRN" and save it as "CSV (Excel compatible)", but NO they can't do such a simple thing...

    And also it should be noted that in most places now they have "Export to Excel" in the latest version of Quicken.  This is an export directly to Excel format, but unfortunately they didn't put that option in the portfolio view print.
  • Jim_Harman
    Jim_Harman SuperUser ✭✭✭✭✭
    edited December 2018
    Jeanne said:

    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

    Yes but you don't need that if you choose the "PRN (123-compatible)" option when exporting. Despite its name, this produces a comma delimited (CSV format) file. If you just give the file the corresponding CSV extension, and if necessary set Excel as the default application for CSV files, it will open directly in Excel, no conversion required.

    If Excel is not the default application for CSVs, you force it by right-clicking on the file choosing "Open with" and picking Excel as the application to use.
    QWin Premier subscription
  • Jeanne
    Jeanne Member ✭✭✭
    edited December 2018
    Jeanne said:

    My goodness.... if you happen to have this kind of software around: Print to a PDF and then convert PDF > Excel. Effortless. For some reason, the column headings are omitted, but that is small potatoes next to the effort required to make a *.PRN file usable as a spreadsheet.

    I'll take this back. The spreadsheet isn't quite as clean as it looked. There would be some cleanup if one had to use this method.
  • Jeanne
    Jeanne Member ✭✭✭
    edited December 2018
    Jeanne said:

    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

    Thanks to both of you. I've learned a few things. First, mechanically following the print-to-file instructions, I missed the "export" option. This is pretty mortifying. But, yes, using the EXPORT button and the 1-2-3 format gets me a perfect spreadsheet. Yippee!

    This leaves me with only one addition issue, but I'll post separately.

    Exporting the tab delimited format gets me only two columns. Again, I'm glad to have learned about the text-to-columns function, but this is kinda messy because, somewhat unpredictably, some rows split out into more columns or fewer. Perhaps a template with macros would get me there... but happily it's now not nec.

    Really, you have made a big difference and I thank you.


  • Unknown
    Unknown Member
    edited December 2018
    Jeanne said:

    Not quite. The PRN file is fixed width text, not delimited. And without delimiters, Excel (at least this version) sees it as one column. However, thanks for getting me going. Re-naming it *.txt prompted Excel to identify the fixed width fields.

    So I can get the info in columns, but this still isn't a spreadsheet. And cutting and pasting the "pages" of the PRN file to get one record/row per security is trickier and messier than you might imagine.

    It turns out one can enlarge and re-orient the paper and reduce the font on the text-on-file "printer." This helped, but even with only the default 11 columns, I can't get one page (meaning one row per security).

    But thanks for trying. I will leave the investment accounts in Quicken. It will have its uses. It just won't do all of the analysis that I want to do.

    Don't use the "tab delimited format", it was a bad choice by the developer's especially since they put "Excel compatible" in the name.

    You troubles start with it just to open it because it comes out as a .txt file.
    So to open in Excel you have to go to the Open menu and allow open .txt files.
    image

    And then you have to go through the steps to tell Excel how it is formatted.
    image

    Delimited is the right choice.

    Excel guess the right  choice of Tab.
    image

    What is happen in this one below is you are suppose to tell Excel what each column type is.  Note that it is select the first one to start and guessed at General.  But notice another problem, only two columns are shown.

    image

    You have to scroll down so that Excel knows there are more columns than the two it can see on the first couple of lines.
    image

    All of this mess just because the developer would just take ".PRN" change its name to "CSV Format/Excel compatible" and make its file type .CSV.
This discussion has been closed.