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

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!

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

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

Powered by vBulletin® Version 4.2.5 Copyright © 2019 vBulletin Solutions Inc. All rights reserved.