export to excel - customize data

Jeanne

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

Jim_Harman

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

Jeanne

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

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.





Opening the file with a text editor.


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


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.

splasher

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.

Jeanne

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

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

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.

Jeanne

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

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.


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


Delimited is the right choice.

Excel guess the right  choice of Tab.


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.



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.


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.