I have financial data that spans several months. At times, an upward or downward trend is apparent, but in some ranges of dates, the data is volatile and it's hard to discern a trend.
What are some techniques to apply to large data sets to determine the overall trend?
The moving average (discussed in Calculating a Moving Average) is the de facto standard for pulling a trend out of seemingly random data. The approach used in Calculating a Moving Average computed an average based on calendar days, with all seven days of the week counted in the calculation. In this example, however, we'll need to use actual data points without regard to the calendar.
Say you want to calculate 20- and 50-day moving averages for your data. Twenty data points are needed to get a 20-day moving average. But in financial markets, data is based on days the market is open (which excludes weekends and holidays). Thus, while the 20-day average will be based on 20 days of activity, the calendar spread will cover more than 20 days.
This routine calculates a 20- and 50-day moving average:
Sub compute_moving_averages() Dim db As DAO.Database Dim rs As DAO.Recordset Dim ssql As String Dim sumit As Integer Dim avg20 As Single Dim avg50 As Single Set db = CurrentDb ssql = "Select * From Yahoo Order By Date" Set rs = db.OpenRecordset(ssql, dbOpenDynaset) 'move down to the 20th row to start rs.Move 19 Do While Not rs.EOF avg20 = 0 rs.Move -19 For sumit = 1 To 20 avg20 = avg20 + rs.Fields("Close") ...