NAME¶
beancounter - Stock portfolio performance monitor tool
SYNOPSYS¶
beancounter [options] command [command_arguments ...]
COMMANDS¶
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
OPTIONS¶
--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
DESCRIPTION¶
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 found.
EXAMPLES¶
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 \
SPY:50:USD:ira:joe:142.25:20000620
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
command.
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.
TUTORIAL¶
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 \
IBM:100:USD:401k:bob:90.25:20000320
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 \
NT.TO:200:CAD:spec:bill:cad:90.25:20000212
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'"
MORE DETAILED COMMAND DESCRIPTION¶
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 period.
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 "
http://www.gloriamundi.org"
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 inverted'.
lsportfolio simply lists the content of the portfolio table. A SQL
restriction can be imposed.
addindex adds stocks a the index table. Currently, no further analysis
references this table.
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.
MORE DETAILED OPTION DESCRIPTION¶
--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 these.
--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
'SQLite2'.
--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 www.oanda.com 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.
SYSTEM OVERVIEW¶
The following section details some of the database and configuration options.
DATABASE REQUIREMENTS¶
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
BeanCounter.pm 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.
CONFIG FILE¶
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 converted
- 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 used)
- 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 Internet.
- 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.
ODBC CONFIGURATION¶
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
[beancounter]
Driver = /usr/lib/libpsqlodbc.so
Database = beancounter
Servername = localhost
[ODBC]
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
[PostgreSQL]
Description = PostgreSQL ODBC driver for Linux and Windows
Driver = /usr/lib/postgresql/lib/libodbcpsql.so
Setup = /usr/lib/odbc/libodbcpsqlS.so
Debug = 0
CommLog = 0
FileUsage = 1
after which
/etc/odbc.ini (or
~/.odbc.ini) can be used to define
actual data sources as follows:
[PostgreSQL]
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 =
[beancounter]
Description = Beancounter DB (Postgresql)
Driver = Postgresql
Trace = No
TraceFile =
Database = beancounter
Servername = some.db.host.com
UserName =
Password =
Port = 5432
Protocol = 6.4
ReadOnly = No
RowVersioning = No
ShowSystemTables= No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
BUGS¶
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.
SEE ALSO¶
Finance::BeanCounter.3pm,
smtm.1,
Finance::YahooQuote.3pm,
LWP.3pm,
Date::Manip.3pm,
Statistics::Descriptive.3pm,
setup_beancounter.1,
update_beancounter.1.
COPYRIGHT¶
beancounter is (c) 2000 - 2006 by Dirk Eddelbuettel <edd@debian.org>
Updates to this program might appear at
http://dirk.eddelbuettel.com/code/beancounter.html.
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 whatsoever.
The information that you obtain with this program may be copyrighted by Yahoo!
Inc., and is governed by their usage license. See
http://www.yahoo.com/docs/info/gen_disclaimer.html for more
information.
Equivalently, foreign exchange rates from
http://fx.sauder.ubc.ca are for
academic research and teaching. See
http://fx.sauder.ubc.ca/about.html
for more details.
ACKNOWLEDGEMENTS¶
The Finance::YahooQuote module, originally written by Dj Padzensky (and on the
web at
http://www.padz.net/~djpadz/YahooQuote/ as well as at
http://dirk.eddelbuettel.com/code/yahooquote) serves as the backbone
for data retrieval, which was also already very useful for the real-time
ticker
http://dirk.eddelbuettel.com/code/smtm.html.