Document Sample
GoogleCode Powered By Docstoc
					                               Overview: Investment Reports

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: Email for more info is

                                                Page 1 of 12

Last Saved: October 26, 2012                                                                    Version 2.0
Investment Reports Explanation

1) Report Dates: Set effective dates for Snapshot Reports and “From/To” Performance reports, as
   detailed below.

2) Reports to Run: Selects which reports to run.

3) Report Aggregation Options: Selects how individual securities will be aggregated for report output.
   Regardless of selection, the last report line always shows performance data for the total investment
   portfolio. Choices are:

     a) By Investment Account, Then By Tradeable Securities/Account Cash: 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.

     b) 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.

     c) By Security Type, then Security Sub Type: Aggregates by Security Type (e.g. Stocks, Bonds,
        Mutual Funds) then by Security Sub Type (e.g. growth, value) as defined by Moneydance.

4) Cost Basis 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.

5) Show Aggregates for Composite Reports of One Security: If enabled, prints composite report lines
   even if the composite consists of one security (e.g. if IBM is held in only one 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.

6) Run Reports: Executes selected reports.

7) Reset to default—Clears all report fields, sets Aggregation Type to “By Investment Account, Then By
   Tradeable Securities/Account Cash, sets Cost Basis to “Average Cost”, unchecks “Show Aggregates
   for Composite Reports of One Security”, reverts to default directory for Download Location.

                                                 Page 2 of 12

Last Saved: October 26, 2012                                                                       Version 2.0
Investment Reports Explanation


     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 one or more 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.

     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.

1) “From/To” Report (FromDate, ToDate)
   a) InvAcct: Investment Account: The investment account in which securities and cash are held.
   a) Security: Either a designated security, or one of 3 aggregated value:
       i) Securities-ALL represents aggregated securities
       ii) Securities/CASH-ALL represents aggregated securities plus uninvested cash
       iii) CASH represents uninvested cash
   b) 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.
   c) SecSubType: User defined Security Sub Type as recorded in Moneydance (see note above).
   d) Ticker: Ticker symbol for security “No Ticker” if security has no ticker.
   e) StartPos: Position in security on the “From” Date
   f) EndPos: Position in security on the “To” Date
   g) StartPrice: Price in security on the “From” Date
   h) End Price: Price in security on the “To” Date
   i) StartValue: StartPos * StartValue
   j) EndValue: EndPos * EndValue
   k) buy: aggregate purchases between “From” and “To” dates (includes commission)
   l) sell: aggregate sales between “From” and “To” dates (includes commission)

                                                Page 3 of 12

Last Saved: October 26, 2012                                                                    Version 2.0
Investment Reports Explanation

     m) shortSell: aggregate short sales between “From” and “To” dates (includes commission)
     n) coverShort: aggregate short cover transactions between “From” and “To” dates (includes
     o) income: aggregate income between “From” and “To” dates
     p) coverShort: aggregate expense between “From” and “To” dates, excluding commission
     q) longBasis: average basis of securities with long positions on “To” date
     r) shortBasis: average basis of securities with long positions on “To” date
     s) realizedGain: realized gain (against average cost) of securities between “From” and “To” dates
     t) unrealizedGain: unrealized gain (against average cost) of securities between “From” and “To”
     u) periodReturn: sum of realizedGain and unrealizedGain
     v) percentReturn: Total Return between “From” and “To” date
     w) annualPercentReturn: Annualized Percent Return between “From” and “To” date.

2) Portfolio Snapshot Report: (SnapDate)
   a) First 5 Columns—See From/To Report above.
   b) LastPrice: The price of the security on the Report Date.
   c) EndPos: Position in security on the Report Date
   d) AbsPrcChg: Absolute Price Change—difference in between LastPrice and the price on the
       business day prior to the Report Date.
   e) AbsValChg: Absolute Value Change—AbsPrcChg * EndPos
   f) PctPrcChg: Percentage Price Change—percent difference in between LastPrice and the price on
       the business day prior to the Report Date.
   g) TR1Day: Total Return between report date and the previous business day.
   h) TR1Wk: Total Return between report date and one week prior.
   i) TR1Mth: Total Return between report date and one month prior.
   j) TR3Mth: Total Return between report date and three months prior.
   k) TRYTD: Total Return between report date and the end of the previous year.
   l) TR1Year: Total Return between report date and one year prior.
   m) TR3Year: Total Return between report date and 3 years prior.
   n) TR_All: Total Return since inception of security/investment account.
   o) AnnRet_All: Annualized Return since inception of security/investment account.
   p) AvgCost:Average Cost Basis of security/aggregated securities in investment account
   q) Income: Income associated with security/investment account since inception.
   r) TotGain: Realized + Unrealized Gain for a security/investment account since inception.

3) Transaction Activity Report:
   a) Parent Account: parent of “Account”
   b) Account: Security or Investment Account associated with transaction
   c) TxnNum: Transaction Number (Parent Transaction) assigned by Moneydance

                                                Page 4 of 12

