vdo2
08-21-2017, 07:30 AM
Hi all,
I have this function which i am trying to modify.
The function below basically calculates the migration frequencies of IDs moving from one rating to another over a period of one calendar year which restricts the computation to cohorts formed at the end of the year (ystart) and to transitions occuring until the end of the year (yend). if they are not specified then the transition matrix is estimated from the year end following the first rating to the year end preceding the last rating action.
My problem is that basically even if i have monthly data i can only calculate transition matrices within a calendar year. However, I would like to estimating rolling transitions such as for instance from Feb-2002 up to Feb-2003, Mar-2002 up to Mar-2003. The function, such as it is written only limits to year end to year end. Anyone can help to modify it where i could compute rolling 12-month frequencies?
Thank you very much for your help!
Function COHORT(id, dat, rat, _
Optional classes As Integer, Optional ystart, Optional yend)
'Function is written for data sorted according to issuers and rating dates (ascending),
'rating classes numbered from 1 to classes, last rating class=default, not rated has number "0"
If IsMissing(ystart) Then ystart = Year(Application.WorksheetFunction.Min(dat))
If IsMissing(yend) Then yend = Year(Application.WorksheetFunction.Max(dat)) - 1
If classes = 0 Then classes = Application.WorksheetFunction.Max(rat)
Dim obs As Long, K As Long, kn As Long, i As Integer, j As Integer, t As Integer
Dim ni() As Long, nij() As Long, pij() As Double, newrat As Integer
ReDim nij(1 To classes - 1, 0 To classes), ni(0 To classes)
obs = id.Rows.Count
For K = 1 To obs
'Earliest cohort to which observation can belong is from year:
t = Application.Max(ystart, Year(dat(K)))
'Loop through cohorts to which observation k can belong
Do While t < yend
'Is there another rating from the same year?
If id(K + 1) = id(K, 1) And Year(dat(K + 1)) <= t And K <> obs Then
Exit Do
End If
'Is the issuer in default or not rated?
If rat(K) = classes Or rat(K) = 0 Then Exit Do
'Add to number of issuers in cohort
ni(rat(K)) = ni(rat(K)) + 1
'Determine rating from end of next year (=y+1)
'rating stayed constant
If id(K + 1) <> id(K) Or Year(dat(K + 1)) > t + 1 Or K = obs Then
newrat = rat(K)
'rating changed
Else
kn = K + 1
Do While Year(dat(kn + 1)) = Year(dat(kn)) And id(kn + 1) = id(kn)
If rat(kn) = classes Then Exit Do 'Default is absorbing!
kn = kn + 1
Loop
newrat = rat(kn)
End If
'Add to number of transitions
nij(rat(K), newrat) = nij(rat(K), newrat) + 1
'Exit if observation k cannot belong to cohort of y+1
If newrat <> rat(K) Then Exit Do
t = t + 1
Loop
Next K
ReDim pij(1 To classes - 1, 1 To classes + 1)
'Compute transition frequencies pij=Nij/Ni
For i = 1 To classes - 1
For j = 1 To classes
If ni(i) > 0 Then pij(i, j) = nij(i, j) / ni(i)
Next j
Next i
'NR category to the end
For i = 1 To classes - 1
If ni(i) > 0 Then pij(i, classes + 1) = nij(i, 0) / ni(i)
Next i
COHORT = pij
End Function
I have this function which i am trying to modify.
The function below basically calculates the migration frequencies of IDs moving from one rating to another over a period of one calendar year which restricts the computation to cohorts formed at the end of the year (ystart) and to transitions occuring until the end of the year (yend). if they are not specified then the transition matrix is estimated from the year end following the first rating to the year end preceding the last rating action.
My problem is that basically even if i have monthly data i can only calculate transition matrices within a calendar year. However, I would like to estimating rolling transitions such as for instance from Feb-2002 up to Feb-2003, Mar-2002 up to Mar-2003. The function, such as it is written only limits to year end to year end. Anyone can help to modify it where i could compute rolling 12-month frequencies?
Thank you very much for your help!
Function COHORT(id, dat, rat, _
Optional classes As Integer, Optional ystart, Optional yend)
'Function is written for data sorted according to issuers and rating dates (ascending),
'rating classes numbered from 1 to classes, last rating class=default, not rated has number "0"
If IsMissing(ystart) Then ystart = Year(Application.WorksheetFunction.Min(dat))
If IsMissing(yend) Then yend = Year(Application.WorksheetFunction.Max(dat)) - 1
If classes = 0 Then classes = Application.WorksheetFunction.Max(rat)
Dim obs As Long, K As Long, kn As Long, i As Integer, j As Integer, t As Integer
Dim ni() As Long, nij() As Long, pij() As Double, newrat As Integer
ReDim nij(1 To classes - 1, 0 To classes), ni(0 To classes)
obs = id.Rows.Count
For K = 1 To obs
'Earliest cohort to which observation can belong is from year:
t = Application.Max(ystart, Year(dat(K)))
'Loop through cohorts to which observation k can belong
Do While t < yend
'Is there another rating from the same year?
If id(K + 1) = id(K, 1) And Year(dat(K + 1)) <= t And K <> obs Then
Exit Do
End If
'Is the issuer in default or not rated?
If rat(K) = classes Or rat(K) = 0 Then Exit Do
'Add to number of issuers in cohort
ni(rat(K)) = ni(rat(K)) + 1
'Determine rating from end of next year (=y+1)
'rating stayed constant
If id(K + 1) <> id(K) Or Year(dat(K + 1)) > t + 1 Or K = obs Then
newrat = rat(K)
'rating changed
Else
kn = K + 1
Do While Year(dat(kn + 1)) = Year(dat(kn)) And id(kn + 1) = id(kn)
If rat(kn) = classes Then Exit Do 'Default is absorbing!
kn = kn + 1
Loop
newrat = rat(kn)
End If
'Add to number of transitions
nij(rat(K), newrat) = nij(rat(K), newrat) + 1
'Exit if observation k cannot belong to cohort of y+1
If newrat <> rat(K) Then Exit Do
t = t + 1
Loop
Next K
ReDim pij(1 To classes - 1, 1 To classes + 1)
'Compute transition frequencies pij=Nij/Ni
For i = 1 To classes - 1
For j = 1 To classes
If ni(i) > 0 Then pij(i, j) = nij(i, j) / ni(i)
Next j
Next i
'NR category to the end
For i = 1 To classes - 1
If ni(i) > 0 Then pij(i, classes + 1) = nij(i, 0) / ni(i)
Next i
COHORT = pij
End Function