Running Products in Queries - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Running Products in Queries

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-07-11 : 09:27:34

writes "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 CumulativePositions
([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"

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2002-07-11 : 10:37:22

Without sample data I don't easily have the facilities to verify this, but try:


update CumulativePositions
set cumposition = d.position
from CumulativePositions c
	inner join (
		select t.tickerID, t.tradedate, sum(qty * isnull(splitratio,1.0) as position)
		from trades t
			left join splits s on ( t.tickerID = s.tickerID and t.tradedate < s.splitdate )
		group by t.tickerID, t.tradedate ) d on ( c.tickerID = d.tickerID and c.positiondate = d.tradedate )

Jonathan Boott, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources