Please consider voting for this IDEA, below!
Lifetime Planner IDEA: Enable Full Export of Plan Data - Create "Portfolio" DAT File in AppData Dir.
Scooterlam
SuperUser, Windows Beta Beta
Lifetime Planner IDEA: Enable full export of LTP plan data by creating a "Portfolio.DAT" file in the AppData directory. Doing this enables user access to all necessary LTP plan data (income, expense, assumptions, and portfolio). Users can then efficiently import and format their entire LTP plan in Excel. Do this until more industrialized features are built to better access and interact with LTP plan data.
CHANGE SUMMARY:
This idea offers another IDEA to allow the user to unlock, view and interact with Lifetime Planner's dataset in a more useable and accessible Excel format. Image 1.
The essence of the change, requests that Quicken create or make available a Portfolio.DAT file to accompany the three existing and accessible DAT files containing LTP's Assumptions, Income and Expense user data. Image 2.
This missing, year-by-year, Portfolio.Dat file would provide the last remaining portion of LTP dataset needed to fully import and format the user's plan into Excel. Example of missing portfolio information is shown in image 3.
Once all 4 DAT files are accessible, the user can build their own custom formatted LTP spreadsheet using Excel macros. Image 1 shows a portion of an LTP dataset, organized and formatted worksheet using the existing 3 DAT files and my "LTP Master Test" file.
The Excel macro takes just 4 seconds to open the Assumptions, Income and Expenses DAT files, format and organize them into one workbook, containing 3 worksheets. Perhaps it would take 5 seconds when the missing Portfolio.DAT file is included and a few extra seconds to further format the workbook. Note that Excel macro expertise is not required but by not using macros it does slow down plan construction and formatting a bit.
Much more formatting can be done either manualy or through macros. The point here is that LTP plan data is made available for those users wanting to see and interact with plan details and results.
Image 1 - Full LTP Plan Data (less Portfolio) Exported to Excel via User Developed Macro
BENEFITS:
- FAST and EFFICIENT - Contrast this automated approach with a manual, year-by-year export of a plan this size (40 years) using existing Quicken's "export to Excel" functionality. Manual construction would take well over 90 minutes - I've done it several times...Imagine doing what-if scenarios with 4-5 second cycle times instead of 90 + minute cycle times....Less effort expended on doing the "low value" things and more effort expended on doing the "high value" things - better plan review, analysis and decisions.
- BETTER ACCESS TO USER DATA - Rather than viewing current and "stove-piped", year-by-year difficult to analyze plan summary tables, a user is able to build, see and interact with a "big picture" view of their plan, in Excel.
- BETTER USE OF TIME and INSIGHT - This allows quicker, more efficient, and better understanding of how changes and what-if scenarios impact the plan result. It also facilitates validation of user assumptions, changes and their follow-on effects in the plan.
- BETTER SCENARIO PLANNING - Access to this full LTP dataset would allow the user to save-off a Excel-based baseline plan along with multiple plan scenarios. Something that LTP currently does not do, but requested in past IDEA posts.
- EXTENDED ANALYSIS and CHARTING CAPABILIY via EXCEL - Use the power of Excel to chart and analyze data and see it in deeper/different ways than the current Plan Result Chart simply cannot do.
- CONSISTANT, RELIABLE and SUPPORTED LTP FORMAT and DATASET - Reduces validation time and corrections each time a run is performed, freeing up time for what is important.
- YOUR COMPETITORS ARE WAY AHEAD OF YOU (FOR YEARS) - Access to, presentation and export of plan data is and has been a staple in both professional and consumer level retirement planners.
THE CHANGE:
1. Create a "portfolio value" DAT file in AppData to capture year-by-year portfolio data, similar in structure to existing expense and income DAT files.
2. Fix issue with current DAT files that requires a user to click on the last year of the plan results chart in order to "load" a complete plan dataset into these DAT files.
3. Continue to industrialize this popular "export to Excel IDEA" along with other reporting IDEAs that allow greater access and interactivity to LTP plan data. Some of these are linked in the reference section below.
Please consider voting for this IDEA, below!
ADDITIONAL DETAILS:
Image 2 - LTP DAT Files
What is missing in the AppData directory is a similar, readable data file for portfolio (account) information. Example shown in Image 3. Portfolio information contains the year-by-year account balances, deposits, gains, tax, withdrawals and inflation information by account type found in the Portfolio Value section of the Plan Summary table.
Image 3 - Portfolio Values Missing (and needed) in Quicken APPDATA DAT Files
By making available this "portfolio value" information for the entire LTP plan, in a similar format as income and expense DAT files, a user can leverage Excel to quickly and efficiently consolidate all LTP plan information in a single view (or 4 worksheets). Using these DAT files provide allows very good consistency, reliability and speed for using Excel macros. Image 4 shows one approach I've used in processing the 3 existing DAT files using Excel macros. Click the control called QW LTP Combined View and Excel builds an LTP (partial) plan in Excel in 4 seconds.
FWIW, My current go-to option for doing this work is using macrorecorder software, Excel macros and Quicken's existing export to excel functionality to build a superior, full excel plan, but at the expense of some reliability and complexity around use of macrorecorder software.
Image 4 - My Excel Macros used to format and consolidate each DAT file to a single workbook.
Please consider voting for this IDEA, below!
REFERENCES:
Tagged:
6