July 8, 2002 at 3:55 pm
I'm building a database to track stock trades and have run into a problem with splits. The problem comes when I try to create running sums of the stocks traded adjusted by running products of splits which have occurred. For various reasons, I can't do this in VB (where it would be pretty easily solved); ideally, I'd love to solve this without a cursor.
I think it's pretty tricky, but take a look - hopefully you can figure out a clever solution, because I'm stuck! 😉
The scenario:
We have four input tables:
1. Trades: contains the stock trades. The quantity traded, Qty, is always positive. TradeActionID links to the TradeActions table to tell us if the multiplier for the TradeActionID is buy(1) or sell(-1).
2. TradeActions: just a quick lookup table to tell us whether to multiply the quantity of stock traded by 1or -1. (for some reason, there are lots of TradeActionID, but they the only TradeActionMultiplier is 1 or -1).
3. Splits: the date and split ratio of stock splits. For example, if ticker 37 split 4-for-1 on July 1, 2002, then the Splits table would have an entry
of (37, 7/1/02, 4).
4. ListDates: simply a list of all dates for which we want results. (Usually set to be consecutive dates over the last few years).
create table Trades
([TickerID] int not null, [TradeDate] datetime not null, [TradeActionID] int not null, [Qty] int not null)
create table TradeActions
([TradeActionID] int not null, [TradeActionMultiplier] int not null)
create table Splits
([TickerID] int not null, [SplitDate] datetime not null, [SplitRatio] float not null)
create table ListDates
([ListDate] datetime not null)
The goal:
I'm trying to create a table CumPositions which contains the cumulative position in each TickerID on each ListDate in which the cumulative position is not 0.
create table CumPositions
([TickerID] int not null, [PositionDate] datetime not null, [CumPosition] float not null)
It wouldn't be too bad if it weren't for the splits: just select the Sum of the Qty * TradeActionMultiplier, with a right join to ListDates on Trades.TradeDate <= ListDates.ListDate.
But here's the catch:
If we had 10,000 shares of a ticker yesterday and a split of 4 occurs today, then we now have 40,000 shares. The 40,000 shares is the cumulative share position I'd like to return for today.
The problem comes in that one given TickerID might have several different stock splits over the last few years, making the summing of the Qty traded impossible.
I can calculate the sumproduct of the splits by using natural logarithms. The query below will tell me the split multiple for shares traded on any given @tradedate when evaluating it on @evaldate:
select round(exp(sum(log(s1.splitratio))),6)
from splits s1
where tickerid = @ticker
and @tradedate < s1.splitdate
and @evaldate >= s1.splitdate
(Note the round is just to clean up the floating result from the equation inside).
And that's as far as I can get. I can't figure out a way to actually sum up the shares into a cumulative position. When I try to put the natural log query above into a right join with ListDates, I end up with garbage.
I'm stumped. Any clever ideas out there?
Thanks!
Lupe
July 9, 2002 at 10:06 am
Your issue might be your join to list dates... It's hard to join based on dates... even more so if your doing it with a right outer join and a less than or equals operator.
The database doesn't seem to be designed for what you are trying to perform, what stage of development is this application in?
Anyways, you might want to consider using a date range for you join instead of a single date... Then you could join on Trades.TradeDate between ListDates.BeginDate and ListDates.EndDate.
Once it is easier to join against you'll probably be able to handle this with two statements, one for calculating dates that do not contain a split, the other for calculating dates that do contain a split.
July 10, 2002 at 3:32 am
Not sure this is a solution for your problem, but I'll give it a go anyway.
You could try to convert the quantities in the Trades table to the quantity taking the splits into account. You already have the logarithm formula for that. You will probably have to do this for all the dates in your ListDates table (if I understand the final goal correctly).
If you put these values in a temporary table, the second step would be to use the 'not too bad' query you mentioned yourself on this table. This way, you don't have to worry about the splits anymore.
I hope this gives you some ideas to solve your problem.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply