BEANCOUNTER(1p)

📦 version 0.8.11, in ubuntu jammy

💖 beancounter(1p) 💖

okay bestie, sit down, hydrate, grab a snack 🍓 — this is the manual for beancounter, the stock portfolio performance monitor, and I'm about to make it make sense. no cap.

✨ NAME ✨

beancounter — Stock portfolio performance monitor tool 🫶

💫 SYNOPSYS 💫

beancounter [options] command [command_arguments ...]

🎀 COMMANDS 🎀

  • addindex index args — adds stock(s) to the market index 'indx' 💅
  • addportfolio sym:nb:fx:type:o:pp:pd ... — adds nb stocks of the 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. there's a whole example further down, iykyk 👀
  • allreports — combines dayendreport, status and risk ✨ iconic trio behavior
  • addstock arg ... — adds stock(s) with symbol arg to the database
  • advancement — reports on unrealized gains from lows 📈
  • backpopulate arg ... — fills with historic data for the given stock(s)
  • checkdbconnection — vibe-checks whether a connection to the db can actually be established
  • dailyjob — combines update, dayendreport, status + risk
  • dayendreport — reports p/l changes relative to the previous day
  • deactivate symbol ... — sets stock(s) inactive in the stockinfo table
  • delete arg ... — deletes the given stock(s) from the database
  • destroydb — deletes the BeanCounter database 💀 (yes, for real, it said what it said)
  • fxbackpopulate arg ... — fills with historic data for the currency(ies)
  • lsportfolio — lists portfolio data
  • plreport — runs a portfolio p/l report rel. to any day
  • quote arg ... — reports current data for the given stock(s)
  • retracement — reports unrealized losses from highs (drawdowns) 😭
  • risk — displays a portfolio risk report
  • split arg ... — split-adjusts price history and portfolio
  • status — status summary report for portfolio
  • update — updates the database with the day's data
  • warranty — displays the short GNU GPL statement

🌈 OPTIONS 🌈

  • --help — shows this help
  • --verbose — more verbose operation, debugging 🔍
  • --date date — report for this date (default is today)
  • --prevdate date — relative to this date (default is yesterday)
  • --currency fx — sets the home currency
  • --restriction sql — imposes an SQL restriction
  • --extrafx fx1,fx2,... — additional currencies to load
  • --forceupdate date — forces the db to store new price info with this date
  • --rcfile file — uses a different configuration file
  • --[no]fxupdate — enforces/suppresses the FX update; default is update
  • --[no]commit — enforces/suppresses the database update; default is commit
  • --[no]equityupdate — enforces/suppresses the Equity update; default is update
  • --[no]ubcfx — uses/skips FX from UBC's Sauder school; default is skip
  • --splitby arg — splits stock history + position by this factor [default 2]
  • --dbsystem system — uses this db backend system; default is PostgreSQL
  • --dbname name — uses this db name; default is beancounter

🦋 DESCRIPTION 🦋

so like, what is beancounter? it gathers and analyses stock market data to evaluate portfolio performance. it has several modes of operation, bestie. the first main mode is data gathering: both current data (think end-of-day closing prices) and historical price data (so it can back-populate the database) can be retrieved — automatically and efficiently — and then stored locally in a relational database system (either PostgreSQL, MySQL or SQLite, though honestly any other system with an ODBC driver could be used too). the second main mode is data analysis, where the stored data gets evaluated to give you performance information. and several canned report types are already available, periodt.

data is retrieved very efficiently in a single batch query per Yahoo! host from the Yahoo! Finance web sites, using the Finance::YahooQuote module (where version 0.18 or newer is required for proxy support, fyi). support exists for North America (i.e. the 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, obvs. and 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, and 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 correct 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. fake it till you make it 💅

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 literally the most important 'position entry' command, bestie. Like, the main character of position entry. And fr fr, just like the other commands, you can hand it several arguments at once. For each of these, records get separated using a colon : and they 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 rest are optional, no pressure. ✨ Executing addportfolio implicitly executes addstock, so it's giving two-for-one energy. 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 it is held at. iykyk.

plreport retrieves the most recent quote(s). This is sooo 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. Heads up though — 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. That 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 precision. 💀

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. Cute and simple. 🎀

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. So don't get too excited, bestie. 🙈

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:

  1. 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.
  2. 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. slay, it's all there. 💅

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. periodt. 🌈

lsportfolio simply lists the content of the portfolio table. A SQL restriction can be imposed. easy peasy. 🍓

addindex adds stocks a the index table. Currently, no further analysis references this table. so it's just vibing there. 🦋

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. set it and forget it, bestie. ✨

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). quick lil peek. 👑

update updates the database with quotes for all stocks for the given day. No output is generated making the command suitable for cron execution. 🤫 (silent but deadly efficient)

dailyjob is a simple convenience wrapper around update, dayendreport, status and risk. the whole squad in one command. 🫶

allreports is a another covenience wrapper around dayendreport, status and risk. (yes, the typo is canon, we don't fix what we love)

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. it's giving "we're not deleting, we're just ✨archiving✨". 😌

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. bye bye 💀

destroydb deletes the BeanCounter database. NUCLEAR OPTION. 🚨

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. the legal girlies get their moment. 📜

✨ 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 " 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.
  • --equityupdate — Similarly, 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. 🫶 (the devs are begging, bestie)
  • --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. 💻
  • --commit — Also, is a boolean switch to suppress actual database updates if the negated --nocommit is selected. This is useful mostly in debugging contexts.
  • --verbose and --debug — The switches can be used in debugging an testing, and --help triggers the display of help message. 🙋

🌈 SYSTEM OVERVIEW 🌈

ok so this bit is where we get into the database + config tea ☕ like what does beancounter actually need to run, and how do you tell it where stuff lives. buckle up bestie 💖

🗄️ DATABASE REQUIREMENTS 🗄️

so rn beancounter depends on either PostgreSQL, MySQL, SQLite (version 2 or 3), or literally any other database that has an ODBC driver available — but heads up: in the ODBC case you'd have to create the required tables manually yourself, no auto-magic 😭.

and if you wanted, you could add yet another DB backend, as long as suitable Perl DBI drivers exist. so the door is open 🚪✨

for PostgreSQL, MySQL and SQLite, the setup_beancounter script can:

  • create and initialize the database
  • form the required tables
  • fill them with some example data

it's giving "starter pack" — it's a starting point for local modifications, not the final form 💅

the connection to the database is made via a dedicated function in the BeanCounter.pm module, so if you ever need to change how it connects, that's the only place you'd have to touch. slay. as of this writing, the Perl DBI (the database-independent interface for Perl) is used, along with the DBI drivers for PostgreSQL, MySQL, SQLite and ODBC.

and ports for Oracle, Sybase, ... are encouraged 👑 like pls go off, we'd love that.

⚙️ CONFIG FILE ⚙️

a configuration file ~/.beancounterrc is read if found. rn it 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 works 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 💀

ngl 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, so like... it's been through some things 🫶

👀 SEE ALSO 👀

  • 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 <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. 🚨 like fr fr, none, zero, nada 💀

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

and that's the whole page bestie!! 🎀 this was BEANCOUNTER(1p), from the perl v5.32.1 toolchain, dated 2021-11-10 — go forth and count your beans, you main character 💅👑💖

📦 where this page comes from