Provided by: beancounter_0.8.10_all bug


       beancounter - Stock portfolio performance monitor tool


       beancounter [options] command [command_arguments ...]


        addindex index args       add stock(s) to market index 'indx'
        addportfolio sym:nb:fx:type:o:pp:pd ...
                                  add 'nb' stocks of company with symbol 'sym'
                                  that are listed in currency 'fx' to the
                                  portfolio with optional 'type' and 'owner'
                                  info, purchase price 'pp' and date 'pd';
                                  see below for a complete example
        allreports                combines dayendreport, status and risk
        addstock arg ...          add stock(s) with symbol arg to the database
        advancement               report on unrealized gains from lows
        backpopulate  arg ...     fill with historic data for given stock(s)
        checkdbconnection         test if connection to db can be established
        dailyjob                  combines update, dayendreport, status + risk
        dayendreport              reports p/l changes relative to previous day
        deactivate symbol ...     set stock(s) inactive in stockinfo table
        delete arg ...            delete given stock(s) from database
        destroydb                 delete the BeanCounter database
        fxbackpopulate  arg ...   fill with historic data for currency(ies)
        lsportfolio               list portfolio data
        plreport                  run an portfolio p/l report rel. to any day
        quote arg ...             report current data for given stock(s)
        retracement               report unrealized losses from highs (drawdowns)
        risk                      display a portfolio risk report
        split arg ...             split-adjust price history and portfolio
        status                    status summary report for portfolio
        update                    update the database with day's data
        warranty                  display the short GNU GPL statement


        --help                    show this help
        --verbose                 more verbose operation, debugging
        --date date               report for this date (today)
        --prevdate date           relative to this date (yesterday)
        --currency fx             set home currency
        --restriction sql         impose SQL restriction
        --extrafx fx1,fx2,...     additional currencies to load
        --forceupdate date        force db to store new price info with date
        --rcfile file             use different configuration file
        --[no]fxupdate            enforce/suppress FX update, default is update
        --[no]commit              enforce/suppress database update, default is commit
        --[no]equityupdate        enforce/suppress Equity update, default is update
        --[no]ubcfx               use/skip FX from UBC's Sauder school, default skip
        --splitby arg             split stock history + position by this factor [2]
        --dbsystem system         use db backend system, default is PostgreSQL
        --dbname name             use db name, default is beancounter


       beancounter gathers and analyses stock market data to evaluate portfolio performance.  It
       has several modes of operation. The first main mode is data gathering: both current data
       (e.g. end-of-day closing prices) and historical price data (to back-populate the database)
       can be retrieved both automatically and efficiently with subsequent local storage in a
       relational database system (either PostgreSQL, MySQL or SQLite) though any other system
       with an ODBC driver could be used). The second main mode is data analysis where the stored
       data is evaluated to provide performance information. Several canned reports types are
       already available.

       Data is retrieved very efficiently in a single batch query per Yahoo!  host from the
       Yahoo! Finance web sites using Finance::YahooQuote module (where version 0.18 or newer is
       required for proxy support). Support exists for North America (i.e. US and Canada), Europe
       (i.e. the Continent as well as Great Britain), several Asian stock markets, Australia and
       New Zealand.

       beancounter can aggregate the change in value for the entire portfolio over arbitrary time
       horizons (provided historical data has either been gathered or has been backpopulated).
       Using the powerful date-parsing routine available to Perl (thanks to the Date::Manip
       modules), you can simply say 'from six months ago to today' (see below for examples).

       beancounter has been written and tested under Linux. It should run under any standard Unix
       as long as the required Perl modules are installed, as as long as the database backend is


        beancounter update --forceupdate today

           This updates the database: it extends timeseries data (such as
           open, low, high, close, volume) with data for the current day,
           and overwrites static data (such as capital, price/earnings, ...)
           with current data. All stocks held in the database are updated
           (unless the --restriction argument instructs otherwise). The
           --forceupdate option lets the program corrects incorrect dates
           returned from Yahoo! (which happens every now and so often), but
           be careful to correct for this on public holidays. Note that
           the --restriction argument will be applied to the portfolio table,
           whereas the overall selection comes from the stockinfo table.

        beancounter addportfolio IBM:100:USD:401k:joe:90.25:20000320  \

           This adds IBM to the 401k portfolio of Joe, as well as SP500
           'Spiders' to his IRA portfolio. The stocks are also added to the
           general stock info tables via an implicit call of the stockinfo

        beancounter addstock LNUX RHAT COR.TO

           This adds these three Linux companies to the database without adding
           them to any specific portfolios.

        beancounter backpopulate --prevdate '1 year ago' \
                                 --date 'friday 1 week ago' IBM MSFT HWP

           This backpopulates the database with historic prices for three
           hardware companies. Note how the date specification is very general
           thanks to the underlying Date::Manip module.

        beancounter fxbackpopulate --prevdate '1 year ago' \
                                 --date 'friday 1 week ago' CAD EUR GBP

           This backpopulates the database with historic prices for these
           three currencies. Note how the date specification is very general
           thanks to the underlying Date::Manip module.

           Unfortunately, Yahoo! is a little bone-headed in its implementation
           of historic FX rates -- these are stored to only two decimals
           precision, just like stockprices. Unfortunately, convention is to
           use at least four if not six. Because of the limited information,
           risk from FX changes will be underestimated.

        beancounter plreport --prevdate '1 month ago' --date 'today' \
                               --restriction "owner='joe'"

           This calculates portfolio profits or losses over the last month. It
           also imposes the database restriction that only stocks owned by
           'joe' are to be included.

        beancounter status --restriction "type='401k'"

           This shows a portfolio status report with the restriction that only
           stocks from the '401k' account are to be included.

        beancounter risk --prevdate "6 month ago"

           This shows a portfolio risk report. This tries describes the
           statistically plausible loss which should be exceeded only 1 out
           of 100 times (see below for more details).

        beancounter dailyjob --forceupdate today

           Run a complete 'job': update the database, show a day-end profit/loss
           report, show a portfolio status report and show a riskreport. In the
           update mode, override a potentially wrong date supplied by Yahoo!
           with the current date.

        beancounter split --splitby 3 --prevdate 1990-01-01 ABC CDE

           Split-adjusts the (hypothetical) stocks ABC and CDE by a factor
           of three: price data in the database is divided by three, volume
           increased by 3 and similarly, in the portfolio shares are increased
           and cost is descreased.  Default dates are --prevdate and --date
           which may need adjusting.


       The following few paragraphs will illustrate the use of beancounter.  We will set up two
       fictional accounts for two brothers Bob and Bill (so that we can illustrate the 'owner'
       column).  The prices below are completely fictitious, as are the portfolios.

       We suppose that beancounter is installed and that the setup_beancounter command has been
       run. We can then create a two-stock (computer hardware) portfolio for Bob as follows:

        beancounter addportfolio SPY:50:USD:401k:bob:142.25:20000620 \

       Here we specify that 100 shares each of SPY and IBM, priced in US Dollars, are in Bob's
       portfolio which is tagged as a 401k retirement account. The (fictitious) purchase price
       and date are also given.

       Let's suppose that Bill prefers networking equipment, and that he has a brokerage account
       in Canada:

        beancounter addportfolio CSCO:100:USD:spec:bill:78.00:19990817 \

       Now we can backpopulate the database from 1998 onwards for all four stocks:

        beancounter backpopulate --prevdate 19980101 CSCO IBM NT.TO SPY

       With this historical data in place, we now compare how Bob's portfolio would have fared
       over the last 18 months:

        beancounter plreport --prevdate '18 months ago' \
                               --restriction "owner='bob'"

       Note how we use double quotes to protect the arguments, and how the SQL restriction
       contains a further single quote around the literal string.

       We can also review the performance for Bill at the most recent trading day:

        beancounter dayendreport --restriction "owner='bill'"

       or the status of holdings and their respective values:

        beancounter dayendreport --restriction "owner='bill'"

       Similarly, a risk reports can be run on this portfolios per

        beancounter risk --restriction "owner='bill'"


       addportfolio is the most important 'position entry' command. As with other commands,
       several arguments can be given at the same time. For each of these, records are separated
       using a colon and specify, in order, stock symbol, number of stocks held, currency,
       account type, account owner, purchase price and purchase date.  Only the first three
       arguments are required, the others are optional. Executing addportfolio implicitly
       executes addstock.  The account type column can be used to specify whether the account is,
       e.g., a tax-sheltered retirement account, or it could be used to denote the brokerage
       company is it held at.

       plreport retrieves the most recent quotes(s). This is useful for illiquid securities which
       might not have traded that day, or if a public holiday occurred, or if there was a data
       error at Yahoo!. Two dates can be specified which determine the period over which the
       profit or loss is computed. This will fail if price data (or currency data in the case of
       foreign stocks data) data is not available for either of those two dates. This may be
       restrictive for foreign stocks where we cannot backpopulate due to lack of public data
       source for historical currency quotes. Major currencies can be retrieved from Yahoo!, but
       only to two decimals precisions.

       dayendreport is similar to plreport but is always over a one-day period.  It also uses
       only one date record by calculating performance given the 'previous close' data.

       status shows holdings amounts, total position values, annualized returns in percentages
       and holding periods in days. Note that the annualized returns can appear excessive if,
       e.g., a ten-day return from a recently purchased stock is extrapolated to an annual time

       risk shows a portfolio risk report which describes the statistically plausible loss which
       should be exceeded only 1 out of 100 times.  In other words, the loss estimate has a
       critical level of 99%. This risk level is estimated via two methods. The first is non-
       parametric and assumes no particular model or distribution; it computes the 1% quintile of
       the return distribution and displays it as well as the corresponding asset value at risk.
       The second method uses the standard Value-at-Risk (VaR) approach. This uses the 1%
       critical value of the Normal distribution and implicitly assumes a normal distribution for
       returns. See "" for more introduction and references. If the
       distribution of normalitty was perfectly true, both measures would coincide. A large
       difference between the two estimates would indicate that the return distribution might be
       rather non-normal.  Another view of the riskiness of a given position is provided by the
       last column with the 'margVaR' heading. It shows the marginal Value-at-Risk. Marginal VaR
       is commonly defined as the risk contribution of the given position to the total portfolio,
       and calculated as the difference in the VaR of the full portfolio and the VaR of an
       otherwise identical portfolio with the given position removed.  Note that calculating
       marginal VaR is fairly slow (on the order of O(n^3) ].

       retracement shows a 'drawdown' report. Drawdown is commonly defined as the percentage loss
       relative to the previous high. The default period is used, but can be altered with the
       --date and --prevdate options. The default period is also corrected for the actual holding
       period. In other words, if a stock has been held for two months, only those two months are
       used instead of the default of six months -- but if the last months has been selected via
       --prevdate then it is used.  For short positions, the analysis is inverted and relative to
       the previous low. The report displays each stock, the number of shares held, the current
       price and holdings value. The next two columns show the maximum price attained in the
       examined period, and the percent decline relative to it. The last column shows the
       unrealized loss relative to the maximum price over the period. The aggregate holdings
       value, percent decline and unrealized loss are shown as well.

       advancement does the opposite of drawdown -- it computes unrealized gains relative to the
       minimum price in the period. The discussion in the preceding paragraph applies `but

       lsportfolio simply lists the content of the portfolio table.  A SQL restriction can be

       addindex adds stocks a the index table. Currently, no further analysis references this

       addstock adds stocks to the database. From then on data will be retrieved for the given
       symbol(s) and stored in the database whenever the update command is executed.

       backpopulate fills the database with historic prices for the given symbols and date
       period. Note that this works well for stocks and mutual fund. Options have no historic
       data stored. Currencies are stored with limited precision as noted above.

       quote simply shows a price quote  for the given symbol(s).

       update updates the database with quotes for all stocks for the given day. No output is
       generated making the command suitable for cron execution.

       dailyjob is a simple convenience wrapper around update, dayendreport, status and risk,

       allreports is a another covenience wrapper around dayendreport, status and risk.

       deactivate will set the active column in stockinfo for the given symbol(s) to false
       thereby inhibiting any further updates of symbol(s).  The existing data for symbol(s) is
       retained. Use this when a stock is acquired, delisted, or you simply want to stop tracking
       it -- but do not want to purge the historical data.

       split adjusts the price database, and the portfolio holdings, for stock splits.  The
       default factor is 2, this can be adjusted with the option --splitby. The dates arguments
       can be set with --prevdate and --date.

       delete removes the given symbols from the database.

       destroydb deletes the BeanCounter database.

       checkdbconnection simply opens and closes the database handle, and returns a specified
       exit code which can then be tested. This is used in the setup_beancounter command.

       warranty display a short GNU General Public License statement.


       --currency can be used to select a different home currency.  Instead of having all values
       converted to the default currency, the selected currency is used.

       --date allows to choose a different reference date. This is then be be used by commands
       working on a date, or date period, such as plreport, dayendreport, backpopulate,
       fxbackpopulate or status. --prevdate allows to choose a different start date for return
       calculations, or data gathering.

       --restriction can be used to restrict the database selection. The argument must be a valid
       part of valid SQL statement in the sense that existing columns and operators have to be
       employed. The argument to this option will be completed with a leading and. The SQL
       restriction will typcally be over elements of the portfolio table which comprises the
       columns symbol, shares, currency, type, owner, cost and date. A simple example would be
       currency='CAD'. Note that this has to protected by double quotes "I on the command-line.

       --extrafx allows to gather data on additional currency rates beyond those automatically
       selected as shares are listed in them. A typical example would be for a European investor
       wanting to convert from the EUR in which the shares are listed into one of the member
       currencies which beancounter would no longer retrieve as shares are no longer listed in

       --forceupdate allows to overwrite an potentially wrong date in the database update.
       Unfortunately, it appears that Yahoo!  occasionally reports correct prices with an
       incorrect date such as the previous day's. In such a case, this option, along with an
       argument such as 'today' can override the bad date datapoint and avoid a hole in the
       database. The downside of this approach is that it would "double" the previous data in the
       case of a public holiday, or even if it was run the weekend. A somewhat smarter comparison
       to previously stored data might prevent that, but would be more complex to implement.

       --rcfile allows to specify a resource file different from the default ~/.beancounterrc.

       --dbsystem allows to switch to a different database backend. The default is PostgreSQL but
       MySQL and SQLite are also supported. For SQLite, the default is now version 3.* but the
       previous version -- which is not binarily compatible -- is supported as well with argument

       --dbname allows to switch to an alternate database. The default is 'beancounter'. This can
       be useful for testing new features.

       --fxupdate is a boolean switch to enforece updates of FX rates during 'update'. The
       default is 'true' but '--nofxupdate' can be used to suppress the update of foreign
       exchange rates.

       Similarly, --equityupdate is a boolean switch to enforece, or suppress updates of Equity
       (i.e. stock) data during 'update'. The default is 'true' but '--noequityupdate' can be
       used to suppress the update of foreign exchange rates.

       --ubcfx is a boolean switch to use the 'PACIFIC' FX rate service from the Sauder School at
       UBC. This is useful when the default FX rate service at Yahoo! is erratic, or unreliable.
       While the PACIFIC server provides a wider variety of exchange rates, Yahoo! can still be
       useful as it can provide more columns (open/high/low). However, during most of 2005,
       Yahoo! has been unrealiable for the exchange rates and has not provided historical FX
       data. On the other hand, the UBC service does not run on Canadian holidays so it cannot
       really server as a full substitute. Contributions for a new data acquisition, maybe via would be welcome.

       --splitby can be used to set a stock split factor other than the default of 2.

       --host can be used to point to a machine containing the PostgreSQL or MySQL database. The
       machine can be remote, or it can be the actual machine beancounter is running on. If a
       hostname is given, tcp/ip connection are used. If no hostname is given, the default value
       of 'localhost' implies that local socket connections are used which may be easier to
       employ for less experienced adatabase users.

       Also, --commit is a boolean switch to suppress actual database updates if the negated
       --nocommit is selected. This is useful mostly in debugging contexts.

       The --verbose and --debug switches can be used in debugging an testing, and --help
       triggers the display of help message.


       The following section details some of the database and configuration options.

       beancounter currently depends on either PostgreSQL, MySQL, SQLite (version 2 or 3) or any
       other database for which an ODBC driver is available (though the required tables would
       have to created manually in the ODBC case). Yet another DB backend could be added provided
       suitable Perl DBI drivers are available. For PostgreSQL, MySQL and SQLite, the
       setup_beancounter script can create and initialize the database, form the required tables
       and fills them with some example data. It is a starting point for local modifications.

       The connection to the database is made via a dedicated function in the
       module, changes would only have to be made there.  As of this writing the Perl DBI (the
       database-independent interface for Perl) is used along the DBI drivers for PostgreSQL,
       MySQL, SQLite and ODBC. Ports for Oracle, Sybase, ... are encouraged.

       A configuration file ~/.beancounterrc is read if found. It currently supports the
       following options:

       currency to specify into which home currency holdings and profits/losses have to be
       host to specify the database server on which the BeanCounter database resides (this is
       needed only for the alternate connection via the DBI-Pg driver in case DBI-ODBC is not
       user to specify the userid for the database connection; if needed. If not specified, the
       current user id is used.
       passwd to specify the password for the database connection, if needed.
       dbsystem to select a database backend, e.g. to switch from PostgreSQL to MySQL or SQLite
       or SQLite2 (the previous format of SQLite).
       dbname to select a different default database name other than the default of 'beancounter'
       proxy to specify the address of a firewall proxy server if one is needed to connect to the
       firewall to specify a firewallid:firewallpasswd combination, if needed.
       odbc is a switch to turn ODBC connection on or off
       dsn to use a different data source name when ODBC is used
               An example file example.beancounterrc should have come with the sources (or the
               Debian package); please consult this file for more examples.

       There are now several ODBC systems available for Linux / Unix. The following ~/.odbc.ini
       work with the iODBC library and the PostgreSQL ODBC driver on my Debian GNU/Linux system:

          [ODBC Data Sources]
          beancounter = BeanCounter Database

          Driver       = /usr/lib/
          Database     = beancounter
          Servername   = localhost

          InstallDir = /usr/lib

       Alternatively, the unixODBC library can be used with the following scheme for
       /etc/odbcinst.ini (or ~/.odbcinst.ini) to define the Postgres database drivers

          Description     = PostgreSQL ODBC driver for Linux and Windows
          Driver          = /usr/lib/postgresql/lib/
          Setup           = /usr/lib/odbc/
          Debug           = 0
          CommLog         = 0
          FileUsage       = 1

       after which /etc/odbc.ini (or ~/.odbc.ini) can be used to define actual data sources as

          Description     = PostgreSQL template1
          Driver          = PostgreSQL
          Trace           = No
          TraceFile       = /tmp/odbc.log
          Database        = template1
          Servername      = localhost
          UserName        =
          Password        =
          Port            = 5432
          Protocol        = 6.4
          ReadOnly        = Yes
          RowVersioning   = No
          ShowSystemTables= No
          ShowOidColumn   = No
          FakeOidIndex    = No
          ConnSettings    =

          Description     = Beancounter DB (Postgresql)
          Driver          = Postgresql
          Trace           = No
          TraceFile       =
          Database        = beancounter
          Servername      =
          UserName        =
          Password        =
          Port            = 5432
          Protocol        = 6.4
          ReadOnly        = No
          RowVersioning   = No
          ShowSystemTables= No
          ShowOidColumn   = No
          FakeOidIndex    = No
          ConnSettings    =


       Finance::BeanCounter and beancounter are so fresh that there are only missing features :)
       Seriously, check the TODO list. This code or its predecessors have been used by the author
       since the end of 1998.


       Finance::BeanCounter.3pm, smtm.1, Finance::YahooQuote.3pm, LWP.3pm, Date::Manip.3pm,
       Statistics::Descriptive.3pm, setup_beancounter.1, update_beancounter.1.


       beancounter is (c) 2000 - 2006 by Dirk Eddelbuettel <>

       Updates to this program might appear at

       This program is free software; you can redistribute it and/or modify it under the terms of
       the GNU General Public License as published by the Free Software Foundation; either
       version 2 of the License, or (at your option) any later version.  There is NO warranty

       The information that you obtain with this program may be copyrighted by Yahoo! Inc., and
       is governed by their usage license.  See for more information.

       Equivalently, foreign exchange rates from are for academic
       research and teaching. See for more details.


       The Finance::YahooQuote module, originally written by Dj Padzensky (and on the web at as well as at serves as the backbone for data retrieval,
       which was also already very useful for the real-time ticker