Documentation

Here is a table organizational chart showing which tables are meant to contain user data, and how these tables are related. In the create_main.sql file you will find the comments that explain the human-readable meaning of individual column names.

Important Notes

If you delete an entry in the fund table, all corresponding entries in all tables are deleted. Be careful!
The total market value in the summary_fund table is calculated for each fund on the date of the most recent entry for that fund in the share_price table. This usually corresponds to the last date shares of a fund were purchased or redeemed. Since the total market value of all funds is used to calculate the portfolio allocation, if you want an accurate portfolio allocation table you will have to enter price data into the share_price table for every fund on a specific recent date (usually the last day of the year).
Keep your financial data on a computer which has no internet connection. Note that it is possible to write viruses that send to the virus author the keystrokes that you type (your password, for example) to connect to your on-line bank or broker. Although the internet connection is encrypted, a virus sitting on your own computer can record the keystrokes that you make, before encrypion.
Compile your programs from human readable source code instead of downloading executables. It is easy to hide bad intentions in executable files. It is harder to do this in code which is obtained in source form. SequelBasis is a short program provided in easy-to-read SQL code. I encourage you to take a look, makes improvements, find bugs, etc.

Data Update

At the moment, the summary,summary_fund, and summary_fund_income tables do not get updated automatically when you change any of the user data tables. The report.sql script, however, runs the commands necessary to update these tables. You can also update the tables manually with the following psql code:

SELECT update_summary();
SELECT update_summary_fund();
SELECT update_summary_fund_income();

A full-update shortcut, which results in these three commands being executed, is:

SELECT * FROM update_all;

Undistributed Income

The distributed flag in the fund_income and reinvested_fund_income tables should be true for almost all U.S. fund income. This is not meant to indicate whether distributed income is reinvested or not. To indicate that distributed fund income is reinvested, enter it into the reinvested_fund_income table. To indicate that distributed fund income is not reinvested, enter it into the fund_income table. The only case where the distributed flag should be false is when fund income should be treated, for instance, as an Undistributed Capital Gain. This occurs rarely with U.S. funds, but may occur more often with offshore funds. When this is the case, fund income results in an increase of the cost basis. Example: many swiss funds do not make any distributions. Nevertheless, they generate income that is taxable by the swiss government. If a person subject to U.S. taxation as well as swiss taxation wishes to avoid double taxation, he/she should opt for the Foreign Tax Credit (I think. I am no tax expert) when considering this income. In this case, since this income is taxed but no distributions are made, I believe it makes sense for the taxpayer to increase the cost basis of the shares that generated this income, like for an Undistributed Capital Gain as recognized by U.S. law, by setting the distributed flag to false.

Inheritance

PostgreSQL allows for inherited tables. As you see on the chart, inheritance is used a lot (solid lines with arrows indicate parent/child relationship). For instance, since share_purchase and share_redemption are children of share_price, every time you add entries to share_purchase or share_redemption, data also becomes available in the share_price table. I can look at the share_purchase table to see when I purchased shares. But if I am simply interested in seeing, for instance, share price as a function of time, I can query the share_price table directly. I automatically see entries in the share_price table for every entry in share_purchase or share_redemption. And if I wish to add entries only to the share_price table, this is also possible. This is useful, for instance, when I look at the summary_fund_cv view. This view lets me see the net value of a fund on the last transaction date. But what if I haven't made any transactions for a long time. I still might like to know, based on today's share price, the value of this fund. To do this, I simply add an entry to the share_price table. Now, if I look at the summary_fund_cv view, I will get the net value based on today's share price after running update_summary_fund().

Derived funds

It is possible to copy a fund by typing the SQL SELECT fund_copy(fund_id). This causes data for a new fund to be placed in the database. The new fund is identical to the old fund, except that it has a different fund_id, and its derived entry points to the fund_id of the original fund. The derived entry is useful so that you know which funds contain real hand-entered data, and which funds were derived by automatic calculations, and can be thrown out. If you use fund_copy to copy a fund, and then you decide that you want to keep the copied fund for good, it would be a good idea to set the derived entry for the fund to NULL. This way, you won't accidentally delete the fund.

Fund currency conversion

US Tax laws require U.S. taxpayers with foreign denominated funds to convert every transaction into U.S. dollars on the date of the transaction. This is a pain to do by hand, but computers can do it easily. The command

select fund_gen_in_ref_curr('USD')

creates new derived funds in the database that have all values converted according to the appropriate daily exchange rate (if the exchange_rate table is up-to-date). This data is easily downloaded from the federal reserve web site, and the included fed2sql.pl script converts it to SQL files which when run will insert the data into the exchange_rate table. You can also use the fund_new_curr(fund_id,new_currency) command to create a single fund in a new currency. fund_new_currency(...) runs fund_copy(...), and then updates the price entries of the data tables appropriately.

Once the derived funds are created, they can be used just like any other funds. The original currency funds are still available if you have to make calculations in the fund currency. The new currency funds are not kept synchronized with the original funds. If you wish to update them, first delete the derived funds and then recreate them.