Exponential Moving Average Challenge

Question

Exponential Moving Average Challenge

Wayne-950947

Ten Centuries

Points: 1122
More actions
January 30, 2009 at 8:56 pm

#134301

Hi Folks
The challenge I'm having here is trying to derive the EMA for the current day's record where the previous day's EMA for the same futures contract is an input. Similarly the next trading day's EMA is dependent on the EMA for today. Using a set-based SQL approach does not cascade the results in the desired fashion.
Here are the details, and with reference to the sample data below:
EMA formula is: EMA_Current = EMA_Previous + (CLS_Current - EMA_Previous) * Constant
Where Constant in my example is 0.15385
Note: The PREV_TIME_KEY is the previous trading day, and it's not necessarily TIME_KEY - 1 as there are gaps due to non-business days. Also COMM_SYMB are different commodities, so the previous record is not necessarily the previous trading day for the current commodity.
The data begins with a seed value, see records where TIME_KEY = 718.
Then to calculate the EMA for records where TIME_KEY = 719, we apply the formula. Taking COMM_SYMB = C as an example we get 371.290064102564 + (372.75 - 371.290064102564) * 0.15385 = 371.51468
Using a SQL set approach, I'm able to derive the next value only (i.e. where TIME_KEY = 719), but thereafter all subsequent records have EMA_F = NULL. I understand why, because my derived values have not been written to the table. So I need some method to derive the next day's EMA, then write the results to the table, then derive the following day's EMA, write to the table, etc... This to be repeated for all 30,000 records in the master table.
Thanks in advance,
Wayne
-- Sample data
DROP TABLE dbo.EMA_TEST
CREATE TABLE dbo.EMA_TEST(
TIME_KEY INT NOT NULL,
COMM_SYMB NVARCHAR(3) NOT NULL,
PREV_TIME_KEY INT,
CLS FLOAT,
EMA_F FLOAT)
INSERT INTO dbo.EMA_TEST(TIME_KEY, COMM_SYMB, PREV_TIME_KEY, CLS, EMA_F)
SELECT 703, 'C', 0, 375.5, 0 UNION ALL
SELECT 703, 'CL', 0, 62.44, 0 UNION ALL
SELECT 703, 'GC', 0, 650.9, 0 UNION ALL
SELECT 704, 'C', 703, 378, 0 UNION ALL
SELECT 704, 'CL', 703, 62.43, 0 UNION ALL
SELECT 704, 'GC', 703, 647.9, 0 UNION ALL
SELECT 705, 'C', 704, 366, 0 UNION ALL
SELECT 705, 'CL', 704, 62.19, 0 UNION ALL
SELECT 705, 'GC', 704, 635.9, 0 UNION ALL
SELECT 706, 'C', 705, 372.5, 0 UNION ALL
SELECT 706, 'CL', 705, 62.49, 0 UNION ALL
SELECT 706, 'GC', 705, 637, 0 UNION ALL
SELECT 707, 'C', 706, 368.5, 0 UNION ALL
SELECT 707, 'CL', 706, 62.03, 0 UNION ALL
SELECT 707, 'GC', 706, 631, 0 UNION ALL
SELECT 710, 'C', 707, 370.75, 0 UNION ALL
SELECT 710, 'CL', 707, 61.22, 0 UNION ALL
SELECT 710, 'GC', 707, 634.8, 0 UNION ALL
SELECT 711, 'C', 710, 372.75, 0 UNION ALL
SELECT 711, 'CL', 710, 61.02, 0 UNION ALL
SELECT 711, 'GC', 710, 631.7, 0 UNION ALL
SELECT 712, 'C', 711, 368.25, 0 UNION ALL
SELECT 712, 'CL', 711, 62.17, 0 UNION ALL
SELECT 712, 'GC', 711, 632.4, 0 UNION ALL
SELECT 713, 'C', 712, 371.75, 0 UNION ALL
SELECT 713, 'CL', 712, 63.33, 0 UNION ALL
SELECT 713, 'GC', 712, 630.9, 0 UNION ALL
SELECT 714, 'C', 713, 369, 0 UNION ALL
SELECT 714, 'CL', 713, 64.09, 0 UNION ALL
SELECT 714, 'GC', 713, 619.1, 0 UNION ALL
SELECT 717, 'C', 714, 365.75, 0 UNION ALL
SELECT 717, 'CL', 714, 62.79, 0 UNION ALL
SELECT 717, 'GC', 714, 617.9, 0 UNION ALL
SELECT 718, 'C', 717, 373, 371.290064102564 UNION ALL
SELECT 718, 'CL', 717, 63.46, 62.6237179487179 UNION ALL
SELECT 718, 'GC', 717, 625.4, 631.753205128205 UNION ALL
SELECT 719, 'C', 718, 372.75, NULL UNION ALL
SELECT 719, 'CL', 718, 63.72, NULL UNION ALL
SELECT 719, 'GC', 718, 624.3, NULL UNION ALL
SELECT 720, 'C', 719, 377.75, NULL UNION ALL
SELECT 720, 'CL', 719, 62.66, NULL UNION ALL
SELECT 720, 'GC', 719, 621.6, NULL UNION ALL
SELECT 721, 'C', 720, 384, NULL UNION ALL
SELECT 721, 'CL', 720, 62.41, NULL UNION ALL
SELECT 721, 'GC', 720, 622.3, NULL UNION ALL
SELECT 724, 'C', 721, 384, NULL UNION ALL
SELECT 724, 'CL', 721, 62.41, NULL UNION ALL
SELECT 724, 'GC', 721, 622.3, NULL UNION ALL
SELECT 725, 'C', 724, 388, NULL UNION ALL
SELECT 725, 'CL', 724, 61.1, NULL UNION ALL
SELECT 725, 'GC', 724, 626.9, NULL UNION ALL
SELECT 726, 'C', 725, 386.75, NULL UNION ALL
SELECT 726, 'CL', 725, 60.34, NULL UNION ALL
SELECT 726, 'GC', 725, 630.3, NULL UNION ALL
SELECT 727, 'C', 726, 388.5, NULL UNION ALL
SELECT 727, 'CL', 726, 60.53, NULL UNION ALL
SELECT 727, 'GC', 726, 636.9, NULL UNION ALL
SELECT 728, 'C', 727, 390.25, NULL UNION ALL
SELECT 728, 'CL', 727, 61.05, NULL UNION ALL
SELECT 728, 'GC', 727, 638, NULL
-- The expected results
DROP TABLE dbo.EMA_ER
CREATE TABLE dbo.EMA_ER(
TIME_KEY INT NOT NULL,
COMM_SYMB NVARCHAR(3) NOT NULL,
PREV_TIME_KEY INT,
CLS FLOAT,
EMA_F FLOAT)
INSERT INTO dbo.EMA_ER(TIME_KEY, COMM_SYMB, PREV_TIME_KEY, CLS, EMA_F)
SELECT 703, 'C', 0, 375.5, 0 UNION ALL
SELECT 703, 'CL', 0, 62.44, 0 UNION ALL
SELECT 703, 'GC', 0, 650.9, 0 UNION ALL
SELECT 704, 'C', 703, 378, 0 UNION ALL
SELECT 704, 'CL', 703, 62.43, 0 UNION ALL
SELECT 704, 'GC', 703, 647.9, 0 UNION ALL
SELECT 705, 'C', 704, 366, 0 UNION ALL
SELECT 705, 'CL', 704, 62.19, 0 UNION ALL
SELECT 705, 'GC', 704, 635.9, 0 UNION ALL
SELECT 706, 'C', 705, 372.5, 0 UNION ALL
SELECT 706, 'CL', 705, 62.49, 0 UNION ALL
SELECT 706, 'GC', 705, 637, 0 UNION ALL
SELECT 707, 'C', 706, 368.5, 0 UNION ALL
SELECT 707, 'CL', 706, 62.03, 0 UNION ALL
SELECT 707, 'GC', 706, 631, 0 UNION ALL
SELECT 710, 'C', 707, 370.75, 0 UNION ALL
SELECT 710, 'CL', 707, 61.22, 0 UNION ALL
SELECT 710, 'GC', 707, 634.8, 0 UNION ALL
SELECT 711, 'C', 710, 372.75, 0 UNION ALL
SELECT 711, 'CL', 710, 61.02, 0 UNION ALL
SELECT 711, 'GC', 710, 631.7, 0 UNION ALL
SELECT 712, 'C', 711, 368.25, 0 UNION ALL
SELECT 712, 'CL', 711, 62.17, 0 UNION ALL
SELECT 712, 'GC', 711, 632.4, 0 UNION ALL
SELECT 713, 'C', 712, 371.75, 0 UNION ALL
SELECT 713, 'CL', 712, 63.33, 0 UNION ALL
SELECT 713, 'GC', 712, 630.9, 0 UNION ALL
SELECT 714, 'C', 713, 369, 0 UNION ALL
SELECT 714, 'CL', 713, 64.09, 0 UNION ALL
SELECT 714, 'GC', 713, 619.1, 0 UNION ALL
SELECT 717, 'C', 714, 365.75, 0 UNION ALL
SELECT 717, 'CL', 714, 62.79, 0 UNION ALL
SELECT 717, 'GC', 714, 617.9, 0 UNION ALL
SELECT 718, 'C', 717, 373, 371.290064102564 UNION ALL
SELECT 718, 'CL', 717, 63.46, 62.6237179487179 UNION ALL
SELECT 718, 'GC', 717, 625.4, 631.753205128205 UNION ALL
SELECT 719, 'C', 718, 372.75, 371.514675240385 UNION ALL
SELECT 719, 'CL', 718, 63.72, 62.7923809423077 UNION ALL
SELECT 719, 'GC', 718, 624.3, 630.606529519231 UNION ALL
SELECT 720, 'C', 719, 377.75, 372.473979954651 UNION ALL
SELECT 720, 'CL', 719, 62.66, 62.7720141343336 UNION ALL
SELECT 720, 'GC', 719, 621.6, 629.220874952697 UNION ALL
SELECT 721, 'C', 720, 384, 374.247258138628 UNION ALL
SELECT 721, 'CL', 720, 62.41, 62.7163182597664 UNION ALL
SELECT 721, 'GC', 720, 622.3, 628.156098341225 UNION ALL
SELECT 724, 'C', 721, 384, 375.747717474 UNION ALL
SELECT 724, 'CL', 721, 62.41, 62.6691911955013 UNION ALL
SELECT 724, 'GC', 721, 622.3, 627.255137611427 UNION ALL
SELECT 725, 'C', 724, 388, 377.632731140625 UNION ALL
SELECT 725, 'CL', 724, 61.1, 62.4277711300734 UNION ALL
SELECT 725, 'GC', 724, 626.9, 627.200499689909 UNION ALL
SELECT 726, 'C', 725, 386.75, 379.03542295464 UNION ALL
SELECT 726, 'CL', 725, 60.34, 62.1065675417116 UNION ALL
SELECT 726, 'GC', 725, 630.3, 627.677357812617 UNION ALL
SELECT 727, 'C', 726, 388.5, 380.491548133069 UNION ALL
SELECT 727, 'CL', 726, 60.53, 61.8640126254193 UNION ALL
SELECT 727, 'GC', 726, 636.9, 629.096261313146 UNION ALL
SELECT 728, 'C', 727, 390.25, 381.992885952796 UNION ALL
SELECT 728, 'CL', 727, 61.05, 61.7387767829985 UNION ALL
SELECT 728, 'GC', 727, 638, 630.466101510118

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Garadin One Orange Chip Points: 29613 More actions · Answer 1

