January 22, 2014 at 3:32 pm
Hi All,
I'm working on a project where I have to get prices for a stock, adjusted for any splits that may have occurred. The problem I am having is when multiple splits occur for the same stock.
Test code:
DECLARE @StockPrice TABLE (
Name NVARCHAR(25),
ClosePrice FLOAT,
PriceDate DATE
)
INSERT @StockPrice (Name, ClosePrice, PriceDate)
VALUES ('CompanyA', 81.37, '11/30/2012'),
('CompanyA', 83.57, '12/31/2012'),
('CompanyA', 44.51, '1/31/2013'),
('CompanyA', 48.36, '2/28/2013'),
('CompanyA', 47.73, '3/31/2013'),
('CompanyA', 14.30, '4/30/2013'),
('CompanyA', 12.72, '5/31/2013'),
('CompanyA', 10.41, '6/30/2013'),
('CompanyA', 10.30, '7/31/2013'),
('CompanyA', 8.53, '8/31/2013'),
('CompanyA', 9.96, '9/30/2013'),
('CompanyA', 10.61, '10/31/2013'),
('CompanyA', 10.65, '11/30/2013'),
('CompanyA', 11.34, '12/31/2013')
DECLARE @Splits TABLE (
Name NVARCHAR(25),
ExDate DATE,
ShareIn FLOAT,
ShareOut FLOAT
)
INSERT @Splits (Name, ExDate, ShareIn, ShareOut)
VALUES ('CompanyA', '1/24/2013', 1, 2),
('CompanyA', '4/30/2013', 1, 3)
SELECT 'Before', *
FROM @StockPrice
ORDER BY PriceDate
UPDATE s
SET ClosePrice = ClosePrice * (ShareOut / ShareIn)
FROM @StockPrice s
JOIN @Splits sp
ON s.Name = sp.Name
AND s.PriceDate >= sp.ExDate
SELECT 'After', *
FROM @StockPrice
ORDER BY PriceDate
I want to have prices for Jan 2013 - Mar 2013 multiplied by 2, and prices for April 2013 - Dec 2013 multiplied by 6, but what is happening is that prices for Jan 2013 - Dec 2013 are multiplied by 2. Why isn't the second split processed (or how is it overwritten)?
January 22, 2014 at 4:00 pm
Hi
The culprit would be the join condition in the update clause. This will return both splits for prices past Mar 2013 because of the >= operator. If you change your update to a select you will see these showing up.
You could change your update to something like the following to resolve it
--UPDATE s
-- SET ClosePrice = ClosePrice * (ShareOut / ShareIn)
SELECT *
FROM @StockPrice s
CROSS APPLY (
SELECT TOP 1 ExDate, ShareIn, ShareOut, Name
FROM @Splits ssp
WHERE s.PriceDate >= ssp.ExDate AND s.Name = ssp.Name
ORDER BY ExDate DESC
) sp
January 22, 2014 at 4:00 pm
I can't remember if it's true, or maybe my mind is playing tricks on me, but an update will only update rows once even if a correspondant select would show multiple lines.
Here's a trick that might be adequate to your situation, but I can't claim it will be good enough performance wise.
WITH rowsCTE AS(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Name ORDER BY ExDate) rn
FROM @Splits
),
recCTE AS(
SELECT *
FROM rowsCTE
WHERE rn = 1
UNION ALL
SELECT row.Name, row.ExDate, row.ShareIn * rec.ShareIn, row.ShareOut * rec.ShareOut, row.rn
FROM rowsCTE row
JOIN recCTE rec ON row.Name = rec.Name
AND row.rn = rec.rn + 1
)
UPDATE s
SET ClosePrice = ClosePrice * Share
FROM @StockPrice s
CROSS APPLY( SELECT TOP 1 ShareOut / ShareIn AS Share FROM recCTE sp WHERE s.Name = sp.Name
AND s.PriceDate >= sp.ExDate
ORDER BY sp.ExDate DESC )x
January 22, 2014 at 5:02 pm
Try this:
UPDATE s
SET ClosePrice = ClosePrice * ISNULL(EXP(so) / EXP(si), 1)
FROM @StockPrice s
CROSS APPLY
(
SELECT SUM(LOG(ShareIn)), SUM(LOG(ShareOut))
FROM @Splits sp
WHERE s.Name = sp.Name
AND s.PriceDate >= sp.ExDate
) b (si, so);
Edit: Just make sure that none of your share in/out values are zero because of this:
SELECT LOG(0.)
Msg 3623, Level 16, State 1, Line 53
An invalid floating point operation occurred.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 23, 2014 at 7:18 am
Thanks guys. Cross Apply did the trick. I seem to have a block on that; it just never occurs to me to use Cross Apply.
Cheers,
Tom
January 23, 2014 at 5:02 pm
Tom Bakerman (1/23/2014)
Thanks guys. Cross Apply did the trick. I seem to have a block on that; it just never occurs to me to use Cross Apply.Cheers,
Tom
You might want to test the case where you have a 2 for 3 split. I didn't check it myself, but if it causes you a problem you may need to modify the CA to SUM the LOG of the ratio (instead of each of the parts), and then in the outer query multiply the price by the EXP of the summed logarithmic ratio.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply