Convert VB Simple Moving Average Function to T-SQL

Question

Convert VB Simple Moving Average Function to T-SQL

Wayne-950947

Ten Centuries

Points: 1122
More actions
December 7, 2008 at 9:05 pm

#202479

Hi
I'm in the process of migrating a MS-Access based trading system to SQL Server 2008. I've never worked with SQL server before, nor T-SQL. I have the following VB function to calculate a simple moving average. It's flexible in the sense that it works on a number of different commodities, and for any window.
Public Function SMA_EOD(Commodity, TrdDay, period As Integer)
Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Double
Dim n As Integer
sql = "SELECT [COMM_SYMB], [TRD_DAY], [CLOSE] FROM fct_EOD" & _
" WHERE [fct_EOD]![COMM_SYMB] ='" & Commodity & "'" & _
" AND [fct_EOD]![TRD_DAY] <= #" & TrdDay & "#" & _
" ORDER BY [fct_EOD]![TRD_DAY];"
Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
SMA_EOD = 0
Exit Function
Else
ma = ma + rst.Fields("CLOSE")
End If
rst.MovePrevious
Next n
rst.Close
SMA_EOD = ma / period
End Function
I need help with the general approach, and then on how to decide which of: a stored procedure, function, or cursor-based solution best fits my situation.
Thanks in advance,
Wayne

Viewing 10 posts - 1 through 9 (of 9 total)

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

krayknot SSCertifiable Points: 7998 More actions · Answer 1

waykna (12/7/2008)
Hi
I'm in the process of migrating a MS-Access based trading system to SQL Server 2008. I've never worked with SQL server before, nor T-SQL. I have the following VB function to calculate a simple moving average. It's flexible in the sense that it works on a number of different commodities, and for any window.
Public Function SMA_EOD(Commodity, TrdDay, period As Integer)
Dim rst As DAO.Recordset
Dim sql As String
Dim ma As Double
Dim n As Integer
sql = "SELECT [COMM_SYMB], [TRD_DAY], [CLOSE] FROM fct_EOD" & _
" WHERE [fct_EOD]![COMM_SYMB] ='" & Commodity & "'" & _
" AND [fct_EOD]![TRD_DAY] <= #" & TrdDay & "#" & _
" ORDER BY [fct_EOD]![TRD_DAY];"
Set rst = CurrentDb.OpenRecordset(sql)
rst.MoveLast
For n = 0 To period - 1
If rst.BOF Then
SMA_EOD = 0
Exit Function
Else
ma = ma + rst.Fields("CLOSE")
End If
rst.MovePrevious
Next n
rst.Close
SMA_EOD = ma / period
End Function
I need help with the general approach, and then on how to decide which of: a stored procedure, function, or cursor-based solution best fits my situation.
Thanks in advance,
Wayne

exact replica of the above function into stored procedure is quite impossible.

what do you want to do??

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

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

Derive a simple moving average (SMA), where the window for which the SMA is calculated is dynamic; i.e. the function accommodates new records as daily data is added so is not bound to a date. Furthermore the size of the window is parameter driven, so a 5-day SMA or a 100-day SMA uses the same function.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

Hi Wayne

Are TrdDay and period completely independent?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Yes, they are independent. TrdDay is a date (business day) for which there is trading data (high, low, close, etc...), and period is the size of the simple moving average window, e.g. 5 days, 21 days, 50days...

Thx.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

waykna (12/8/2008)
Yes, they are independent. TrdDay is a date (business day) for which there is trading data (high, low, close, etc...), and period is the size of the simple moving average window, e.g. 5 days, 21 days, 50days...
Thx.

Ok, thanks.

I'm reading your code like this:

Get data up to and including TrdDay, then from this dataset, use the earliest n rows for the calculation, where n = period.

Does this look about right? If not, could you please describe in a little more detail? Cheers.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

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

Your description is about right, except that the latest (most current) n rows are used for the calculation. The function moves from the most current to the earliest record. E.g. if only 150 records are available for a commodity and a 100 day moving average is derived, then only the most current 51 records will have values as there's insufficient data for the first 99.

The function is called from an access query that contains the following fields:

Trading Day

Commodity

SMA_5: SMA_EOD([COMM_SYMB],[TRD_DAY],5) in this example a 5 day moving average

There are 40 different commodities and two years data in fct_EOD. It obtains all records up to the most current (maximum) trading day, per commodity in order to derive the moving average.

Thx.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

waykna (12/9/2008)
Your description is about right, except that the latest (most current) n rows are used for the calculation. The function moves from the most current to the earliest record. E.g. if only 150 records are available for a commodity and a 100 day moving average is derived, then only the most current 51 records will have values as there's insufficient data for the first 99.
The function is called from an access query that contains the following fields:
Trading Day
Commodity
SMA_5: SMA_EOD([COMM_SYMB],[TRD_DAY],5) in this example a 5 day moving average
There are 40 different commodities and two years data in fct_EOD. It obtains all records up to the most current (maximum) trading day, per commodity in order to derive the moving average.
Thx.

You mean, select the most recent n rows (where n = period) where the date is older than or equal to @TrdDay?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ron McCullough SSC Guru Points: 63877 More actions · Answer 8

Wahkna.

May I suggest that you look at Books On Line (BOL) at

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/4534b705-d946-441b-9b5d-5fbe561c9131.htm

for examples of how to use the AVG function available in SQL Server.

knowing how your dates are stored in your ACCESS db, you might also want to read in BOL the various ways and formats for date and datetime in SQL Server 2008. And then search SSC for articles by Jeff Moden on how to handle date calculations in T-SQL

Hope this gets you started and if you still have questions come back with them.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 9

Here is the equivalent of your function in T-SQL:

create Function SMA_EOD(@Commodity as Varchar(25), @TrdDay as DateTime, @period As Integer)

Returns Integer AS

Begin

Return (SELECT TOP (@Period)

Case When Count(*) < 100

Then Sum([CLOSE])

Else 0 End as CloseSum

FROM fct_EOD

WHERE fct_EOD.COMM_SYMB = @Commodity

AND fct_EOD.TRD_DAY <= @TrdDay)

End

Well, actually I couldn't help myself, and I did fix some stuff so that it would be faster. This approach is still pretty slow though.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]