The Investment Reports Extension (invextension.mxt) is a set of four useful investment reports which are displayed in table form or downloaded in .csv form to a directory of the user's choosing. Reports are detailed below, and a basic overview of classes is provided. Reference website is: http://code.google.com/p/moneydance-investment-reports/. Email for more info is: dkfurrow@gmail.com.
Report Options:
Aggregation Options: Specifies how securities in different accounts will be summarized: choices are:
By Investment Account, Then By Tradeable Securities/Account Cash</i>: Aggregates by Investment Account, and then, within each investment account, breaks out performance data for all tradeable investments (e.g. Stocks/Bonds/Mutual Funds) and uninvested cash.
By Ticker: Aggregates by Security (regardless of which investment account). For example, if IBM is held in a taxable investment account and 401K, the aggregate will show total performance for IBM for both accounts.
By Security Type, Then By Security Sub Type: By Security Type, and then (within each Security Type), by Security Sub Type [as defined below]
Show Aggregates for Single Security: If enabled, prints composite report lines even if the composite consists of one security (e.g. if IBM is held in only one investment account, the report will include 2 lines for IBM, as a single security and as an aggregate). Uncheck to suppress printing of single-security composites.
Cost Basis Reporting Options: Determines whether security basis always defaults to average cost, or uses lot matching (on accounts where the user has enabled it). Please note that Moneydance currently only offers lot matching on long positions.
Hide Positions with Zero value: Controls whether closed positions are shown in report output.
Use Ordinary Return Calculation: Check to use Ordinary (i.e. non-time-weighted) total returns. Otherwise, use Modified-Dietz Returns (see below for detail).
Display Number Of Frozen Columns: The report output consists of a table with fixed (i.e. non-scrollable) columns on the left side. Choose how many columns are fixed.
Download Location: Sets the download location for the Transaction Activity and Security Prices downloads. Note: if report generation generates any errors, the log file will also be in the download location.
Date Range: A Date Range consists of either
a Rule Input (i.e. sets a reference date and a date rule [e.g."quarter-to-date"]) or
Direct Input: A set of fixed dates
(from/to dates in the
case of the 'From/To' Report, snapshot case in the case of the Snapshot
Report.
Note that the Date Range panel tests whether it has a valid date range,
and indicates to that effect in the panel border (red/invalid, green
valid).
Reports to Run: Selects which reports to run (note, at least one report is automatically selected, and only one report type may be run at a time).
'From-To' Reports describe investment performance over a prescribed period of time.
Snapshot Reports describe investment performance on a specific date
Export Transaction/Price Data
Transaction Activity and Securities Prices are downloaded reports which contain all investment and pricing activity, respectively.
Custom Reports : Manage the saving and removing of custom reports here.
Generate Report
Run Reports runs selected reports as outlined in (6).
Show Help File displays this file.
The text panel displays report status, and error messages if any.
Field Chooser Tab: allows the user to choose which of the available fields (i.e. columns) will be shown in report output, and the order of viewed fields. The default configuration is that all fields are displayed in standard order.
Account Chooser Tab: allows the user to choose which of the accounts will be included in reports and downloads. The default is, all accounts included.
Allows the user to designate specific income or expense categories which apply to investments. If a category (e.g. a check written to "Tuition Expense") is not designated, then the income or expense item will be treated as a transfer for the proposes of calculated investment performance.
Definitions:
Each report line item represents either a Security or a Composite. A Security represents a unique accounting unit in Moneydance. For example, if IBM is held in two different investment accounts, then each holding represents a unique Security. A composite is a grouping of securities based on common characteristics. For example, the user can choose to aggregate By Ticker, and that option will form composites of all securities with a common ticker (i.e. CurrencyType in the language of the Moneydance API). For the example just cited, the Composite will consist of both IBM holdings, presumably held in different investment accounts.
Color Coding/Hide Closed Positions:
Both From/To Report and Portfolio snapshot report use color coding to represent Composite. Light Gray represents secondary Composites, Dark Gray represents primary Composites, and Green represents All Securities. Secondary Composites combine to produce Primary Composites, and Primary Composites combine to produce the ALL Securities line. Double-clicking a security in any report allows the user to edit security properties.
"From/To" Report (FromDate, ToDate)
InvAcct: Investment Account: The investment account in which securities and cash are held.
Security: Either a designated security, or one of 3 aggregated values:
Securities-ALL represents aggregated securities
Securities/CASH-ALL represents aggregated securities plus uninvested cash
CASH represents uninvested cash
SecType: User defined Security Type as recorded in Moneydance. Please Note: Security Type and SubType are managed in Moneydance at the account level: thus it is entirely possible to characterize the same traded security differently in two accounts. For example, IBM can be listed as a "Bond", "Municipal" in one account and a "Stock", "Large-Cap" in another. This report is not designed to check for such potential discrepancies.
SecSubType: User defined Security Sub Type as recorded in Moneydance (see note above).
Ticker: Ticker symbol for security, "No Ticker" if security has no ticker.
StartPos: Position in security on the "From" Date
EndPos: Position in security on the "To" Date
StartPrice: Price in security on the "From" Date
End Price: Price in security on the "To" Date
StartValue: StartPos * StartValue
EndValue: EndPos * EndValue
buy: aggregate purchases between "From" and "To" dates(includes commission)
sell: aggregate sales between "From" and "To" dates(includes commission)
shortSell: aggregate short sales between "From" and "To" dates (includes commission)
coverShort: aggregate short cover transactions between "From" and "To" dates (includes commission)
income: aggregate income between "From" and "To" dates
expense: aggregate expense between "From" and "To" dates, excluding commission
longBasis: cost basis of securities with long positions on "To" date
shortBasis: cost basis of securities with short positions on "To" date
realizedGain: realized gain (against cost basis) of securities between "From" and "To" dates
unrealizedGain: unrealized gain (against cost basis) of securities between "From" and "To" dates
periodReturn: sum of realizedGain and unrealizedGain
TotPctReturn: Total Return between "From" and "To" date (if a position is not open as of the 'From' date, a blank is shown)
AnnPctReturn: Annualized Percent Return between "From" and "To" date(if a position is not open as of the 'From' date, a blank is shown).
TotPctReturn: Total Return between "From" and "To" date, for positions which are opened after the 'From' date.
AnnPctReturn: Annualized Percent Return between "From" and "To" date for positions which are opened after the 'From' date.
Portfolio Snapshot Report: (SnapDate)
First 5 Columns: See From/To Report above.
LastPrice: The price of the security on the Report Date.
End Pos: Position in security on the Report Date
AbsPrcChg: Absolute Price Change: difference in between LastPrice and the price on the business day prior to the Report Date.
AbsValChg: Absolute Value Change: AbsPrcChg * EndPos
PctPrcChg: Percentage Price Change: percent difference in between LastPrice and the price on the business day prior to the Report Date.
TR1Day: Total Return between report date and the previous business day (Note, for all returns, if positions are not open as of the beginning of the applicable window, a blank is returned).
TR1Wk: Total Return between report date and one week prior.
TR1Mth: Total Return between report date and one month prior.
TR3Mth: Total Return between report date and three months prior.
TRYTD: Total Return between report date and the end of the previous year.
TR1Year: Total Return between report date and one year prior.
TR3Year: Total Return between report date and 3 years prior.
TR_All: Total Return since inception of security/investment account.
AnnRet_All: Annualized Return since inception of security/investment account.
Long Basis: cost basis of securities with long positions as of the snapshot date.
Short Basis: cost basis of securities with short positions as of the snapshot date.
Income: Income associated with security/investment account since inception.
Ann. Div: Annualized Dividend--Dividend quantity from the security/composite, expressed as an annualized calculation.
Div Yield: Annualized Dividend divided by total long value.
Yield On Basis: Annualized Dividend divided by long basis.
Rlzd Gain: cumulative realized gain (against cost basis) of securities as of the snapshot date
Unlzd Gain: cumulative unrealized gain (against cost basis) of securities as of the snapshot date
TotGain: Realized + Unrealized Gain for a security/investment account since inception.
Transaction Activity Report:
Parent Account: Investment Account
Security: Security or Investment Account associated with transaction
Ticker: Security Ticker (note: if security has no ticker a unique identifier is assigned during report intialization).
DivFreqency: Estimated Dividend Frequency (e.g. "ANNUAL", "QUARTERLY", "BIANNUAL", "MONTHLY", "UNKNOWN")
TxnNum: Transaction Number (Parent Transaction) assigned by Moneydance
Date: Date of transaction
TxnType: Type of Parent
Transaction, in accordance with
the following table:
| Report Output | Moneydance Enum Name | Integer Value |
| "Bank" | TXN_TYPE_BANK | 4 |
| "Buy" | TXN_TYPE_BUY | 0 |
| "BuyTransfer" | TXN_TYPE_BUY_XFER | 2 |
| "ConverShort" | TXN_TYPE_COVER | 7 |
| "Dividend" | TXN_TYPE_DIVIDEND | 5 |
| "DivReinvest" | TXN_TYPE_DIVIDEND_REINVEST | 11 |
| "DivTransfer" | TXN_TYPE_DIVIDENDXFR | 8 |
| "MiscExpense" | TXN_TYPE_MISCEXP | 10 |
| "MiscIncome" | TXN_TYPE_MISCINC | 9 |
| "Sell" | TXN_TYPE_SELL | 1 |
| "SellTransfer" | TXN_TYPE_SELL_XFER | 3 |
| "ShortSell" | TXN_TYPE_SHORT | 6 |
Desc: Description field of transaction (Parent Transaction)
Buy: Dollar amount of security purchased
Sell: Dollar amount of security sold
Short: Dollar amount of security sold short
Cover: Dollar amount of security short-covered
Dollar amount of commission associated with buy/sell/short/cover
Income: Dollar amount of income
Expense: Dollar amount of expense
Transfer: Dollar amount of transfer (positive indicates transfer-in)
cashEffect: sum of Buy/Sell/Short/Cover/Commission/Income/Expense/Transfer
secQuantity: security quantity associated with transaction
MktPrice: Market Price of security on transaction date
Position: Total Split-Adjusted position in security after accounting for transaction.
Long Basis: Cost basis of security with long position (includes commission)
Short Basis: Cost basis of security with short position (includes commission)
Open Value: Mark-to-Market value of security (market price * position)
CumUnrealGain: Open Value compared to long/short basis:
PerUnrealGain: Unrealized gain from previous transaction to current transaction
PerRealGain: Realized gain from Sell/Short Cover transaction against long/short basis.
PerInc/Exp: Sum of transaction income/expense
PerTotalGain: period unrealized gain + period realized gain
CumTotalGain: sum of all PerTotalGain to date.
Securities Prices Report
id: Currency ID assigned by Moneydance
Name: Currency Name
Ticker: Currency Ticker or NoTicker
Date: Snapshot Date:
PricebyDate: Closing price on SnapshotDate
PriceByDate(Adjust): Split Adjusted price as referenced by the current date
Total Return:
Total Return is calculated as shown below. Please note the following
Ending Market Value (EMV) is calculated as the end position * end price, plus all income and expenses associated with the security (securities) during the report period.
Start Date is the later of (a) report start date (b) the date on which the first buy or short sale occurs. End Date is the earlier of (a) report end date and (b) the date on which a security position is closed. Therefore, changing report start and end dates has no effect on the returns calculation for a position which is closed at start or end date, respectively.
The formula for the Modified Dietz method is as follows:
RMDietz = (EMV-BMV-CF)/[BMV + SUMPRODUCT(Wi*CFi)]
where:
EMV = ending market value
BMV = beginning market value
CF = the net cash flow for the period (contributions to a portfolio are
entered as positive cash flows while withdrawals are entered as
negative cash flows).
SUMPRODUCT(Wi*CFi)] = the
sum of each cash flow, CFi, multiplied by its weight, Wi.
The weight (Wi) is the proportion of the total number of days in the
period that the cash flow CFi is in (or out) of the portfolio.
Wi can be calculated as: Wi = (CD - Di)
/ CD
where:
CD = the number of calendar days during the return period being
calculated
Di = The day in the return period on which the cash flow (CFi) occurred
For ordinary returns, the calculation is simply: ROrdinary = (EMV-BMV-CF)/[BMV + CF]
Annualized Percent Return:
APR is calculated using the net cash effect (less transfer) or each transaction associated with a security (securities), plus the start value and end value associated with the Start and End date as noted below.
Start Date is the later of (a) report start date (b) the date on which the first buy or short sale occurs. End Date is the earlier of (a) report end date and (b) the date on which a security position is closed. Therefore, changing report start and end dates has no effect on the returns calculation for a position which is closed at start or end date, respectively.
This calculation method can be replicated by the XIRR function of Microsoft Excel.
Transaction Entry:
Any expense associated with a buy/sell/short cover/cover short transaction is considered to be a commission.
All designated income and expense associated with an investment account is considered as income (or expense) associated with Cash (see above for details).
All Transfer (i.e. "BuyTransfer" and "SellTransfer") transactions in the table above are considered to be transfers of cash to non-investment accounts. If the user wants to transfer cash between two invesment accounts, then enter two transactions: a "buy" and "transfer" or "sell" and "transfer"to effect the desired result.
Dividend Frequency/ Dividend Calculations:
Dividend Frequency (which affects annualized dividend, dividend yield calculations) is estimated through a heuristic. Multiple dividends which occur within 21 days of each other (such cases as separate dividend/capital gains transactions, or dividend corrections) are ignored. The heuristic currently assigns one frequency to each security, based on the time between dividends of the last two dividend instances. If only one dividend is found, the dividend frequency is assumed to be annual.
Annualized Dividend: The report first calculates the total of the most current dividend (which might consist of multiple transactions within an assumed 21 day window, as noted previously). Next an annualized Dividend/Share value is calculated, using the position prior to the most recent set of dividend transactions (this covers the case of mutual fund dividend/reinvest transactions, where buys are recorded coincident with dividends). Finally, the Dividend/Share value is multiplied by the current position, in order to get the final value for Annualized Dividend.
Editing Security Information: Double-clicking a security row will bring up a dialogue box allowing the user to edit security properties (e.g. Security Type, Secruity Sub Type).
Returns backup: Doubleclicking a return cell will bring up a text box containing the information which backs up the return calculation, allowing the user to replicate the return calculation in a spreadsheet if desired.
The Main class registers the extension and passes Moneydance data to other classes.
The ReportControlFrame class receives the output of Main, creates a frame to hold the report controller GUI.
The ReportControlPanel class controls report output: the user selects report parameters, executes reports via the Run Reports button.
The BulkSecInfo class contains mapping of investment accounts to security accounts, security accounts to transactions, and contains AccountWrapper classes which hold transaction-level and security-level data. BulkSecInfo also produces the Securities Prices Report and Transaction Activity Report from these transaction and pricing data.
TransactionValues returns one line of data for each parent
transaction with buy, sell, short sell, cover short, commission,
transfer, and security quantity data.
TransactionValues also create synthetic cash account to record movement
of uninvested cash for each Investment Account. The initial account
balance is recorded as a purchase of cash, and new TransactionValues
objects are created for each related Security Transaction or Investment
Account transaction. For example, a purchase of a security and the
payment of the related commission creates a sale of the appropriate
amount of cash (potentially a short-sale if the account does not
contain enough cash). All synthetically created TransactionValues
object have a transaction id of x.1, where x is the transaction number
of the primary transaction which created the cash transaction. For
example if a security purchase has id 121, then the corresponding cash
sale has id 121.1.
TransactionValues includes a GainsCalc object (current concrete
implementations GainsAverageCalc and GainsLotMatchCalc), which
calculates long and short basis for a security.
All Extractor classes serve to extract value from a series of security transactions.
The TotalReport Class is an abstract class holding HashSets of SecurityReport and CompositeReport objects. Its concrete implementations are the TotalFromToReport and TotalSnapShotReport.
ComponentReport is an abstract class which is a superclass to both SecurityReport and CompositeReport.
SecurityReport Class is an abstract class representing one report line of either the From/To or Snapshort report. Therefore, its concrete implementations are SecurityFromToReport and SecuritySnapshotReport. Note that a SecurityReport can either represent a report line for a leaf-level security or the aggregation line for a CompositeReport (described below).
CompositeReport class performs aggregation of one or more leaf-level (i.e. unique security in unique investment account) security reports. It holds one placeholder SecurityReport (used to store aggregate data) and contains references to all leaf-level SecurityReports aggregated by the CompositeReport.
Aggregator is an interface for the classes which are used to aggregate leaf-level security Reports. To accomplish this, Moneydance classes are wrapped in classes which implement Aggregator. The wrapped classes are InvestmentAccountWrapper, CurrencyWrapper, SecurityAccountWrapper, Tradeable, SecurityTypeWrapper, and SecuritySubTypeWrapper. In this case Tradeable is defined by a single Boolean member false for the CurrencyType used to define uninvested cash, true for all other securities.
The DateUtils and IOUtils classes provide useful methods for date math and input/output, respectively.
The DateRange and DateRangePanel classes serve to control the DateRange object wihich governs the time frame of report output.
The GoalSeek, GoalSeekData, GoalSeekFunction, XIRR, XIRRData, and XIRRNPV classes are all used for calculation of Annualized Percent Return. More info can be found at http://gautam.satpathy.org/software/java/jxirr.
The ReportSaveControlFrame, ReportSaveControlPanel, controls the process of saving a report to a specific name.
All classes named similarly to "ReportConfig" (e.g. ReportConfig, ReportConfigChooserPanelBase, etc) are control mechanisms for a unique report configuration. The complete list of elements for a report configuration are in the ReportConfig class.
The SecurityAccountEditorForm controls the editing of security properties. Double-clicking on the report display generates this form to edit security properties.
ReportOutputTable class implements and enhanced JTable for use in report display. The display implements methods so that the columns are sized based on cell data length, the user can fix columns for ease of scrolling, closed positions can be either displayed or hidden, and the user may implement a hierarchical sort of data (3 columns or less).
The Prefs class holds report preferences.
The project supports JUnit testing. Test classes are under the test folder.
BulkSecInfoTest checks for consistency of transaction data from the test database testMD02.md and stored data in csv format. Both the average cost and lot-matching cost accounting methods are supported.
The ReportProdTest class contains 3 tests: testGetFromToReport() and testGetSnapReport() generate From/To and Snap reports, respectively, from the MD test database testMD01.md included in the resources folder. The generated reports are then compared to saved versions (in csv files) also contained in the resources folder. Finally, the testRepSnapAgainstFT generates a Snap Report and compares the columns in that report to series of generated From/To reports. This ensures that the Snap Report and FT Reports are producing the same data. The class also contains tests which check for consistency between aggregated returns and leaf-level security reports for different aggregation methods.
The CheckRepFromTo and CheckRepSnap classes, also in the test folder, are not actually JUnit tests. Instead, they output, in tabbed-text form, and exploded view of the From/To and Snap reports, respectively. This allows report output to be checked against intermediate calculations. An example of how to do that is found in the spreadsheet ReportCheckExample.xlsm&, also found in the resources directory. That spreadsheet also breaks down some sample transactions in order to show Mark-to-Market T-account entries.