SequelBasis Introduction
SequelBasis is a free
(GPL-licensed)
cost basis and capital gain calculator for mutual funds. Cost Basis
and capital gain are calculated using both the
FIFO and Average Cost Basis Single Category methods.
Entering mutual
fund data and generating comprehensive reports is simple. U.S. citizens
living abroad, and other U.S. taxpayers who own mutual funds denominated
in a foreign currency, will appreciate the ease with which SequelBasis can convert
all prices into the dollar reference currency for every purchase or redemption
using the appropriate daily foreign exchange rate. Users with other
reference currencies may also appreciate the flexibility and simplicity of
SequelBasis.
To operate, SequelBasis requires
PostgreSQL
(a free multi-platform relational database
management system that is easy to install). Sequelbasis has been tested on UNIX and
GNU/Linux machines, but it should work on other
operating systems as well.
The conversion of the Federal Reserve foreign exchange
data (for users with mutual funds which are not denominated in
their reference currency), and customization of the pivot table views
with SequelPivot, are both done in
the Perl language, which is also available
on many platforms.
Features
- Straightforward data entry
- Report generation presenting FIFO cost basis, average cost
basis, capital gain, total number of shares, total market value,
and portfolio allocation.
- Report generation can be extended with SQL commands, or with
SequelPivot,
a pivot-table view generator.
- When shares are sold, purchases are automatically 'split' into smaller
chunks if necessary.
- To calculate your cost basis, SequelBasis takes into consideration
the following basis adjustments:
- Purchase Fees
- Redemption Fees
- Undistributed Capital Gain
- Return of Capital (Nontaxable Distributions)
- Fund data is stored in a group of simple, easy to manage tables. These
tables, as well as report tables, can be exported in CSV format to a variety
of other programs. Data for different funds is kept in the same tables,
identified by a different fund ID.
- Funds in different currencies are managed easily.
- A fund in one currency can be copied and converted to a fund in another
currency. All prices are converted using the daily exchange rate that
was current on the transaction date, like tax laws of some countries may require.
- The exchange rate historical data can be entered by hand, or it can
be downloaded from the
U.S. Federal Reserve web site and converted automatically.
- SequelBasis can easily be extended to perform other calculations,
if you know SQL.
Why not a spreadsheet or tax preparation software
Doing these kinds of calculations automatically with a simple and easily
maintained spreadsheet does not appear to be feasible. Doing this in SQL, however,
means that it is possible to improve report generation without changing the
fundamental data structures that are used to store data. So if I improve
SequelBasis, old data can be presented in new ways, with little effort. With
spreadsheets, if improvements are made to a report, it is necessary to manually
update every old spreadsheet by hand to use the improvements.
Tax preparation software is not made for users which must declare taxes to
two different governments (like all U.S. Americans living abroad).
Most tax preparation software is proprietary,
and the storage of your data is in a format that is not easily transferred
to other software unless the vendors of the software happen to decide to
write converters.
Disclaimer
All comments, examples, and data provided with this program, as well
as the program itself, were written as a programming exercise and are
not meant to accurately represent tax laws of any country. Data
generated by this program is not guaranteed to be accurate and should
not be used to file your tax return or make financial decisions.
See a financial advisor to help you file your tax return and
make financial decisions.
These programs are free software; you can redistribute them and/or modify
them 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.
These programs are distributed in the hope that they will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
for more details.
You should have received a copy of the GNU General Public License
along with these programs; if not, write to the Free Software Foundation, Inc.,
59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
Example
Here are
results
generated by SequelBasis for this
simple example source code.
Notice in the report how the purchased shares were split up
automatically.
The table names may seem cryptic. The _cv
suffix means that what is displayed is not the original table, but
a VIEW of the table that has more readable column names than the
original table. The _cjv suffix means that, in addition
to human-readable column names, the view is joined with the
fund table so that fund abbreviations and currencies are
displayed. The _pcjv suffix means that what is displayed
is a pivot table view containing aggregate data like sums of
the data in multiple rows.
For help interpreting the pivot table results (views
that end with pcjv), see the
SequelPivot
documentation.
Here are
results
generated by SequelBasis for this
example source code which solves some example problems
that are found in IRS Publication 564.
Here are
results
for an
example
of a fund denominated in Euros. Notice how the capital gain of the
fund denominated in Euros is positive, but in dollars the fund has a
capital loss due to the depreciation of the Euro. Note in
summary_cjv how the basis is adjusted by purchase and redemption
fees, as well as the undistributed income, which is being treated
like an Undistributed Capital Gain. I do not know if this is in
accordance to IRS rules (see the documentation for an explanation).
The redemption fee also increases the cost basis of the shares when
they are sold. If you prefer, set s_adj to be true in the fund table
so that the sale fee is used as an adjustment of the sale price
instead of the cost basis. The way you choose to do this depends on
which value (if any) is reported to the IRS by the bank. See IRS
Publication 564.
Documentation
Here is an explanation of how to
install SequelBasis and generate an example report.
Here is some incomplete documentation.
Download
SequelBasis is copyrighted (2002, 2003, 2004), David
Hoover.
SequelPivot is copyrighted (2002, 2003, 2004), David
Hoover.
These programs are free software; you can redistribute them and/or modify
them 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.
These programs are distributed in the hope that they will be useful, but
WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY
or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License
for more details.
You should have received a copy of the GNU General Public License
along with these programs; if not, write to the Free Software Foundation, Inc.,
59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
Click here to download SequelBasis and
SequelPivot in tar format.
Other Links
SequelBasis is a
project. Here is the
development
page.
LinuxLinks
and
Christopher Browne
have some links to other financial software for GNU/Linux.