View Full Version : [SOLVED:] Even Vectors - Panel Data
Natarajan95
04-25-2019, 06:25 AM
Hi guys,
I am writing my thesis on an extensive panel data set. However, my data source doesn't allow me to pull data on specific dates and instead just supplies me with changes in my data. This implies that i have to manually correct the length of the vectors of 70.000 rows.
I am no genious at VBA, but i am guessing there is a smarter way to do this. I provided a sample workbook as an attachment.
I basically need to add rows so all of the observations are at a length of 32, and if there is no observation in that given period, simply link the price (Kiid Ongoing Charge) to the previous period. I hope one of you can help.
Best regards
Patrick
Fennek
04-25-2019, 08:56 AM
Hello, for the first stock the following code could help:
Sub T_1()
Dim Stock(31, 5)
r = 3 Charge = 0
For i = 0 To 31
Stock(i, 0) = Cells(r, 1)
Stock(i, 1) = Cells(r, 2)
Qu = CDate(Format(CDate(DateAdd("q", i, #1/1/2011#)), "q-YYYY"))
Ch_Date = CDate(Format(Cells(r, 4), "q-YYYY"))
If Ch_Date > Qu Then
Stock(i, 3) = Qu
Stock(i, 4) = Charge
Stock(i, 5) = "Q" & Format(Qu, "q-YYYY")
Else
Stock(i, 3) = Cells(r, 4)
Stock(i, 4) = Cells(r, 5)
Stock(i, 5) = Cells(r, 6)
Charge = Stock(i, 4)
r = r + 1
End If
Next
i Cells(3, "O").Resize(32, 6) = Stock
End Sub
Can you add the loop for all stocks? regards
Fennek
04-25-2019, 08:57 AM
Sub T_1()
Dim Stock(31, 5)
r = 3
Charge = 0
For i = 0 To 31
Stock(i, 0) = Cells(r, 1)
Stock(i, 1) = Cells(r, 2)
Qu = CDate(Format(CDate(DateAdd("q", i, #1/1/2011#)), "q-YYYY"))
Ch_Date = CDate(Format(Cells(r, 4), "q-YYYY"))
If Ch_Date > Qu Then
Stock(i, 3) = Qu
Stock(i, 4) = Charge
Stock(i, 5) = "Q" & Format(Qu, "q-YYYY")
Else
Stock(i, 3) = Cells(r, 4)
Stock(i, 4) = Cells(r, 5)
Stock(i, 5) = Cells(r, 6)
Charge = Stock(i, 4)
r = r + 1
End If
Next i
Cells(3, "O").Resize(32, 6) = Stock
End Sub
Natarajan95
04-26-2019, 11:09 AM
Sub T_1()
Dim Stock(31, 5)
r = 3
Charge = 0
For i = 0 To 31
Stock(i, 0) = Cells(r, 1)
Stock(i, 1) = Cells(r, 2)
Qu = CDate(Format(CDate(DateAdd("q", i, #1/1/2011#)), "q-YYYY"))
Ch_Date = CDate(Format(Cells(r, 4), "q-YYYY"))
If Ch_Date > Qu Then
Stock(i, 3) = Qu
Stock(i, 4) = Charge
Stock(i, 5) = "Q" & Format(Qu, "q-YYYY")
Else
Stock(i, 3) = Cells(r, 4)
Stock(i, 4) = Cells(r, 5)
Stock(i, 5) = Cells(r, 6)
Charge = Stock(i, 4)
r = r + 1
End If
Next i
Cells(3, "O").Resize(32, 6) = Stock
End Sub
Hi Fennek,
Thank you for the help on this! It seems like it's working well, and the reframing is perfect. I am however having some problems with the loop. Would i have to add an identifier for each unique stock?
Best regards
Patrick
Fennek
04-27-2019, 12:34 AM
Hello,
the idea of my code is to generate the 32 Quarters:
For i = 0 To 31
Qu = CDate(Format(CDate(DateAdd("q", i, #1/1/2011#)), "q-YYYY"))
Next i
It seem from your picture, that you modified this.
regards
Natarajan95
04-27-2019, 09:16 AM
Hello,
the idea of my code is to generate the 32 Quarters:
For i = 0 To 31
Qu = CDate(Format(CDate(DateAdd("q", i, #1/1/2011#)), "q-YYYY"))
Next i
It seem from your picture, that you modified this.
regards
Hi Fennek,
I didn't change anything from the code that you posted, and it works like a charm. But how would i go around looping it? I have a ton of stocks in a sheet with the same setup as in my initial sheet.
Br
Patrick
Fennek
04-27-2019, 12:51 PM
Hi,
you are right, try this code, just starting "Outer_Loop":
Sub Outer_Loop()
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) <> Cells(i - 1, 1) Then Clean_Stock_2 (i)
Next i
End Sub
Sub Clean_Stock_2(ByVal r As Long)
Dim Stock(31, 5)
Dim Quarter(31)
Dim Bo As Boolean
Charge = 0
'Frame
For i = 0 To 31
Stock(i, 0) = Cells(r, 1)
Stock(i, 1) = Cells(r, 2)
Stock(i, 2) = Cells(r, 3)
Stock(i, 5) = "Q" & Format(DateAdd("q", i, #1/1/2011#), "q-YYYY")
Quarter(i) = Stock(i, 5)
Next i
'Data
Do While Cells(r, 1) = Stock(0, 0)
Qu = "Q" & Format(Cells(r, 4), "q-YYYY")
rr = Application.Match(Qu, Quarter, 0)
If Not IsError(rr) Then
Stock(rr, 3) = Cells(r, 4)
Stock(rr, 4) = Cells(r, 5)
If Not Bo Then Charge = Stock(rr, 4): Bo = True
End If
r = r + 1
Loop
'fill
For i = 0 To 31
If Stock(i, 4) = 0 Then
Stock(i, 4) = Charge
Else
Charge = Stock(i, 4)
End If
Next i
'Output
lr = Cells(Rows.Count, "O").End(xlUp).Row + 1
lr = IIf(lr < 3, 3, lr)
Cells(lr, "O").Resize(32, 6) = Stock
End Sub
regards
Natarajan95
04-28-2019, 04:03 AM
Hi Fennek,
That seems to have worked - It loops the stocks exactly as i need it to. Excel gives me a "Subscript out of Range" error however, after running the first 4 stocks. What could be the reason behind this?
Br
Patrick
Fennek
04-28-2019, 02:44 PM
Hi,
this code is a bit "slimmer"
'von Q1-2011 - Q4-2018
Dim Quarter(31) As String
Sub Outer_Loop()
SetQu
For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row
If Cells(i, 1) <> Cells(i - 1, 1) Then Clean_Stock_2 (i)
Next i
End Sub
Sub Clean_Stock_2(ByVal r As Long)
Dim Stock(31, 5)
Dim Bo As Boolean
r1 = r
ID = Cells(r, 1)
ISIN = Cells(r, 2)
LegName = Cells(r, 3)
Charge = 0
'Data
Do While Cells(r, 1) = ID And Year(Cells(r, 4)) < 2019
Qu = "Q" & Format(Cells(r, 4), "q-YYYY")
rr = Application.Match(Qu, Quarter, 0) - 1
If Not IsError(rr) Then
Stock(rr, 3) = Cells(r, 4)
Stock(rr, 4) = Cells(r, 5)
If Not Bo Then Charge = Stock(rr, 4): Bo = True
End If
r = r + 1
Loop
'fill
For i = 0 To 31
Stock(i, 0) = ID
Stock(i, 1) = ISIN
Stock(i, 2) = LegName
Stock(i, 5) = Quarter(i)
If Stock(i, 4) = 0 Then
Stock(i, 4) = Charge
Else
Charge = Stock(i, 4)
End If
Next i
'Output
lr = Cells(Rows.Count, "O").End(xlUp).Row + 1
lr = IIf(lr < 3, 3, lr)
Cells(lr, "O").Resize(32, 6) = Stock
End Sub
Sub SetQu()
For i = 0 To 31
Quarter(i) = "Q" & Format(DateAdd("q", i, #1/1/2011#), "q-YYYY")
Next i
End Sub
Now you have to find your way for your own.
regards
Natarajan95
04-29-2019, 03:01 AM
Hi Fennek,
Thank you for the help on this, it seems to be working now.
Best
Patrick
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.