BEANS -- An Investment-Club Accounting Program
by Kip Rugger & Eugene Reimer 1999
(revised 2007-October)
INTRODUCTION
This program helps with the bookkeeping and reporting chores of the treasurer
of an investment club. It may work "right out of the box" for a Canadian
investment club; it may also need some customization, if your club has
different rules about contributions or redemptions. For a club in another
country, you will need some knowledge of your tax laws, before you can
determine whether this program is suitable.
Although primarily intended for investment clubs, this program may also be
helpful with some chores for an individual Canadian investor, especially one
with foreign trades and brokerage accounts in foreign currencies, since in
such cases the most beneficial tax treatment is too much work for "by hand"
methods.
INSTALLING
You need to "unzip" the supplied zip-file into a newly created directory;
a directory named "beans" is suggested.
On one of the Microsoft operating-systems, save the supplied zipfile to a
newly created folder C:\beans; then start a "Command-Prompt" window, and in it
type the following:
cd \beans
unzip zipfile.zip
Or, using "Winzip", click on Actions, then Extract, fill in the location as
C:\beans and then click on Extract.
TESTING YOUR INSTALLATION
The Windows-version of ICON is included in this distribution. If you're using
any other system, then you install ICON from
www.cs.arizona.edu/icon.
It's free, and available for practically any platform. Incidentally, the
definitive book on ICON "The Icon Programming Language, Third Edition" (which
the author bought for $51.95 in the 1990's) is now available in electronic
form, for free, at that U of Arizona website. If you expect to use ICON for
other projects, you'll want to install it into a directory that's mentioned in
your PATH. Otherwise, you could simply copy the executables to your "beans"
directory. (In Windows the default location is \winicon\bin\NTICON*.EXE.)
Now use ICON to translate beans.icn into an executable program. On Windows,
in a Command-Prompt window, type
cd \beans
nticont beans.icn
to do that translation. (On a Unix system use slash instead of backslash and the ICON-translator will be named icont rather than nticont).
Then type the following:
beans
to invoke the beans program, which will read the supplied beans.asc file,
producing files rep.asc rep.htm rep.tex. Take a look at the rep.asc file,
or use your browser to look at rep.htm.
You should also verify that your favourite text-editor works on the
beans.asc file, and that you can print at least one of rep.asc or rep.htm.
(If you have a "make" program, then the command-line "make" will do both of
the above steps, using the supplied Makefile.)
OPTIONAL SOFTWARE INSTALLATION
The beans program produces T5013 forms in Postscript. If you lack the means
to print Postscript, you can install Ghostscript including GSview from
pages.cs.wisc.edu/~ghost.
To further improve your printing choices, you may also want TeX plus LaTeX;
a free version of such software for Windows is available from
www.miktex.org.
If you run the Windows operating-system, you may want to install a suite of
"Unix-tools for Windows",
in order run the supplied "bash" scripts. Running "bash" scripts is not needed for the basic bookkeeping and reporting functions of beans;
but is needed for the printing of the T5013's at year-end, although translating that simple script to a Windows BAT script is another solution.
Many Windows-users are reluctant to use any software that involves the
command-line, largely because of the hideous command-prompt (aka Dos-Box)
provided by Windows. It comes with the 80-column, 25-line, white-on-black
look of a 1960's-vintage dumb-terminal, however this can be changed to taste,
by right-clicking on the title-bar, selecting Defaults, and so on; see also
support.microsoft.com/kb/309019.
We recommend black-on-white text, with approximately 160-columns, 60-lines.
Or, you may want to try an alternative such as the open-source "Console" from
sourceforge.net/projects/console
(there's also a shareware program called "Winconsole").
Windows users will need to install a text-editor, since the ones provided by
Microsoft are inadequate. Notepad can be used in a pinch, but only on very
small files, so you'll soon be forced to look elsewhere. Here's information
on alternatives:
www.mydigitallife.info/2007/05/04/windows-vista-notepad-replacement-alternative/
Mac-OSX users may also want to shop around for a text-editor, since the one (TextEdit) provided by Apple comes configured to be a WYSIWYG word-processor rather than a
text-editor. However it can be reconfigured to behave like a text-editor; see
support.apple.com/kb/TA20406.
REPORT GENERATION
Three formats of output are available: TeX, HTML and plain-Ascii. TeX will
provide the best results where supported. HTML is the second choice for
situations where TeX is not available, the idea being to print from Netscape
or Internet-Exploder. The plain-Ascii is good for debugging, emailing, etc,
and in situations where neither tex nor html can be printed.
GETTING STARTED
The input comes from a single file, beans.asc in this directory.
The format should be pretty obvious, but there are a few non-obvious
points.
Say you have been investing for many years, and that you wish to start
using beans to do your tax-calculations for 1999. To get started, you
will need to describe the "state" of your investments at the end of 1998.
You will need the balances in each broker-account, the quantity and cost
of each stock that you held, as of 1998dec31.
Here is a "startup" example for a person named John Doe:
CURRENCY CA$ ca $ca can Canadian
CURRENCY US$ us $us usa American
CLUB JDI (John Doe Investments) CA$ 1.00 1 PTBi TBI
CNCYOPT INC WITHIN
SPREAD US$ 0.020
98dec PERIOD US$ 1/0.65231
BROKER CAbroker WaterhouseCA CA$ -58.00
BROKER USbroker WaterhouseUS US$ 652.31
BANK USmmf WtrhsMMFndUS US$ 0.00
BANK misc Misc CA$ -10000.00
STOCK BCE bce CA$ CAbroker 100 3029.000000
STOCK BankOfMontreal bmo CA$ CAbroker 100 6029.000000
MEMBER me me 1 0.000000
Subsequent sections will tell you more about the statements shown above.
For now you can copy them as shown, replacing "John Doe" with your own
name, replacing "Waterhouse" with the name of your broker. (You can
simply edit the supplied beans.asc.)
You will create as many STOCK statements as you held stocks on 98dec31.
Each such statement names the stock, gives the symbol, specifies the
currency, identifies the broker-account, specifies the quantity, and
specifies the cost. Note that the cost is given in Canadian dollars!
(In whatever currency is specified on the CLUB statement.)
The balance in each broker or bank-account is specified as a number at
the end of the line. The balance in a Canadian account is given in
Canadian dollars; the balance in a US$ account is given in American
dollars.
The program requires that you have have at least one MEMBER statement,
with a non-zero number-of-units (the first of the two numbers). The
program also does a consistency-check ensuring that the assets equal the
liabilities. There are several ways for you to calculate initial values
that will satisfy this requirement.
We recommend that you copy the MEMBER line as shown, and that you compute
a negative number for the initial-balance in the "misc" account such that
the assets sum to zero. In other words, you add up the cost of your stocks,
then add your cash balances; then you put the negative of that sum as the
"misc" balance, and voila you are done with the tough part!
(When summing the cash balances, remember to multiply a US$ balance by the
same conversion-factor that you specified in the PERIOD line.)
If you don't get it quite right on your first try, not to worry; the
program will complain, showing you the values for assets and for
liabilities. The magnitude of the difference, will likely make the
problem obvious.
To check just the "startup" month of 1998december, enter the command-line:
beans 98dec
then look at the rep.asc output file.
The "starting up" part will be mildly difficult; but after that we can
assure you that the program will be surprisingly easy to use, whether for
a club or for an individual.
Of course, the "starting up" part is much easier for a new club or a new
individual-investor. In this case there are no STOCK statements and all
the balances are zero.
REPORT INPUT
This section is addressed to the needs of a person taking over as the
treasurer of the Blue Sky Club. Most of it will apply to other clubs
as well.
In order to prepare a report, you must first collect the relevant
data: the closing prices for the US-dollar, the DOW, and the TSE,
on the last business day of the month. This is very easy to forget!
The rest of the data comes in the broker's statement.
The data for the current month is appended to the beans.asc file.
The easiest way to start this operation is to copy the previous
month's data and edit it.
The order of the statements is arbitrary, except that a "declaration" must
preceed a "use"; the following order works, but we advise picking an
order that corresponds to that used by your broker:
PERIOD --- delimits new period, must be first
PRICE US$ xxx --- show US$ at month-end
PRICE DOW xxx --- show DOW at month-end
PRICE TSE xxx --- show TSE at month-end
BANK/BROKER/MEMBER/... --- put "declarations" here
DIV/EXP/INT/BUY/SELL/MOVE --- in order by broker then by date
PRICE DonohueA xxx --- stock prices at month-end
MEETING/MEMBUY/MEMDRAW --- unit-purchases, are done after month-end
The statements are free format; fields are separated by one or more spaces.
If a name contains spaces, enclose it in parentheses.
Here is a sample month with commentary:
98dec PERIOD
The first statement must be PERIOD. The report for this period
will show the contents of the December broker statement, and will
be handed out at the January meeting.
PRICE US$ 1.5330
Specify the price of one US-dollar in Canadian-dollars. You may also
enter a reciprocal-price as: PRICE US$ 1/0.6523
PRICE DOW 9181.43
PRICE TSE 6485.94
The DOW and TSE as of Dec 31 (end of day).
98dec PERIOD US$ 1.533 DOW 9181.43 TSE 6485.94
One statement that's equivalent to the preceding 4 statements.
You may put several price-quotes onto one PRICE statement as well as onto
the PERIOD statement.
PRICE DonohueA 29.30
PRICE Arco 65.3750
PRICE Johnson&Johnson 83.8750
Stock prices, taken from the broker's statement. These prices are
quoted in the "default" currency, ie that which was declared to be
"natural" for the stock when it was declared by a STOCK statement.
If you must quote in another currency, simply append it to the
number, eg PRICE Johnson&Johnson 128.5823CA$.
Here and everywhere else numbers may be given in the fractional format,
eg PRICE Arco 65+3/8. It is rumoured that the US markets will be
giving up this clumsiness soon...
dec01 DIV DonohueA BrokerCA 16.00
dec08 DIV Johnson&Johnson BrokerUS 12.50 1.87
dec15 CAPDIV Healthcare BrokerCA 95.00
Dividends, taxed-dividends (the second number is the tax-withheld), and
a capital-dividend (which is a costbase-adjustment rather than income).
Here we show the appropriate account as in BrokerCA. The amounts are in
the currency of that account.
STOCK Arco arc US$
dec11 BUY Arco BrokerUS 50 78.50 29.00
dec11 SELL Healthcare BrokerCA 500 2.00 38.70
Buy 50 shares of Arco at 78.50US$ each, paying 29.00US$ in brokerage.
Sell 500 shares of Healthcare at 2.00CA$ paying 38.70CA$ in brokerage.
The STOCK declaration shows that Arco is traded in US-dollars; this will
be the default-currency for the PRICE statement; however, BUY and SELL
statements are in broker-currency, like DIV etc.
Short-trades are handled by "BUYing" a negative quantity of stock, then
later "SELLing" a negative quantity. This will seem logical after you
have considered the costbase and gain/loss calculations.
dec11 BUY Arco (arc) BrokerUS 50 78.50 29.00
As of 2003, the STOCK declaration may be omitted -- it is only needed if
you wish to quote prices in a currency other than that of the Broker
used to BUY it. The stock-symbol may be specified on the initial BUY.
dec11 BUY Schwab BrokerUS 50 0.00 0.00
Buy 50 shares of Schwab at zero dollars and no brokerage. This indicates
a stock split.
dec10 MOVE BrokerCA BrokerUS 3247.65 2100.00
Move money from BrokerCA to BrokerUS with a currency conversion. Here we
show the originating and final amounts, which will go into computing the
cost of the conversion.
dec10 MOVE BrokerCA Kroemer 1000.00
A withdrawal to pay member Kroemer. (This will be done some time after
the selling of units by the MEMDRAW statement.)
dec01 MOVE Kroemer BrokerCA 20.00
dec01 MOVE Selwood BrokerCA 20.00
dec01 MOVE Yost BrokerCA 20.00
These MOVE's reflect a deposit of 20.00 from each of three members.
dec01 DEPOSIT BrokerCA Kroemer 20 Selwood 20 Yost 20
This statement is equivalent to the 3 preceding MOVE statements.
MISCOPT COMBINED
Most readers should skip this part and the following MOVE examples.
The examples involving a "bank" account for each member, assume the use
of MISCOPT SEPARATE. If you prefer to have a single such account, then
you need to specify MISCOPT COMBINED in your input file -- it will apply
to unit purchases done with MEETING and MEMBUY statements in the same
period, but strictly speaking it starts to apply in the following period.
When using COMBINED, replace member-name in the MOVE examples with misc.
dec10 MOVE (payable to Kroemer) BrokerCA misc 1000.00
This is how you enter a cheque drawn on BrokerCA to pay Kroemer, when
using MISCOPT COMBINED.
After the word MOVE you may add a parenthesized comment to appear on the
report.
dec01 MOVE misc BrokerCA 60.00
This is how you enter a deposit of sixty dollars, possibly 20 from each
of the 3 members, when using MISCOPT COMBINED. This was the old way of
doing things for the Blue Sky Club, it meant less data-entry, but it
required more in the way of other record-keeping. The new SEPARATE
method is less error-prone for the treasurer, and it provides reminders
to other members since they see their payment status on each report.
We advise everyone to use SEPARATE, which is the default, and we only
offer COMBINED so that the old BSC club records will continue to be
supported.
dec16 INT BrokerCA 4.07
dec16 INT BrokerUS -6.24
This is how you enter interest-income and margin-costs.
dec31 EXP Postage-Young Young 37.44
MEMBUY Young 37.44
This is an expense that is payed in units.
MEETING Selwood 31.00 20.00
This shows Selwood's meeting-expenses were $31; and that each "active"
member contributed $20.
When a new member joins, his/her first contribution is entered with an
explicit MEMBUY statement; in the following month the new member will
be treated as an "active" member.
MEMDRAW Kroemer 1000.00 90
MEMDRAW Martin ALL 90
This shows a member-withdrawal of a thousand Canadian-dollars, and another
member resigning. The second number (90) specifies the maximum waiting-period
in days.
CLASSA Yost
This shows that Yost has gone Class-A for the month. Class-A means
non-voting.
Several other verbs exist, but are seldom used:
BANK -- create a bank account
BROKER -- create a broker account
CLUB -- specify the club's name and the currency it uses
CURRENCY -- add a new currency
INDEX -- add a new index (as in DOW or TSE)
MEMBER -- add a new member to the club
MISCOPT -- specify whether using a COMBINED "misc" account, or a SEPARATE one for each member
NOTE -- add a comment to the report (in transactions-section)
CNCYOPT -- specify the (RevenueCanada) accounting-method options
SPREAD -- specify the moneychanger-spread on a foreign-currency
XFER -- move a stock from one broker-account to another
See the "Input Summary" at the end of this document, for more details on these statements.
DOING THE TAXES
At the end of the year, you may wish to experiment with a few things to
reduce the amount owing to RevenueCanada. This is where some of the
mysterious statements that you saw in the "Getting Started" section, will
become useful.
The CNCYOPT statement takes two operands; the first is either CAP or INC;
the second is either WITHIN or NOWITHIN.
RevCan allows you a choice of how to account for gains or losses on foreign
currency; and you specify your choice with the CAP/INC operand on CNCYOPT.
Since you are more likely to have losses than gains, you will usually be
better off with the INC option; but you may wish to try the CAP option to
see what happens.
For each foreign-currency, RevCan allows you to use one conversion-factor
for transactions within a period, and another conversion-factor for
evaluations at the end of the period. We will refer to the former as a
"Within-Price" and to the latter as an "Ending-Price".
The program will automatically choose either the lowest or the highest
from all eligible Within-Prices, so as to minimize payments to RevCan.
Any conversion actually realized on a MOVE statement, is a candidate.
The ending-price plus or minus half the Spread are two candidates; and
the beginning-price forms two more such candidates (the previous period's
ending-price serves as the beginning-price).
The Spread is specified on the SPREAD statement. For all this to work,
you need to specify ending-prices as "fair" or "mid-point" prices. As an
example say your usual money-changer is selling US-dollars at 1.49 and is
buying them at 1.47; then the mid-point rate is 1.48, and the spread is
0.02 (for US$'s).
Note that you must specify an ending-price for each foreign currency, for
each period. For the candidate within-prices you can simply rely on
those that program computes, as described above. But if you happen to
have gathered additional candidates, you can tell the program about them.
For example: 99jan PERIOD US$ (1.5123 1.5476) 1.5330
Here you enter a list of within-prices enclosed in parentheses, followed by
the ending-price. You can always reduce a list of within-candidates down
to two by discarding all but the lowest and the highest.
You can specify NOWITHIN on the CNCYOPT statement, to tell the program to
ignore within-prices. In this case the program will behave like the older
version and simply use the ending-price throughout the period. The way the
program works rules out any chance of saving money; but you may be
interested in a comparison, to determine how much you saved due to the
WITHIN-enhancement.
Your beans.asc file may contain more than one of the CNCYOPT or SPREAD
statements. The Spread may be re-specified at any time; however, the
CNCYOPT may only be re-specified in January of each year (since an entire
tax-year must be done with one method).
RUNNING THE PROGRAM
As mentioned earlier, the beans program reads beans.asc, and produces the
report in multiple formats for the most recent period. Now for some ways
to over-ride this behaviour:
beans -i club.asc
beans -i me.dat
These command-lines illustrate running the program with a different
input-file; as you would need to do if you are doing the books for
more than one individual or club. The input-file can have any name you
like; there's no requirement that the name end in dot-asc.
beans -oa
This asks for just rep.asc output. Other choices are -ot for rep.tex,
or -oh for rep.htm, or -od for rep.dat.
The rep.dat output-file is in beans-input-file format. Such
output is useful if you want to "Close the Books" at a yearend, without
going through those painful "Getting Started" calculations all over
again. The program allows multiple years of data in one beans.asc
file; so you need never do such a "book-closing".
Historical note: the default name for the beans-input-file was originally
beans.dat; that suffix became increasingly troublesome over the years
with other software claiming it, so we went to beans.asc, which solved one
problem but left the name of this output-file an unfortunate anomaly.
[Consider: switch to -ob and rep.beans to avoid dot-dat; would also go from
beans.asc to something like records.beans.]
beans -sPBU
To produce a report with Portfolio,Balances,Units sections.
After -s is a string of letters from B/C/c/I/i/J/j/P/p/T/t/U/u/V/v/X/x, where:
P is for Portfolio-section,
T is for transactions,
X is for indexes,
B is for Balances,
I is for Income,
J is for Income-Categorized,
C is for Costbase,
U is for Units,
V is for Units-Next-Period.
The capital-letter sections are for one period; the small-letter variants are for "Year-to-Date" instead.
beans 97jan 97feb
This asks for several reports, from other than the most recent period.
beans 02aug*2
This produces a "July+August" report; for a club that skips a summer
meeting.
beans EACH
This asks for all possible (monthly) reports.
beans 97
beans 98
beans YEAREND
These ask for "Year-End" style reports. When producing a yearend report,
this program also produces a PostScript file for each member. Please
read the file Mbr/00readme, which describes how to print these onto
T5013 forms from RevenueCanada.
Incidentally you can configure the sections you want to see on a "monthly"
as well as on a yearend report, with those two strings of letters on the
CLUB statement. Each is a string of letters, where P is for Portfolio-section,
etc. (Same as for the -s operand explained above.)
Whenever we have said "month" we should have said "period", since you
can have multiple months in your accounting-period; see the CLUB
statement.
beans DUMP
This produces a lot of output, and may be helpful to a programmer who is
debugging changes to this program.
KNOWN BUGS
A bug showed up when the Blue-Sky-Club was winding up. The program is unable
to process MEMDRAW statements when the Club-Unrealized-Gain is zero, as will
happen when the entire portfolio is sold. The workaround is to buy a dummy
stock for a penny say, have it appreciate to two cents; you may also need
a dummy member owning a tiny fraction of a unit.
COMMAND-LINE SUMMARY
beans [-?] [-i infile]... [-o[a][h][t][d]] [-s sections] [date[*nbr]/EACH/YEAREND/DUMP]...
Above is the beans command-line; where square-brackets indicate an
optional-part; slash separates choices; and triple-dot means one-or-more-of.
-? will produce information on using the program.
-e tells the program to "echo" the input; sometimes helpful when debugging.
-g will show the WITHIN-optimization details.
The other operands were explained in "Running the Program".
INPUT SUMMARY
Here is a complete list of possible statements; square-brackets indicate an
optional-part; slash separates choices; triple-dot means one-or-more-of;
<sections> is a string of letters from B/C/c/I/i/J/j/P/p/T/t/U/u/V/v/X/x.
BANK name longname currency [balance [cost]]
BROKER name longname currency [balance [cost]]
CLUB name longname currency fullvote-amt monthsperperiod
sections-normalreport sections-yearendreport
CURRENCY name suffix heading country nationality
INDEX name longname currency
MEMBER name SIN [units cost]
MISCOPT COMBINED/SEPARATE
CNCYOPT CAP/INC WITHIN/NOWITHIN
SPREAD [currency spread]...
STOCK name shortname currency [broker qty cost]
[date] PERIOD [currency/index/stock [(price...)] ending-price[currency]]...
[date] PRICE [currency/index/stock [(price...)] ending-price[currency]]...
date BUY stock [(shortname)] broker qty price brokerage [tax]
date SELL stock [(shortname)] broker qty price brokerage [tax [BIZ]]
date XFER stock from-broker to-broker qty
date CAPDIV stock broker roc-amt [other [cg [tax [foreign]]]]
date DIV stock/reason broker amt [tax]
date EXP reason bank/broker amt [tax]
date INT [(reason)] bank/broker amt [tax]
date MOVE [(reason)] from-bank/broker to-bank/broker from-amt [to-amt]
date DEPOSIT [(reason)] to-bank/broker [from-bank amt]...
date NOTE comment
[date] CLASSA member...
MEETING member expense-amt contrib-amt
MEMBUY member amount
MEMDRAW member amount/ALL/MAX [max-wait-days]