Consulting

Results 1 to 10 of 10

Thread: Even Vectors - Panel Data

  1. #1

    Even Vectors - Panel Data

    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
    Attached Files Attached Files

  2. #2
    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

  3. #3
    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

  4. #4
    Quote Originally Posted by Fennek View Post
    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
    Attached Images Attached Images
    Last edited by Natarajan95; 04-26-2019 at 02:08 PM.

  5. #5
    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

  6. #6
    Quote Originally Posted by Fennek View Post
    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

  7. #7
    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

  8. #8
    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

  9. #9
    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

  10. #10
    Hi Fennek,
    Thank you for the help on this, it seems to be working now.

    Best

    Patrick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •