PDA

View Full Version : Automate Lagging of Variables



Nick_London
11-27-2009, 09:22 AM
Hello,

I'm looking to automate creating lags of variables. In the attached workbook - the sheet source contains a list of variables arranged by columns.

I want to create a new set of variables for each column lagged up to a certain order.

The sheet provides an example of lags for the first 2 variables to the 6th order. I would like to do this all all the variables in the source sheet. It would also save a lot of time if each lag can be autolabled as per the labels in row of the sheet. I've just added L1, L2, L3, L4, L5, L6 to the variable names.

Also it will ideal if any code could be flexible so that I can change the numbers of lags required in the code or via an input box as needed.

Hope someone can show me the way.....

Thanks,

Nick

p45cal
11-27-2009, 10:26 AM
I hope you've got xl2007 because the 68 columns in your sheet x 7 =476 and xl2003 only has 255 columns. Anyway, first select the range, including headers, on your source sheet and run this macro. Enter the number of lags you want and click OK.
Sub Blah()
Dim SourceRange As Range
Set SourceRange = Selection
LagsReqd = InputBox("How many Lags required?", "Number of Lags required", 6)
Set NewSht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy NewSht.Cells(1, 1)
DestColm = 2
For Each Colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
Header = Colm.Cells(1).Value
Colm.Copy NewSht.Cells(1, DestColm)
DestColm = DestColm + 1
For i = 1 To CLng(LagsReqd)
NewSht.Cells(1, DestColm) = Header & "L" & i
Colm.Offset(1).Resize(Colm.Rows.Count - 1).Copy NewSht.Cells(i + 2, DestColm)
DestColm = DestColm + 1
Next i
Next Colm
End Sub
It creates a new sheet each time.

Nick_London
11-29-2009, 03:00 PM
Hello P45Cal,

Thank you for the solution and highlighting the column limitation issues, didn't think of that.

As a test, I tried running the code in Excel 2003 for a subset of the variables and it works great.

To get over the column limitations, I thought about whether it's absolutely essential to include large number of lags and the answer is probably no. There's probably a way I can work manually work out which lags I need for each variable. In accordance with this thinking, what if i want to calculate a maximum of three lags for each variable but with the lag order differing per variable depending on what values are in rows 2, 3, and 4.

So for example for the first variable in column B, I could put the values 1, 4, 12 in B2, B3 and B4. Then I just need the macro to transforms that variable according to that lag order - at lag 1, lag 4 and lag 12. And then for the next variable, create lags based on values in C2,C3 and C4 and so fourth.

So in the result sheet I would have only maximum of 3 lags for each variable based on predefined lag orders for each variable. Also I don't essentially need the orginal variable in the results sheets unlagged), only the lagged varibales, further saving space and column limitation problems.

Would the above be easy to integrate into the code?

Thanks,

Nick

mdmackillop
11-29-2009, 03:34 PM
Sub Blah()
Dim SourceRange As Range
Set SourceRange = Selection
Set NewSht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy NewSht.Cells(1, 1)
DestColm = 2
For Each colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
For j = 2 To 4
Header = colm.Cells(1).Value
lag = colm.Cells(j).Value

Range(colm.Cells(5), colm.Cells(colm.Cells.Count)).Copy NewSht.Cells(5, DestColm).Offset(lag)
DestColm = DestColm + 1
NewSht.Cells(1, DestColm - 1) = Header & "L" & lag
Next j
Next colm
End Sub

p45cal
11-29-2009, 04:11 PM
very similarly!:Sub Blah()
Dim SourceRange As Range
Set SourceRange = Selection
Set NewSht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy NewSht.Cells(1, 1)
DestColm = 2
For Each colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
Header = colm.Cells(1).Value
For i = 2 To 4
lag = colm.Cells(i)
If lag <> "" Then
NewSht.Cells(1, DestColm) = Header & "L" & lag
colm.Offset(4).Resize(colm.Rows.Count - 4).Copy NewSht.Cells(lag + 5, DestColm)
DestColm = DestColm + 1
End If
Next i
Next colm
End Sub

mdmackillop
11-29-2009, 04:30 PM
...and neater too!

Nick_London
12-08-2009, 11:36 AM
Gents,

I have tested this out and works great. Thanks for your efforts.

May need to tweak slightly in the future but for now is fine.

Nick

Nick_London
04-05-2010, 06:55 AM
Hi,

I would like to make a change to the macro I am using the calculate lags of variables but am struggling to get it to work. Currently the macro below calculates the order of lags based on the entries in rows 2 to 4 in each column for a given variable.


Sub Create_Specified_lags()
Dim SourceRange As Range
Set SourceRange = Selection
Set newsht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy newsht.Cells(1, 1)
DestColm = 2
For Each colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
Header = colm.Cells(1).Value
For i = 2 To 4 'lag order worked out here
lag = colm.Cells(i)
If lag <> "" Then
newsht.Cells(1, DestColm) = Header & "L" & lag
colm.Offset(4).Resize(colm.Rows.Count - 4).Copy newsht.Cells(lag + 5, DestColm)
DestColm = DestColm + 1
End If
Next i
Next colm
End Sub



I would now like to specify the order of lags in one cell of the column, e,g row 3, in the following format:

B3: 2,3,7, 4
C3: 1,2,3
D3: 7
etc

So instead reading the lags orders based on row 3 to 4, I want it to read it from within cell. I have found out how to spilt the cells to read each lag by using the split function below.

Sub Split3()
Dim x As Variant
Dim i As Long
x = Split(Range("B3"), ",") 'specify cell to split and the delimiter
For i = 0 To UBound(x)
MsgBox x(i)
Next i
End Sub

This gives an example for column B only. But I struggling to work out how to incorporate this into the orginal code. I've come up with something like the below but it does not work at all, changes are indicated by NEW.

Hope someone can help.

Thanks,

Nick

Sub Create_Specified_lags_test()
Dim SourceRange As Range
Dim x As Variant 'NEW
Dim k As Long 'NEW
Set SourceRange = Selection
Set newsht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy newsht.Cells(1, 1)
DestColm = 2
For Each colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
Header = colm.Cells(1).Value

x = Split(Cells(3, colm).Value, ",") 'NEW
For k = 0 To UBound(x) 'NEW
' For i = 2 To 4
lag = x 'NEW
If lag <> "" Then
newsht.Cells(1, DestColm) = Header & "L" & lag
colm.Offset(4).Resize(colm.Rows.Count - 4).Copy newsht.Cells(lag + 5, DestColm)
DestColm = DestColm + 1
End If
Next k
Next colm
End Sub

p45cal
04-06-2010, 08:57 AM
Sub Blah()
Dim SourceRange As Range, NewSht As Worksheet, DestColm As Long, Colm As Range, Header, myArray, i, lag
Set SourceRange = Selection
Set NewSht = ThisWorkbook.Sheets.Add
SourceRange.Columns(1).Copy NewSht.Cells(1, 1)
DestColm = 2
For Each Colm In SourceRange.Columns.Offset(, 1).Resize(, SourceRange.Columns.Count - 1)
Header = Colm.Cells(1).Value
myArray = Split(Colm.Cells(3).Value, ",")
For i = 0 To UBound(myArray)
lag = myArray(i)
If lag <> "" Then
NewSht.Cells(1, DestColm) = Header & "L" & lag
Colm.Offset(4).Resize(Colm.Rows.Count - 4).Copy NewSht.Cells(lag + 5, DestColm)
DestColm = DestColm + 1
End If
Next i
Next Colm
End Sub