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.
- 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.
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:
A full-update shortcut, which results in these three commands being executed, is:
SELECT * FROM update_all;
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.
PostgreSQL allows for inherited tables. As you see on the
inheritance is used a lot (solid lines with arrows indicate
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().
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
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