Even Vectors - Panel Data

[Natarajan95]

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]

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]

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]

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]

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]

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