Estate Report

Question

I am trying to create a "simple" estate report.

For a number of banking and brokerage accounts, I would like the report to:

List the cash and securities on the report opening date (from period). For each security in each account on the opening date, I would like the security name, the number of shares, the price per share, and the total value for each security.

I would like the report to show the transactions between the opening date and closing date. This would be primarily interest and dividends with some cash withdrawals. In addition, there are some security sales and purchases. For each security transaction, I would like report to show the security name, the number of shares, the price per share, and the total value for each security listed for each transaction.

On the closing date, most of the accounts would have a zero balance, but for completeness, I would like to show the cash and securities on the report closing date (to period). For each security in each account on the closing date, I would like the security name, the number of shares, the price per share, and the total value for each security.

It seems like I am looking for a hybrid of a banking, spending and balance report. I have tried to export these various reports in excel / csv and then merge them together, but end up with a complex mess that does not seem to allow someone to easily understand the starting point, see the changes over time, and then understand how we got to the ending position.

Does anyone have a suggestion on how I can create such a report? I am OK if I need to output a few reports and merge things together in excel, but don't want to have to enter each transaction manually.

Thanks for any assistance with this.

q_lurker · Answer

I do not use csv files to transfer reports to Excel. I prefer to copy the report (icon or Ctrl-c) and then paste the report directly into Excel at a beginning cell location. I have found that more efficient to get multiple reports into one sheet (or multiple sheets).

Jim_Harman · Answer

I don't think you will be able to merge reports with balances with transaction reports into one big report, because they will have different columns, unless you generate them separately and paste them together in Excel. It may be easier to understand if you keep them as separate reports.

For investing account balances, you could use the Investing > Portfolio Value report for your opening and closing dates. Group by Account and customize to include the columns you want.

For banking account balances, you could use the Net Worth & Balances > Account balances report for your opening and closing dates. Customize to exclude investing accounts.

For transactions, you could use the Banking > Banking transaction report. This will also include investing transactions, but just the share quantities. Set the date range, subtotal by account, and customize to include the columns you want. Note that for banking accounts the number next to the account name is not the opening or closing balance but the net change in the account value. For investing accounts it is the net cash transferred into or out of the account plus any dividends or other distributions, even if they were reinvested.

If you want all the details for investing transactions, you would have to use the Investing > Investment Transaction report for those accounts.

Will you be using this yourself as Executor, or does it need to be in a format an accountant or someone else can use? I can imagine that a third party would prefer to work with the actual bank and brokerage statements.

volvogirl · Answer

good luck.  When i took any Quicken reports or printouts to an accountant or enrolled agent, etc.  they didn’t want it in the Quicken format.  I had to redo the estate/trust checking account in an Excel spreadsheet.