Each month I prepare several standard reports in Quicken, for example a Spending by Category report.

Graham106

Each month I prepare several standard reports in Quicken, for example a Spending by Category report. I customize the reports with the current date, Print to a tab de-limited (Excel compatible) disk file and Export to the current month direcory. I then copy the resulting Notepad and paste it into a copy of Excel report for the previous month. Normally, all the formatting features of the Excel report are preserved.
This month, however, the numbers in the reports appear as text instead on numbers.
I have tried to change them to numbers but without success.

q_lurker

I have had good luck copying a report to the clipboard and then switching to Excel and pasting the copy directly a chosen cell (upper left corner of report).  All the original Excel cell settings (size, format, etc.) are maintained.  All cell values are overwritten with the copy values.  Formulae within the report range will be overridden; formulae outside the report range will be maintained.  I find that much simpler than working through tab-delimited or other files.  

Chris_QPW

Graham106 said:

 I then copy the resulting Notepad and paste it into a copy of Excel report for the previous month. 

This is the step where you are losing the formatting.
You are pasting text into Excel instead of letting Excel figure out what the format of each cell should be.

You can use @q_lurker's method or you need to open that *.txt file directly in Excel and it will prompt you through selecting the tab as the separator and such.

You can also avoid this prompting in a could of ways.
First off you can now export directly into Excel format:

It will even prompt to ask if you want to open it in Excel after you have exported it.

