Investment DB Design Help

[jnoonan22]

New to the forum, and did a quick search to make sure I wasn't asking a question on something that's already been answered and didn't see anything, so here goes...



I have some simple DB building exp, and have an idea of putting together an investment tracking DB. My primary concern at the time has to do with cost tracking of investments. Many of you might be familiar with what I'm talking about, but in case you don't, I'm referring to tracking the cost basis of investments using two different techniques: First-In-First-Out (FIFO) and Weighted Average Cost (WAC).

I'd love to hear any ideas or opinions for efficiently tracking WAC, but mostly I need help with tracking FIFO. Say i record 3 separate purchases of shares in Apple, Inc. (AAPL) on three separate dates: 1/5/11 buy 50 shares @ $300/sh, 6/1/11 buy 100 shares @ $350/sh, and 11/24/11 buy 50 shares @ $400/sh. After my third purchase, my two cost basis are the same, $70,000. WAC says I have 200 shares at total cost of $70,000, which is 200 shares with an avg of $350/sh.

The trick comes when selling. If on 12/12/11, I sell 125 shares for $425/sh, my cost basis for those 125 shares using WAC is simply 125 x $350 = $43,750. However, using FIFO, my cost basis is only $23,750 (The first 50 shares @ 300/sh = 15,000 plus 25 shares of the 100 bought @ 350/sh = 8,750).

So when using FIFO, I basically need to be able to create "bins" to store each individual purchase of shares, and then, when selling, have the DB know to draw shares from the first bin created at one cost and then, when depleted, draw from the next available at the next cost, and so on. Is there a special set of tools in Access for this type of data tracking?

In setting up my DB, I have a handful of tables currently. This first is a table of companies who are buying and selling investments. Another table contains investment info such as symbol, type (stock, fund, etc), name, rating, and so forth. Then I have transaction table, which requires a company, an investment, a date, transaction type, shares, total cost, etc. Now, I've thought about maybe having two separate transaction tables, one for purchases, and one for sales. Would this make more sense?

Honestly, any help or guidance is appreciated. Thanks.

[Rainlover]

I would suggest that you post a copy of your database so we can gain a better insight.

When attaching a Database please convert to 2003 as not all people have the latest version.