Rolling Calculation

[KristinaS]

Hi,
I'm completely new to VBA and have quite a complex task to carry out. So far I have been doing ok, however I need to calculate a variable using rolling windows... Let me explain. In one worksheet I have 30 rows of data, each row represents a time series of daily returns differences. In the next worksheet I would like to calculate a time series for each row using the formula below

TE=((1/(n-1))*(e_pt-e_pt^Average)^2)^0.5

Where e_pt is the variable in the rows, e(ptaverage) is the average of all observations above, and n is the number of observations up to e(pt)

I am not sure how to go about this since I have to do this for 30 different rows, each different lengths.

In the workbook below, the first worksheet has the data and the second has the first three calculations to illustrate the formula.

Can anyone help?

Thanks

[SamT]

Assuming that the formulas in Calculations! rows 2, 3, & 4 are correct, Place this formula in Calculations(A1) and copy it down as far as needed. (Select A1 and the area it needs to be in and press Ctrl+D)

=IF(OR(Data!A1="",Data!A1=0),"",((1/COUNT(Data!A$1:A1))*(Data!A1-SUM(Data!A$1:A1)/COUNT(Data!A$1:A1))^2)^0.5)

Further assuming you want each column in Calculations to calculate it's corresponding column in Data, After you have the formulas all the way down in Column "A", Select from Column "A" to Column "AE" and press Ctrl+R to fill all columns and rows with the correct formula.

When a cell address in a formula has a $ in front of the column letter or row number, the letter(s) or number following the $ will not change no matter where you paste it.


The same thing can be done in VBA code to give a much smaller file size, but I think it would be slower to operate. I just filled your example Calculations with the formula from A! to AE1700 and it took less than a minute to finish.

[p45cal]

Assuming:
1. You want VBA
2. The bottommost zeroes in each column (not row) are not to be used in the calculation
3. The sample formulae you had in the Calculations sheet gave the right answers (I see no -1 in the formulae there)

Try this:

Sub blah()
For Each colm In Sheets("Data").Range("A1:AD1652").Columns
  lr = colm.Rows.Count
  Do Until colm.Cells(lr) <> 0 Or lr = 1
    lr = lr - 1
  Loop
  Sheets("Calculations").Range(colm.Resize(lr).Address).FormulaR1C1 = "=((1/COUNT(Data!R1C:RC))*(Data!RC-SUM(Data!R1C:RC)/COUNT(Data!R1C:RC))^2)^0.5"
Next colm
End Sub

[KristinaS]

Assuming:
1. You want VBA
2. The bottommost zeroes in each column (not row) are not to be used in the calculation
3. The sample formulae you had in the Calculations sheet gave the right answers (I see no -1 in the formulae there)

Try this:

Sub blah()
For Each colm In Sheets("Data").Range("A1:AD1652").Columns
  lr = colm.Rows.Count
  Do Until colm.Cells(lr) <> 0 Or lr = 1
    lr = lr - 1
  Loop
  Sheets("Calculations").Range(colm.Resize(lr).Address).FormulaR1C1 = "=((1/COUNT(Data!R1C:RC))*(Data!RC-SUM(Data!R1C:RC)/COUNT(Data!R1C:RC))^2)^0.5"
Next colm
End Sub

Thank you so much! works like a charm!

[KristinaS]

Assuming that the formulas in Calculations! rows 2, 3, & 4 are correct, Place this formula in Calculations(A1) and copy it down as far as needed. (Select A1 and the area it needs to be in and press Ctrl+D)

[CODE=IF(OR(Data!A1="",Data!A1=0),"",((1/COUNT(Data!A$1:A1))*(Data!A1-SUM(Data!A$1:A1)/COUNT(Data!A$1:A1))^2)^0.5)/CODE]

Further assuming you want each column in Calculations to calculate it's corresponding column in Data, After you have the formulas all the way down in Column "A", Select from Column "A" to Column "AE" and press Ctrl+R to fill all columns and rows with the correct formula.

When a cell address in a formula has a $ in front of the column letter or row number, the letter(s) or number following the $ will not change no matter where you paste it.


The same thing can be done in VBA code to give a much smaller file size, but I think it would be slower to operate. I just filled your example Calculations with the formula from A! to AE1700 and it took less than a minute to finish.

Thank you SamT, this one works great too!

[snb]

I don't think 1/count(Data!A$1:A1) matches 1/(n-1)

I'd prefer to translate 1/(n-1) into 1/(row(A1)-1)

And I thought that ^2^.5 = ^1

The whole formula rewritten:

TE= (e_pt-e_pt^Average)/(n-1)

A oneliner might suffice:

Sub M_snb()
   [Calculations!A1:AD1700] = [if(Data!A1:AD1700="","",if(Data!A1:AD1700=0,"",if(row(A1:A1700)=1,Data!A1:AD1700,(Data!A1:AD1700-average(offset(A$1:AD$1,,,row(A1:AD1700))))/(row(A1:A1700)-1))))]
End Sub

[SamT]

TE=( (1/(n-1)) * (e_pt-e_pt^Average)^2 )^0.5

And I think, but don't quote me

TE= (1/(n-1))^0.5 * e_pt-e_pt^Average

And that might be the same as

TE= 1/(n-1)^2 * e_pt-e_pt^Average

And ( although I probably completly lost it in the last 2 lines) that would be

(e_pt-e_pt^Average) / (row(A1)-1)^2

Which would give a divide by zero error and preclude putting the formula in Row 1

[p45cal]

named ranges ept, eptaverage and n used in the sheet attached:vbaExpress47122.xlsx
Image in next post.

[p45cal]

Image for previous post:
2013-08-11_221826.jpg