Simple Moving Averages & VB Coding

Hey Everyone,

I'm just starting a new project and my first task is to create two Simple Moving Averages (SMA) and compare them. For example I have a data set that changes each day, similar to a stock or currency. I want to calculate a Long SMA and a Short SMA. A Long SMA would be the average of say the last 20 data points, and a Short SMA the average of the Last 10 Data Points at a given date in time. As time moves ahead the average would continue to move ahead with it.

The challenge is however, I am required to be able to change how many data points the long and short SMAs take into consideration. One iteration of the code I may be required to use 20 & 10 data points, and upon the next iteration I may wish to use 21 & 9 data points. the Pseudo Code would look something like this:

On January 1 2000

LongSMA = B3 = X
ShortSMA = C3 = Y

Take the average as of January 1 2000 going back X days and record it in cell D4
Take the average as of January 1 2000 going back Y days and record it in cell E4

Move ahead one day & record averages in Cell B4 & C4, respectively

Cont....

I have attached a sample Work Book with Two Worksheets to help illustrate my question.

Any thoughts ?

Sorry for the long winded question.