PDA

View Full Version : Rolling Calculation



KristinaS
08-10-2013, 02:12 PM
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))*(ept-eptAverage)^2)^0.5

Where ept 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
08-10-2013, 03:21 PM
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
08-10-2013, 03:47 PM
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
08-11-2013, 04:13 AM
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
08-11-2013, 04:15 AM
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
08-11-2013, 08:28 AM
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= (ept-eptAverage)/(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
08-11-2013, 11:57 AM
TE=( (1/(n-1)) * (ept-eptAverage)^2 )^0.5

And I think, but don't quote me

TE= (1/(n-1))^0.5 * ept-eptAverage

And that might be the same as

TE= 1/(n-1)^2 * ept-eptAverage

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

(ept-eptAverage) / (row(A1)-1)^2

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

p45cal
08-11-2013, 02:20 PM
named ranges ept, eptaverage and n used in the sheet attached:10410
Image in next post.

p45cal
08-11-2013, 02:26 PM
Image for previous post:
10412