Complicated query help

[YST]

Hi all,

I need some help in how to structure the portfolio management database I am constructing.

Essentially, I have an Accounts table, an Equities table, a Transactions table, a Stock Events table and a Currency Exchange table.

The Accounts table holds information regarding an individual investment account.

The Equities table holds information regarding an individual stock. Name, exchange traded, stock symbol, etc.

The Transactions table handles information regarding a transaction that occurs in an individual account. Buy, sell or move a stock, number of shares, account number, stock symbol.

The Stock Events table holds information that happens to a stock in general, such as stock splits, dividend payouts, name changes, mergers, acquisitions, etc.

The currency exchange records the date, foreign currency and exchange rate relative to the CDN dollar.

What I need to do is create a query such that it works it's way through the individual tables and calculates the stock positions held on a specific date in each account.

For example, in Transactions say you have the following:

Acct. 1, Buy 500 shares Nextel on July 10, 03 at $5.00
Acct. 1, Sell 300 shares Nextel on July 20, 03 at $6.00
Acct. 1, Sell 100 shares Nextel on July 31, 03 at $3.25

In Stock Events you have the following:

Nextel 2 for 1 stock split on July 25, 2003.

So, if you were to request an update as of July 21, you would get the following:

Acct. 1, 200 shares Nextel at $5.00 cost, Cash $1800.

An update as of August 1 would produce the following:

Acct. 1, 300 shares Nextel at $2.50 cost, Cash $2125.

If someone could point me in the proper direction of solving this problem, it'd be of great help. I don't specifically need the code, I do know some SQL, but more of a general understanding of how to solve the problem would be much appreciated.

Thanks!

 

[Dwight]

YST,

You should use an update query. Set it so that it will multiply the shares and divide the transaction price by the split ratio on less than or equal to the day the split becomes effective for the matching security.

In your example:

Acct. 1, Buy 500 shares Nextel on July 10, 03 at $5.00
Acct. 1, Sell 300 shares Nextel on July 20, 03 at $6.00
Acct. 1, Sell 100 shares Nextel on July 31, 03 at $3.25

Becomes:

Acct. 1, Buy 1,000 shares Nextel on July 10, 03 at $2.50
Acct. 1, Sell 600 shares Nextel on July 20, 03 at $3.00
Acct. 1, Sell 100 shares Nextel on July 31, 03 at $3.25

You don’t need to adjust the currency table but if you have a table with historical security prices (what if I want to know the value of my position on July 21st and not just the cost?) then you would have to adjust this table as well.

The Help files will show you how to use an update query.

 

[namliam]

In my (humble) oppinion you should do an append query.

In your example you will have 200 shares in stock at the moment of the 2 for 1 split so..

your current stock (200) is split 2 for 1 NOT your entire stock, which was 500, and not your previous sell of 300 stocks

I think you should append a "buy" for the "free" split
Acct. 1, Split 200 shares Nextel on July 25, 03 at $0.00

This will get what you want cause you now have
200 at 5.00
200 at 0.00

giving 400 at 2.5
after
Acct. 1, Sell 100 shares Nextel on July 31, 03 at $3.25
you will have:
Acct. 1, 300 shares Nextel at $2.50 cost, Cash $2125.

Regards

The Mailman

 

[YST]

Thanks for the good advice.

I will try it first using an Append query and if I am not satisfied with the results, will switch to an update query.

However, there are still a couple of things I am confused about.

I have the Append query working to a degree, however, how do I set the cost of shares to 0 on the append query.

Second, how do I get this to run automatically whenever a new Stock Split is entered into the Stock Events table.

Third, how exactly do I sum the buys and sells in a querry?

Thanks!

Rob

 

[namliam]

Third, how exactly do I sum the buys and sells in a querry?

using a group by / sum query

Second, how do I get this to run automatically whenever a new Stock Split is entered into the Stock Events table.

Not sure what you mean but you can run the query "at the push of a button" or from VBA checks, anyway a number of ways depending on what it is you want to do... and when you want to do it..

how do I set the cost of shares to 0 on the append query.

Easy enough just TYPE 0 into the rowsource (QBE) and add that to the column CostOfShare

Maybe you can post a sample of your (empty or test data) DB and i or we will certainly help you figure it out...

Regards