Last Saved: October 26, 2012                                                                    Version 2.0
Investment Reports Explanation

     d) Date: Date of transaction
     e) TxnType: Type of Parent Transaction, in accordance with the following table:
                                 “Bank”           TXN_TYPE_BANK                 4
                                 “Buy”            TXN_TYPE_BUY                  0
                                 “BuyTransfer” TXN_TYPE_BUY_XFER                2
                                 “CoverShort”     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

     f)   Desc: Description field of transaction (Parent Transaction)
     g)   Buy: Dollar amount of security purchased
     h)   Sell: Dollar amount of security sold
     i)   Short: Dollar amount of security sold short
     j)   Cover: Dollar amount of security short-covered
     k)   Commission: Dollar amount of commission associated with buy/sell/short/cover
     l)   Income: Dollar amount of income
     m)   Expense: Dollar amount of expense
     n)   Transfer: Dollar amount of transfer (positive indicates transfer-in)
     o)   cashEffect: sum of Buy/Sell/Short/Cover/Commission/Income/Expense/Transfer
     p)   secQuantity: security quantity associated with transaction
     q)   MktPrice: Market Price of security on transaction date
     r)   Position: Total Split-Adjusted position in security after accounting for transaction.
     s)   Long Basis: Average cost basis of security with long position (includes commission)
     t)   Short Basis: Average cost basis of security with short position (includes commission)
     u)   Open Value: Mark-to-Market value of security (market price * position)

 A “Bank” transaction is any transaction where a transfer takes place between the investment account and
another (non-security) account, or an income or expense is assigned directly to an investment account. Examples
are transfers to and from bank accounts to investment accounts, or account-level expenses (margin interest) or
account level income (income on cash balance).

                                                      Page 5 of 12

Last Saved: October 26, 2012                                                                           Version 2.0
Investment Reports Explanation

     v)    CumUnrealGain: Open Value compared to long/short basis:
     w)    PerUnrealGain: Unrealized gain from previous transaction to current transaction
     x)    PerRealGain: Realized gain from Sell/Short Cover transaction against long/short basis.
     y)    Per Inc/Exp: Sum of transaction income/expense
     z)    PerTotalGain: period unrealized gain + period realized gain
     aa)   CumTotalGain: sum of all PerTotalGain to date.

4) Securities Prices Report
   a) id: Currency ID assigned by Moneydance
   b) Name: Currency Name
   c) Ticker: Currency Ticker or “NoTicker”
   d) Date: Snapshot Date:
   e) PricebyDate: Closing price on SnapshotDate
   f) PriceByDate(Adjust): Split Adjusted price as referenced by the current date

                                                   Page 6 of 12

Last Saved: October 26, 2012                                                                        Version 2.0
Investment Reports Explanation

Notes on Report Information:
1) Total Return:
   Total Return is calculated as shown below. Please note the following
   a) 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.
   b) 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:

     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).

     and                           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:

     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

2) Annualized Percent Return:
   a) 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.
   b) 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.
   c) This calculation method can be replicated by the XIRR function of Microsoft Excel™.

                                                  Page 7 of 12

Last Saved: October 26, 2012                                                                        Version 2.0
Investment Reports Explanation

3) Transaction Entry:
   a) Any expense associated with a buy/sell/short cover/cover short transaction is considered to be a
   b) All income and expense associated with an investment account is considered as income (or
         expense) associated with Cash.
4) ini file:
   a) The extension generates a file invextension.ini, which records the user’s selected path for report
         generation. invextension.ini is stored in the Moneydance home directory.

Notes on Classes:
1) The Main class registers the extension and passes Moneydance data to other classes.
2) The ReportControlFrame class receives the output of Main, creates a frame to hold the report
   controller GUI.
3) The ReportControlPanel class controls report output: the user selects report parameters, executes
   reports via the “Run Reports” button. .
4) 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.
5) The IAccount interface provides a standard for transaction reporting at either the Security level or
   Investment Account level it has implementations SecurityAccountWrapper and
     a) The SecurityWrapper class provides functionality for SecurityAccounts.
     b) The InvestmentAccountWrapper provides additional functionality for Investment Accounts.
6) TransactionValues returns one line of data for each parent transaction with buy, sell, short sell,
   cover short, commission, transfer, and security quantity data.
     a) 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.
     b) TransactionValues includes a GainsCalc object (current concrete implementations
        GainsAverageCalc and GainsLotMatchCalc), which calculates long and short basis for a security.

                                                Page 8 of 12

Last Saved: October 26, 2012                                                                      Version 2.0
Investment Reports Explanation

7) The TotalReport Class is an abstract class holding HashSets of SecurityReport and CompositeReport
   objects. Its concrete implementations are the TotalFromToReport and TotalSnapShotReport.
