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.