First, let me say, good post. Sample data, table definitions,expected results and a clear description of the problem.

This should work for you. Please note that the clustered index is required. The logic this uses is explained in detail in the running totals article referenced in my signature.

DROP TABLE EMA_Test

CREATE TABLE dbo.EMA_TEST(

TIME_KEY INT NOT NULL,

COMM_SYMB NVARCHAR(3) NOT NULL,

PREV_TIME_KEY INT,

CLS FLOAT,

PRIMARY KEY CLUSTERED(COMM_SYMB, Time_Key), -- PART OF SOLUTION

EMA_F FLOAT,

RT FLOAT)

INSERT INTO dbo.EMA_TEST(TIME_KEY, COMM_SYMB, PREV_TIME_KEY, CLS, EMA_F)

SELECT 703, 'C', 0, 375.5, 0 UNION ALL

SELECT 703, 'CL', 0, 62.44, 0 UNION ALL

SELECT 703, 'GC', 0, 650.9, 0 UNION ALL

SELECT 704, 'C', 703, 378, 0 UNION ALL

SELECT 704, 'CL', 703, 62.43, 0 UNION ALL

SELECT 704, 'GC', 703, 647.9, 0 UNION ALL

SELECT 705, 'C', 704, 366, 0 UNION ALL

SELECT 705, 'CL', 704, 62.19, 0 UNION ALL