8) ComponentReport is an abstract class which is a superclass to both SecurityReport and
9) The 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).
10) The 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
11) Aggregator is an abstract class for the 5 classes which are used to aggregate leaf-level security
    Reports. To accomplish this, Moneydance classes are wrapped in classes which extend Aggregator.
    The extensions are InvestmentAccountWrapper, CurrencyWrapper, 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
12) The DateUtils and IOUtils classes provide useful methods for date math and input/output,
13) The GoalSeek, GoalSeekData, GoalSeekFunction, XIRR, XIRRData, and XIRRNPV classes are all used
    for calculation of Annualized Percent Return. More info can be found at
14) The 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).
15) The Prefs class holds report preferences.

Notes on Test Classes:
1) The project supports JUnit testing. Test classes are under the “test” folder.
2) BulkSecInfoTest checks for consistency of transaction data from the test database and
   stored data in csv format. Both the average cost and lot-matching cost accounting methods are
3) The ReportProdTest class contains 3 tests: testGetFromToReport() and testGetSnapReport()
   generate From/To and Snap reports, respectively, from the MD test database

                                                Page 9 of 12

Last Saved: October 26, 2012                                                                    Version 2.0
Investment Reports Explanation

     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.
4) 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.

A note on short sales:

Dividends Paid on Short Sales
By Roy Lewis | More Articles
December 6, 2002 | Comments (0)
With the market as it, many of you have decided to include shorting in your investment strategy. It's
quite possible that one of the companies that you shorted was one that pays a cash dividend. Since
you are likely borrowing the shares that you initially sold to create your short position, you are
required to reimburse the lender of the stock for the dividends that he missed. Your broker probably
notified you of that fact, and reduced your cash position in your account by the amount of the

The question then becomes: Where and how do you report this payment on your tax return? Is it an
adjustment to the basis of the stock in the short sale? Or is it a period expense that can be deducted
immediately, even if the short position was not closed in the tax year you're filing. As with much of
the tax law, there is no clear-cut answer.

The rule
If you borrow stock to make a short sale, you might have to remit payments to the lender in lieu of
the dividends distributed while you maintain your short position. You can deduct these payments
only if you hold the short sale open at least 46 days and you itemize your deductions.

If you close the short sale by the 45th day after the date of the short sale, you can't deduct the

                                                Page 10 of 12

Last Saved: October 26, 2012                                                                         Version 2.0
Investment Reports Explanation

payment made to the lender in lieu of the dividend. Instead, you must increase the cost basis of the
stock used to close the short sale by that amount.

To determine how long a short sale is kept open, don't include any period during which you hold,
have an option to buy, or are under a contractual obligation to buy substantially identical stock or
securities. In addition, don't include any period during which you are considered to have diminished
your risk of loss from the short sale by reason of holding one or more other positions in substantially
similar or related properties.

To deduct these expenses, they are treated as investment interest expenses, and are subject to all
of the rules and regulations involving investment interest expense. Report these expenses on
Schedule A of your tax return. If you don't itemize your deductions (i.e., you claim the standard
deduction) investment interest expense won't be tax-effective for you, and you'll miss this deduction.
And if you can't take the deduction because you don't itemize your deductions, it's lost forever. There
are no "elections" that you can make in order to use the investment interest deduction to reduce any
gain (or increase the loss) when you eventually close your short position.

An example
Let's say you short 100 shares of XYZ Company on Feb. 1 at $10 a share. On Feb. 15, your broker
notifies you that your account will be reduced by $50 for the dividend paid by XYZ Company to its
shareholders. On March 10, you close your short position by buying 100 shares of XYZ at $8 a
share. Since the short position was not open for at least 46 days, you cannot use the $50 in-lieu-of-
dividend payment as a current expense. Rather, this $50 is added to the price of the stock that you
purchased to close the position. In the example above, your net gain on your short position would be
$150 ($1,000-($800+$50)=$150).

Let's use the same example, but change the dates. Let's say that you don't close the short position
until May 15. In this case, the in-lieu payment of $50 would be treated as investment interest, which
is deductible on Schedule A (assuming that you itemize your deductions), and your gain on the
closing of the short position would be $200.

Now, this might not make much difference during the year, but at year-end, it might make the
difference between a current-period deduction and an adjustment to basis. So now is the time to
review your short positions and see if any will pay a dividend.
Roy Lewis lives in a trailer down by the river and is a motivational speaker when not dealing with tax issues,
and he understands that The Motley Fool is all about investors writing for investors. You can take a look at

                                                  Page 11 of 12

Last Saved: October 26, 2012                                                                           Version 2.0
Investment Reports Explanation

the stocks he owns as long as you promise not to ask him which stock to buy. He'll be glad to help you compute
your gain or loss when you finally sell a stock, though.

                                                   Page 12 of 12

Last Saved: October 26, 2012                                                                         Version 2.0

Shared By: