Automating the Running of Quicken Report

Patrick

I frequently "run" (5) Quicken Reports that create data for a financial Excel Spreadsheet I have developed over the past years. This incorporates many complex macro modules that take the Quicken data, analyze it before creating an overall report.

Now I want to automate Quicken so I can effectively select one icon and all my reports will be automatically executed. Can anyone suggest a way of doing this?

Thank - Patrick Russell

pswillia1

I second this request.

Snowman

Do you have any screenshots of what you want to accomplish?  Also is the analysis done in Quicken (Macro Modules) or as I suspect Excel.

I too have a very large spreadsheet in excel to show income and expenses for my household and multiple properties.  This large spreadsheet covers columns A - N and rows 1 to 873 and includes a cash forecast for the current year (by month) and the next 4 years by year.

Late last year I undertook a project to be able to export the data from a Quicken monthly report and import it into large Excel spreadsheet to show this information monthly for a calendar year instead of manually doing it. It also showed my available cash balance at the end (or beginning) of every month. I had to make some major adjustments in the spreadsheet because the item names needed to match those in the Quicken report EXACTLY. I then have 12 monthly reports created in Quicken to export from.

Important note: The informational area in the Excel spreadsheet needs to be protected and locked. The data for this area of the spreadsheet comes from an import area elsewhere in the spreadsheet by referencing the appropriate cell in the import area.

I then run say the December 2022 report. I then export that report to Excel via a txt file. I then first open the large monthly spreadsheet in excel. Then I open the txt export file. Note windows assumes that you are looking for an excel file so you will have to change the extension option from All Excel files to All files so that the txt file will be listed. Open the file. The file columns will be compressed so you will need to highlight the columns (so that the whole column is highlighted), then right click, select column width and enter a number that will show the information that you need.

Then highlight the information that you want to copy to the large spreadsheet. On the large spreadsheet select the cell where you want the information to be copied to and right click to select "Paste Special". Then in the "Paste" section select Values. This will copy just the values and not mess with any formatting in the large spreadsheet.

Patrick

Hello! This is what I’ve been doing over the past 2-years…

I use Quicken Premier on a regular basis, downloading Quicken Report data from (5) accounts. I do this by exporting .txt data.

Then I run my Excel program (.xlsm) loaded with macros, to import the txt files and process the results. The excel part of this process is easy Just one click.

On the other hand, that multi-click process for exporting the txt data for all of my accounts “sucks”. All I ask of Quicken is to be able to record all keystrokes I make for each account and then be able to “execute” the resulting recording with (1) click. It sounds such an easy a task for their development folk and would be so very time-saving for me, and many others I am sure.

Snowman

The are a number of programs that will record keystrokes with the ability to create macros. You can even buy a programmable keyboard that will have ‘special’ function keys that you can do this macro recording. I use a Corsair keyboard that has many programmable buttons on it. This is what you need to look into. Quicken should not do this as it would create more problems than it would solve and buying a keyboard/macro program that will be infinitely more flexible.

Scooterlam

@Patrick

@Snowman references 3rd party keystroke programs as a potential solution to your automation want in Quicken.  I wanted to share what I do in Quicken's Investing tab and Lifetime Planner feature to give some idea on how I automate a similar task (that Quicken does not support).  Hope this helps and gives you another perspective. 

Like you, I also use a handful of excel macros for one-click builds of my final report.  But, on the front-end, when collecting Quicken data, I use a tool called Macrorecorder (https://www.macrorecorder.com/).   Macrorecorder automates report generation and saving procedures, avoiding repeated/tedious/timewasting tasks in Quicken.   Its fairly straightforward to use but there is somewhat of a learning curve.  Have a look.  Its free to try and frankly, I am only using the free version for a fairly extensive 1,800 line script.

For illustration, I use Macrorecorder to:

1.  Export a custom version of my investment portfolio to a tab-delimited text file for later import to Excel.


2.  Export Income, Expense and Portfolio Data for every year of my Lifetime Planner plan to a tab-delimited text file for later import to Excel.  Image.



All this can be done in Macrorecorder mostly using keystroke recording.  Only one command in the script references a screen location (does not use a keystroke). And, keep in my you have the choice between Quicken's Classic and Standard Menus.  Together they offer alot of keystroke commands.  To be sure, given that Quicken is becoming seeming more unstable (blinks, blanks, delays in screen writes et al) as versions get pushed to production,  You will likely use wait states as well, one of many additional feature of the app.   Overtime, you will likely have some script maintenance to do, but that comes easier as you gain experience in the tool - I'm on version 10 of my script, mainly as a result of ODD UI behaviors along with some of my own optimization improvements.

From the Macrorecorder output, I then pickup all of the text files and run them through a number of Excel Macros.  In my case, I have 2 clicks (one Macrorecorder, one Excel).  Total runtime is less than 3 minutes for both.




The end result is a complete Lifetime Planner dataset in Excel, including the starting portfolio, all assumptions, and of course the Income, Expense, and portfolio data for each year of the planner.   Below is a snippet from my LTP master test file.

So, what you're wanting likely can be done outside of Quicken.



Hope this helps get you started as a workaround.

Patrick

Thanks for the advice. Greatly appreciated.