SELECT 705, 'GC', 704, 635.9, 0 UNION ALL

SELECT 706, 'C', 705, 372.5, 0 UNION ALL

SELECT 706, 'CL', 705, 62.49, 0 UNION ALL

SELECT 706, 'GC', 705, 637, 0 UNION ALL

SELECT 707, 'C', 706, 368.5, 0 UNION ALL

SELECT 707, 'CL', 706, 62.03, 0 UNION ALL

SELECT 707, 'GC', 706, 631, 0 UNION ALL

SELECT 710, 'C', 707, 370.75, 0 UNION ALL

SELECT 710, 'CL', 707, 61.22, 0 UNION ALL

SELECT 710, 'GC', 707, 634.8, 0 UNION ALL

SELECT 711, 'C', 710, 372.75, 0 UNION ALL

SELECT 711, 'CL', 710, 61.02, 0 UNION ALL

SELECT 711, 'GC', 710, 631.7, 0 UNION ALL

SELECT 712, 'C', 711, 368.25, 0 UNION ALL

SELECT 712, 'CL', 711, 62.17, 0 UNION ALL

SELECT 712, 'GC', 711, 632.4, 0 UNION ALL

SELECT 713, 'C', 712, 371.75, 0 UNION ALL

SELECT 713, 'CL', 712, 63.33, 0 UNION ALL