Besides this if you go the Print route (which is required in some spots in Quicken that don't have the Excel export) you should pick .PRN instead of "Tab delimited".  And when you save the file make sure to end the file name with .CSV.  A .PRN file is actually just a .CSV file.  If you save it that way you can just open the .CSV file and Excel will know what to do with it without prompting.

Graham106

Thank you very much for your suggestions. I tried @q_lurker's method and still ended up with the numbers as text. There was no prompt. My print screen looks different to the screen shot you included. I am using Quicken 2016 and assume that it is an older version than yours. It seems that the option to Export to Excel Workbook .xlsx is not available to me. My version has a Report to Excel compatible format but that also retains the numbers as text.
I tried the Print option and selected .PRN instead of "Tab delimited". I added .CSV and saved the file. When I opened it as an Excel file, the formatting was very strange and unfortunately, the numbers were still text.

Jim_Harman

One thing you might do is in your spreadsheet, before pasting in the numbers from Quicken, select the area where you will be pasting and make sure the cells are formatted as General or Number and not as text. 

Of course Microsoft changes how you do this with each new version of Excel. In Excel 2010, you select the region to format, then go to the Home ribbon bar and pick the format from the dropdown box in the Number section.

Graham106

Thank you so much for your help Jim. I tried your suggestion but without success. Actually, when I prepare the reports each month, I paste the tab de-limited (Excel compatible) disk file directly into a copy of the previous month-s Excel file that is, of course, already formatted.
It is very strange. It seems as though something such as a setting has been changed but I cannot think what it could be.

Chris_QPW

OK you didn't state what version of Quicken you are using, and it makes a difference.
The exporting directly to Excel format is a Quicken Subscription only feature.

I just did some more testing, and I see the problem, it has nothing to do with Quicken.
Whether you copy and paste or save a file these are all "text formats" there isn't anything in them that tells Excel what format a given cell should be.  Excel is guessing.

For fields with numbers if they contain a coma for the thousands Excel sets that cell as a number.  For cells like 333.33 it sets it as General.

On the other hand I did try what @Jim_Harman suggested (In Excel from Office 365) and it left the formatting alone.  So I don't know why this one is not working for you.

Jim_Harman

Excel is usually good at telling whether the data in a cell is a number or a label (text).

When you say the numbers appear as text, how are they wrong? Are they left instead of right justified, multiple numbers in the same cell, or what?

Do you get #VALUE! errors?

q_lurker

Chris_QPW said:

...
I just did some more testing, and I see the problem, it has nothing to do with Quicken.
Whether you copy and paste or save a file these are all "text formats" there isn't anything in them that tells Excel what format a given cell should be.  Excel is guessing.
... 

(The following is offered with respect to my preferred Copy and Paste (Ctrl-C and Ctrl-V) process.)

I disagree that Excel is guessing in that if the cell already has a format assigned, that format is used.  The OP has indicated cells are pre-formatted.

FWIW, I tested this by importing an account balances report, formatting some balances to a date format, then reimporting a different set of balances.  Those cells formatted as date, remained as date presentations. 

First time through, I would say Excel is applying its normal logic.  Type 123 or 123.45 and the cell keeps it's "General" format.  Type in 123,456.78 and it gets a "number" format designation.  Type in 12/21/18 and it gets a "Date" format.

None of this explains @Graham106 's issue with the format not being maintained.

I have used the process I prefer for decades with many versions of Quicken.  There were times years ago when the whole report would come in jumbled in one line, but that s a difference story.  

Chris_QPW

Note the "guessing" I talked about is if the user hasn't pre-formatted the cells.  This would of course include the "first time" and it would include if the person is opening a file that doesn't contain such formatting like a CSV or tab delimited file.  It would of course also include if the user saves the "template" Excel spreadsheet in a format that doesn't allow this formating, like CSV.

Graham106

Let me recap. I am using Quicken 2016 and have been using the following procedure for several years to prepare monthly reports.
1. I click on Reports/My Saved Reports and Graphs and select the individual reports.
2.I customize the report using the current month date.
3. I save the report and then click Print/Export to tab-delimited (Excel compatible) disk file and click Export.
4. I save the resulting Disc File in the current month directory or folder.
5. I open the file which has the heading "Name of Report" -Notepad
6. I copy the contents of the file and paste it into a copy of the previous month's Excel file that is correctly formatted e.g. with expenses and amounts. Normally, Excel somehow recognises and formats the amounts in a number format.
7. Unfortunately, now, instead of numerical amounts, the amounts appear as text numbers i.e. they are left-justified, cannot be formatted and cannot be added.

I really appreciate all the comments and suggestions and have tried each of them so far without success.

I am at a complete loss to know what has changed from previous months and how I can fix the problem.

Jim_Harman

What version of Excel are you running? Is is Windows, not Mac?

When you do the Paste in Excel you should be doing a plain Ctrl-V Paste and not a Paste Special or running a Macro from the Toolbar.

You may need to start over with a new blank Excel file to get rid of whatever setting in your current file is forcing it to treat your data as text.

When you save the Excel file, make sure it is saved as xls or xlsx and not csv or some other format, so that the formatting info is saved.

Graham106

Thank you again for your advice, Jim.
I tried starting with a new blank Excel file that I saved as an xlsx. file as you suggested.
I also selected a different report in case the previously saved Quicken report format had somehow become corrupted.
Unfortunately the result was the same -the numbers in the new Excel report were in text format rather than numeric.

As a matte of interest, however, in following this procedure, it seems to me that I am saving unformatted data in a notepad format to an unformatted Excel file. What would make the Excel file display the numbers in a numeric format?

Jim_Harman

Unfortunately the report export in the current version of Quicken is different from yours so it is hard to replicate your process exactly.

However like @q_lurker I copied a report to the clipboard and pasted it into a Notepad file then copied the Notepad data and pasted it into Excel and everything worked fine, the numbers remained as numbers and existing cell formats in the Excel file were preserved. 

There are two aspects to this -
1 whether Excel treats the pasted data as values or as labels (text). 
2 how it formats the pasted data.

Usually Excel is good at recognizing numbers in the pasted data and stores them as values, but it sounds like your problem is that for some reason Excel is treating all the pasted data as labels, thus being unable to do numeric formatting or arithmetic on it.

Is there something about your system that has changed recently? Upgrade to Windows 10, new version of Excel? What version of Excel are you running?

You say you are using Notepad, but is it really Wordpad, Word or another editor?

Otherwise I am stumped.

 

Graham106

Thank you again Jim. I really appreciate your support.

We have been avid users of Quicken for more than 20 years. With all of our financial records and history on Quicken, we are anxious to find a solution because, based on our research there is nothing comparable on the market.

To my knowledge, nothing about our system has changed recently. We have had Windows 10 for several years and we have also been running Excel 365 for several years.

The reference to the Notepad file is only apparent when I export the Quicken file and open it in the directory or folder where I save it. The heading then appears as the name of the report -Notepad. When I click on Help at the top of the file, a Windows 10 notice opens. There is also a Format tab but this only provides the opportunity to change the font or turn Word wrap on or off.

Jim_Harman

I don't use Excel 365, but it appears you may be pasting as unformatted text somehow. See this Help info
https://support.microsoft.com/en-us/topic/paste-special-e03db6c7-8295-4529-957d-16ac8a778719

Also it is possible that the desktop and online versions of Excel 365 may work differently.

Chris_QPW

Jim_Harman said:

Also it is possible that the desktop and online versions of Excel 365 may work differently.

The "online/Mobile/free" version of Office isn't "Office 365".  Office 365 is a desktop version that is basically Office 2019, with more current updates, since it is a subscription.

I'm using Office 365 and it performs just fine (retains the formatting).  And given that @q_lurker has been using this method of copy an paste for many years I don't think this has anything to do with the Quicken version either.  I don't have both Office 365 and Quicken 2016 on one machine so I can't really test that, but it doesn't seem likely.  What's more there are two facts.  @Graham106 says that it worked in the past and Quicken 2016 hasn't had any updates in many years.  If possible I think you should try this on another machine.

Something else is going on, but I don't know what (maybe a reinstall of Office might help).

I do have one question though.  Why does it matter?
Yes, I know that with General the numbers might be formatted a bit different than with Number, but you should still be able to add them up and things like that.  It certainly should be stopping you from using it altogether.

I see this comment:

Graham106 said:

7. Unfortunately, now, instead of numerical amounts, the amounts appear as text numbers i.e. they are left-justified, cannot be formatted and cannot be added.

And are you saying that even after you paste in the data you can't format it?
And what is "cannot be added"?

Also even though I have used your flow of saving to a tab delimited file and copying from Notepad to Excel, you haven't indicated if you have tried @q_lurker's suggestion to just copy and paste directly from Quicken to Excel.

Graham106

Thank you very much for this suggestion Jim. When I followed the link, I got quite excited and thought that the solution could be to select one of the Paste Special options.

But when I copied the data in the notepad and tried Paste Special, I found that the available choices were limited to Unicode Text or Text.

Jim, it may be unrelated but I have noticed a recent change in Excel. For some reason, the decimal point in my Excel spreadsheets has been replaced with a comma.

I googled to find how to change it back and found that if I go into File > Options, then in the Excel Options dialog, click Advanced from left pane, then in the right section, the Editing options group, uncheck Use system separator, then type comma into the Decimal separator box to instead of the decimal point, and type decimal point to the Thousand separator box to replace the comma.

I tried this and the comma did change to a decimal point but it also changed other things e.g. the Caps Lock key stopped working, the keys to open and close brackets stopped working, etc so I changed it back so I could continue to use the keyboard.

Could this have something to do with the formatting issue do you think?

Graham106

Hi Chris,
Thank you very much for your help. It seems as though your message crossed with my reply to Jim Harman.

Firstly in relation to Excel 365 that I have been using for some time, please see my comments regarding a formatting change regarding the decimal point. I am now wondering if this could have something to do with the issue.

Regarding your question about why it matters. The problem is that the numbers appear in the Excel file as text. They cannot be formatted and it is not possible to perform any arithmetical functions. When I said "cannot be added" I was referring to the addition function.

Regarding your last point about @q_lurker's suggestion to just copy and paste directly from Quicken to Excel. I did try that but I could not find a way to copy a Quicken report directly from Quicken. This sounds like a possible solution, but how do I do that?

q_lurker

Graham106 said:

...

Regarding your last point about @q_lurker's suggestion to just copy and paste directly from Quicken to Excel. I did try that but I could not find a way to copy a Quicken report directly from Quicken. This sounds like a possible solution, but how do I do that?

If your "copy" standards in Quicken are set to the Windows standards (as opposed to long ago Quicken standards), then from the report window within Quicken, Ctrl-C should copy the complete report.  No 'selection' necessary.  There is also an 'export' icon same place as Chris used above (March 23 post) that has a "Copy" option.  That may not say Copy to Clipboard as the current version states.

Then switch to Excel, choose the upper left cell where the report should start, and Ctrl-V to paste it in.     

Chris_QPW

Graham106 said:

 I did try that but I could not find a way to copy a Quicken report directly from Quicken. This sounds like a possible solution, but how do I do that?

Bring up the report and select anywhere in it, then type Ctrl+C.

On the adding.  I don't see that behavior.
I purposely set a column of numbers to General and then used AutoSum on them, without any problems.

Graham106 said:

They cannot be formatted 

What is preventing you from formatting them?

Graham106

Thank you for the instructions for copying a Quicken report directly from Quicken.
I tried Ctrl-C, right clicking on the Quicken report and clicking on the Export icon that opened a small menu that included "Copy Report to Clipboard", none of which I had tried before.
Unfortunately, when I pasted each into Excel, although it did produce the complete report, the numbers were still in text format.

Graham106

Hi Chris,
The amounts in the reports appear as text. They are left-justified and the format cannot be changed- The amounts cannot be summed, subtracted, multiplied or divided.

Chris_QPW

OK "Text" isn't the default for any kind of numbers, it is either "General" or "Number".  Well it might also default to Currency if the dollar sign is there.

You haven't yet said why you can't change the format of the cells afterwards.  Is there come kind of error?

All an all I would say there is something strange with your Excel and so maybe a "repair" or reinstall is needed.

Graham106

Chris, I am beginning to think that you may be right about the problem being with the Excel file. I will look at the repair and reinstall options as you suggest. Thank you again for your help