SELECT 713, 'GC', 712, 630.9, 0 UNION ALL

SELECT 714, 'C', 713, 369, 0 UNION ALL

SELECT 714, 'CL', 713, 64.09, 0 UNION ALL

SELECT 714, 'GC', 713, 619.1, 0 UNION ALL

SELECT 717, 'C', 714, 365.75, 0 UNION ALL

SELECT 717, 'CL', 714, 62.79, 0 UNION ALL

SELECT 717, 'GC', 714, 617.9, 0 UNION ALL

SELECT 718, 'C', 717, 373, 371.290064102564 UNION ALL

SELECT 718, 'CL', 717, 63.46, 62.6237179487179 UNION ALL

SELECT 718, 'GC', 717, 625.4, 631.753205128205 UNION ALL

SELECT 719, 'C', 718, 372.75, NULL UNION ALL

SELECT 719, 'CL', 718, 63.72, NULL UNION ALL

SELECT 719, 'GC', 718, 624.3, NULL UNION ALL

SELECT 720, 'C', 719, 377.75, NULL UNION ALL

SELECT 720, 'CL', 719, 62.66, NULL UNION ALL

SELECT 720, 'GC', 719, 621.6, NULL UNION ALL

SELECT 721, 'C', 720, 384, NULL UNION ALL

SELECT 721, 'CL', 720, 62.41, NULL UNION ALL

SELECT 721, 'GC', 720, 622.3, NULL UNION ALL

SELECT 724, 'C', 721, 384, NULL UNION ALL

SELECT 724, 'CL', 721, 62.41, NULL UNION ALL

SELECT 724, 'GC', 721, 622.3, NULL UNION ALL

SELECT 725, 'C', 724, 388, NULL UNION ALL

SELECT 725, 'CL', 724, 61.1, NULL UNION ALL

SELECT 725, 'GC', 724, 626.9, NULL UNION ALL

SELECT 726, 'C', 725, 386.75, NULL UNION ALL

SELECT 726, 'CL', 725, 60.34, NULL UNION ALL

SELECT 726, 'GC', 725, 630.3, NULL UNION ALL

SELECT 727, 'C', 726, 388.5, NULL UNION ALL

SELECT 727, 'CL', 726, 60.53, NULL UNION ALL

SELECT 727, 'GC', 726, 636.9, NULL UNION ALL

SELECT 728, 'C', 727, 390.25, NULL UNION ALL

SELECT 728, 'CL', 727, 61.05, NULL UNION ALL

SELECT 728, 'GC', 727, 638, NULL

DECLARE @PrevCSnvarchar(3),

@PrevTK int,

@rtfloat

UPDATE EMA_TEST

SET @rt = RT = CASE WHEN Comm_symb = @PrevCS AND @rt <> 0 THEN @rt + (CLS - @rt) * 0.15385

ELSE EMA_F

END,

@PrevCS = Comm_Symb,

@PrevTK = Time_Key

FROM ema_test WITH (INDEX(0)) -- IMPORTANT!

SELECT * FROM ema_test

order by time_key, comm_symb

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Amazing how many problems seem to be solved using the running totals approach at times. 🙂

steve dassin SSCrazy Points: 2396 More actions · Answer 3

Lynn Pettis (1/30/2009)
Amazing how many problems seem to be solved using the running totals approach at times. 🙂

Yep.

This was recognized a long time ago by the major sql database venders in the form of OLAP, analytic window functions (ie. row_number()over..etc). And to keep their customers from going to other types of software. Their main concern was the solutions must be relatively 'easy'. (Note they had the foresight to see that these functions might conceptually confuse users. But that's another topic for another time:)

Consider the following from an sql-99 standard document:

ISO/IEC JTC1/ SC32 WG3:YGJ-068

ANSI NCITS H2-99-154r2

'Introduction to OLAP functions'

May 5, 1999

http://www.cse.iitb.ac.in/dbms/Data/Papers-Other/SQL1999/OLAP-99-154r2.pdf

"Every standard must be prepared to tackle new issues that arise as the market evolves. Sometimes this will require breaking the mold. Today, the SQL community is challenged by the emergence of OLAP, with its distinctive requirements. If SQL does not respond positively to this challenge, SQL risks becoming irrelevant to an important and growing segment of the database industry."

Unfortunately MS does not share the sql view of the utility of OLAP. If they

did they would have implemented all of it by now:) Of course RAC was intended to pick up some of the slack:)

www.beyondsql.blogspot.com

Wayne-950947 Ten Centuries Points: 1122 More actions · Answer 4

Many thanks Seth, much appreciated!

I'll be doing some reading on the running totals...

Wayne