Public Sub AlignDate()
Dim X() As Variant
Dim shtArr() As Integer
Dim smallest_date As Date
Dim largest_date As Date
Dim fwdFill As Boolean
Application.ScreenUpdating = False
Start:
If Sheet1.OptionButton1.Value Then
fileType = Sheet1.OptionButton1.Caption
mulSel = True
X = Application.GetOpenFilename(filefilter:=fileType & " Files,*." & fileType, _
MultiSelect:=mulSel, Title:="File(s) to be aligned")
'Tests the variable X to see if it is valid
If UBound(X) = 0 Then Exit Sub
ReDim shtArr(1)
shtArr(1) = 1
smallest_date = minDateCSV(X(), shtArr())
largest_date = maxDateCSV(X(), shtArr())
ElseIf Sheet1.OptionButton2.Value Then
fileType = Sheet1.OptionButton2.Caption
mulSel = False
Target = Application.GetOpenFilename(filefilter:=fileType & " Files,*." & fileType, _
MultiSelect:=mulSel, Title:="File(s) to be aligned")
If Target = False Then Exit Sub
ReDim X(1) As Variant
X(1) = Target
smallest_date = minDateXL(X(), shtArr())
largest_date = maxDateXL(X(), shtArr())
Else
MsgBox Prompt:="Select a File Type First", Buttons:=vbCritical
Exit Sub
End If
If Sheet1.OptionButton3.Value Then
fwdFill = True
ElseIf Sheet1.OptionButton4.Value Then
fwdFill = False
Else
MsgBox Prompt:="Select Whether to forward fill or not", Buttons:=vbCritical
Exit Sub
End If
MsgBox smallest_date
Application.ScreenUpdating = True
MsgBox largest_date
If (DateDiff("d", smallest_date, largest_date) <= 0) Then
MsgBox Prompt:="Issue with data. Max date can not be less than or Equal to Min date. Check!!", Buttons:=vbCritical
Exit Sub
End If
If MsgBox("Date Range:- " & smallest_date & " To " & largest_date, vbOKCancel) = vbCancel Then
Exit Sub
End If
Call alignFiles(X(), smallest_date, largest_date, fwdFill)
End Sub
Private Function minDateCSV(X() As Variant, shtArr() As Integer) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
ActiveWorkbook.Close
Next
minDateCSV = smallDate
End Function
Private Function maxDateCSV(X() As Variant, shtArr() As Integer) As Date
Dim largeDates As Date
Dim largeDate As Date
Dim sheet_index As Integer
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
Sheets(1).Activate
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
ActiveWorkbook.Close
Next
maxDateCSV = largeDate
End Function
Private Sub alignFiles(X() As Variant, smallest_date As Date, largest_date As Date, fwdFill As Boolean)
smallest_value = smallest_date
largest_value = largest_date
sheet_index = 1
tdate = smallest_value
'Populating NA and aligning the data
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
row = Row_Col(sheet_index)
col = 1
tdate = smallest_value
If row = 0 Then
GoTo x2
End If
Do Until tdate > largest_value
If row = 65537 Then
GoTo x2:
End If
If Sheets(sheet_index).Cells(row, col).Value <= tdate And Sheets(sheet_index).Cells(row, col).Value <> "" Then
GoTo X:
'Ignoring Saturdays and Sundays
ElseIf (DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7) Then
Sheets(sheet_index).Cells(row, col).EntireRow.Insert
Sheets(sheet_index).Cells(row, col) = tdate
Sheets(sheet_index).Activate
For count_col = 2 To Sheets(sheet_index).Cells(row - 1, 2).End(xlToRight).Column
If fwdFill Then
If IsNumeric(Sheets(sheet_index).Cells(row - 1, count_col).Value) Then
Sheets(sheet_index).Cells(row, count_col).Value = _
Sheets(sheet_index).Cells(row - 1, count_col).Value
Else
Sheets(sheet_index).Cells(row, count_col).Value = "NA"
End If
Else
Sheets(sheet_index).Cells(row, count_col).Value = "NA"
End If
Next
End If
X:
If (tdate = Sheets(sheet_index).Cells(row, col).Value) Or DatePart("w", tdate) = 1 Or DatePart("w", tdate) = 7 Then
tdate = DateAdd("d", 1, tdate)
End If
If DatePart("w", tdate) <> 1 And DatePart("w", tdate) <> 7 Then
row = row + 1
End If
Loop
x2:
Next
Next
End Sub
Private Function Row_Col(i) As Integer
Dim row, col As Integer
row = 1
col = 1
Do Until Sheets(i).Cells(row, col).Value = ""
If IsDate(Sheets(i).Cells(row, col).Value) Then
Row_Col = row
Exit Function
Else
row = row + 1
End If
Loop
End Function
Private Function minDateXL(X() As Variant, shtArr() As Integer) As Date
Dim smallDates As Date
Dim smallDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has atleast one line of header and the date is in the 1st column itself. Phew!wat a relief
If IsDate(ActiveSheet.Range("A2").Value) Then
smallDates = ActiveSheet.Range("A2").Value
Else
'If the date is not in the A2 then it is definitely in A3.
smallDates = ActiveSheet.Range("A3").Value
End If
If Y > 1 Then
If (DateDiff("d", smallDates, smallDate) > 0) Then
smallDate = smallDates
End If
Else
smallDate = smallDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
minDateXL = smallDate
End Function
Private Function maxDateXL(X() As Variant, shtArr() As Integer) As Date
Dim largeDates As Date
Dim largeDate As Date
For Y = 1 To UBound(X)
Workbooks.Open X(Y)
For sheet_index = 1 To ActiveWorkbook.Sheets.Count
Sheets(sheet_index).Activate
If (MsgBox("Do you want to include the tab " & ActiveSheet.Name & " in alignment process?", vbYesNo) = vbNo) Then
GoTo hmmm
End If
' Assumption: The Csv file has more than two lines of input and the date is in column A
' and the data is continuous
largeDates = ActiveSheet.Range("A1").End(xlDown).Value
If Y > 1 Then
If (DateDiff("d", largeDates, largeDate) < 0) Then
largeDate = largeDates
End If
Else
largeDate = largeDates
End If
hmmm:
Next
ActiveWorkbook.Close
Next
maxDateXL = largeDate
End Function
Wednesday, August 20, 2008
VBA-Netting
Sub netting()
'
' Macro netting
'
'
'------------------------------------------------------------------------------------------
'Prepairing the tabs to receive the data. The older data from the tabs will be cleared out.
'------------------------------------------------------------------------------------------
Sheets("Full").Select 'Clearing the Contents of Full tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Validation").Select 'Clearing the Validation Tab
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.ClearContents
Sheets("Long").Select 'Clearing the contents of Long tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short").Select 'Clearing the contents of Short tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short_Re").Select 'Clearing the contents of Short_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Long_Re").Select 'Clearing the contents of Long_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Result").Select 'Clearing the contents of Result tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Input").Select 'Copying Header from the Input Tab
Rows("1:1").Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
ActiveSheet.Paste 'Pasted the header in the Result Tab
Selection.Font.Bold = True
Range("Y1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents 'Clearing the end part of header after column Y
Range("A1").Select
Sheets("Input").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:X1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Full").Select
Range("A1").Select
ActiveSheet.Paste ' Copying all the data to Full tab. We will use this tab and not the Input tab now
'------------------------------------------------------------------------------------------------
'The data from the Full tab will be moved to Short and Long tab based on the LONG/SHORT Column
'------------------------------------------------------------------------------------------------
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Short" 'Selecting the short trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Short").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Long" 'Selecting the long trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Long").Select
Range("A1").Select
ActiveSheet.Paste
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the PAYFORMULA in Long Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],SEARCH(""%"",RC[6])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Long Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Short").Select
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the RECFORMULA in Short Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""%"",RC[-1])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Short Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("L14").Select
Range("A1").Select
Range("A2:AD2").Select
'Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Short tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
Sheets("Long").Select
Range("O10").Select
Range("A2:AD2").Select
'Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Long tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
'-------------------------------------------------------------------------------------------------------------
'The netting process starts here.
'The Long and Short tab gets netted for exact maturity and rest trades are marked as Not Netted.
'These not netted trades will be netted off once again in the LOng_Re and Short_Re tabs using the maturity band
'-------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Dim longCoupon, shortCoupon, longNotional, shortNotional, longMaturity, shortMaturity, longPV01, shortPV01 As Double
Dim shortResidualMaturity, longResidualMaturity As Double
Dim bigL, smallL, notnettedPoint, bigloopCount, smallloopCount, finalFlag, sm_i As Integer
Dim vali_Row_Ct As Integer
finalFlag = 0
Sheets("Short").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
Sheets(1).Select
vali_Row_Ct = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
If (bigL = bigloopCount And finalFlag = 1) Then
For sm_i = smallL To smallloopCount
Sheets("Short").Activate
Range("Z" & sm_i).Value = "N"
Next sm_i
Exit For
End If
Sheets("Long").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNotional = shortNotional) Then
If (longResidualMaturity = shortResidualMaturity) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
Else
Sheets("Long").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short").Activate
shortPV01 = Range("Y" & smallL).Value
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short").Range("B" & smallL).Value
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
Else
Sheets("Short").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long").Range("B" & bigL).Value
notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
End If
End If
Else
If ((longResidualMaturity - shortResidualMaturity) > 0) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
Sheets("Short").Range("Z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Range("Z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Next smallL
Next bigL
'----------------------------------------------------------------------------
' we need to do the netting of band of maturity after the full netting is over.
'
'------------------------------------------------------------------------------
Sheets("Long").Select
Range("A1:AD1").Select
Selection.AutoFilter
Range("K1").Select
Selection.End(xlToRight).Select
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Range("S1").Activate
Selection.Copy
Sheets("Long_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
'-------------------------------------------------------------------------------
' Copying not netted trades from Long tab to Long_Re tab
'-------------------------------------------------------------------------------
Sheets("Long").Select
Selection.Copy
Sheets("Long_Re").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Short").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AD1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Selection.Copy
'-------------------------------------------------------------------------------
'Copying not netted trades from Short tab to Short_Re tab
'-------------------------------------------------------------------------------
Sheets("Short_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Short").Select
Selection.Copy
Sheets("Short_Re").Select
Range("A1").Select
ActiveSheet.Paste
'--------------------------------------------------------------------------------
Sheets("Short_Re").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long_Re").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
Sheets("Long_Re").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longNettedFlag = Range("Z" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short_Re").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortNettedFlag = Range("Z" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNettedFlag = "Y") Then
Exit For
End If
If (longNettedFlag = "N") Then
If (shortNettedFlag = "N") Then
If (longNotional = shortNotional) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short_Re").Activate
'Range("z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long_Re").Activate
Range("z" & bigL).Value = "N"
'Exit For
Else
Sheets("Long_Re").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short_Re").Activate
shortPV01 = Range("Y" & smallL).Value
Range("AT" & smallL).Value = "Diff is less than 0.15"
If (longResidualMaturity < 30) Then
Exit For
ElseIf (shortResidualMaturity < 30) Then
'notnettedPoint = smallL + 1
ElseIf ((longResidualMaturity < 365 And shortResidualMaturity < 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0))) _
_
Or (longResidualMaturity > 365 And shortResidualMaturity > 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0)))) Then
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long_Re").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Short_Re").Select
Range("z" & smallL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
Else
Sheets("Short_Re").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Long_Re").Select
Range("z" & bigL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
'Sheets("Short_Re").Range("Z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
Else
'Sheets("Long_Re").Range("Z" & bigL).Value = "N"
Exit For
End If
End If
Else
'notnettedPoint = smallL + 1
End If
End If
Next
Next
'--------------------------------------------------------------------------------------
' Data needs to be moved from the Long and Short tabs to the Result tab
'--------------------------------------------------------------------------------------
Sheets("Long_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("2:2").Select
Selection.Delete shift:=xlUp
Sheets("Short_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("Z2").Select
row_delete = Selection.End(xlDown).Row + 1
If (row_delete >= 65536) Then
row_delete = 2
End If
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Application.CutCopyMode = False
Selection.Cut
Rows("1:1").Select
ActiveSheet.Paste
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Selection.Delete shift:=xlUp
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1").Select
'----------------------------------------Validation Sheet Checks ----------------------------
Sheets("Validation").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-2],4),MID(RC[-2],5,2),RIGHT(RC[-2],2))-DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
Range("H3").Select
Range("C2:H2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("C2:H" & vali_Row_Ct - 1)
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(ABS(RC[-2]-RC[-1])<0.15,""Ok"",""Check"")"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],Input!C[-10]:C[2],13,0)-VLOOKUP(RC[-10],Input!C[-10]:C[2],13,0)"
Range("K2:L2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("K2:L" & vali_Row_Ct - 1)
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>365,IF(ABS(RC[-1]-RC[-2])<31,""ok"",""check""),if(rc[-1]>30,IF(ABS(RC[-1]-RC[-2])<8,""Ok"",""Check""),IF(RC[-1]=RC[-2],""Ok"",""Check"")))"
Range("O2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("O2:O" & vali_Row_Ct - 1)
ActiveSheet.Calculate
Range("A1").Select
'----------------------------------Validation in results sheets ------------------------------
Application.ScreenUpdating = True
Sheets("Result").Select
Range("A1").Select
fin_row = Selection.End(xlDown).Row
Range("Z1").Select
ActiveCell.FormulaR1C1 = "PV01 Orig"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "PV01 Check with Orig"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Check for inclusion of netted deals"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Zone Orig"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Zone Check"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-24],Input!C[-24]:C[-2],23,0)"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-26],Validation!C[-27],1,0)),""Ok"",""Check"")"
Range("AB3").Select
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-27],Input!C[-27]:C[-6],22,0)"
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]=RC[-1]"
Range("Z2:AD2").Select
Selection.AutoFill Destination:=Range("Z2:AD" & fin_row)
ActiveSheet.Calculate
Range("A1").Select
End Sub
'
' Macro netting
'
'
'------------------------------------------------------------------------------------------
'Prepairing the tabs to receive the data. The older data from the tabs will be cleared out.
'------------------------------------------------------------------------------------------
Sheets("Full").Select 'Clearing the Contents of Full tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Validation").Select 'Clearing the Validation Tab
Range("A2:Q2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.ClearContents
Sheets("Long").Select 'Clearing the contents of Long tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short").Select 'Clearing the contents of Short tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Short_Re").Select 'Clearing the contents of Short_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Long_Re").Select 'Clearing the contents of Long_Re tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Result").Select 'Clearing the contents of Result tab
Cells.Select
Selection.ClearContents
Selection.ClearContents 'Cleared
Sheets("Input").Select 'Copying Header from the Input Tab
Rows("1:1").Select
Selection.Copy
Sheets("Result").Select
Range("A1").Select
ActiveSheet.Paste 'Pasted the header in the Result Tab
Selection.Font.Bold = True
Range("Y1").Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.ClearContents 'Clearing the end part of header after column Y
Range("A1").Select
Sheets("Input").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:X1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Full").Select
Range("A1").Select
ActiveSheet.Paste ' Copying all the data to Full tab. We will use this tab and not the Input tab now
'------------------------------------------------------------------------------------------------
'The data from the Full tab will be moved to Short and Long tab based on the LONG/SHORT Column
'------------------------------------------------------------------------------------------------
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Short" 'Selecting the short trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Short").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Full").Select
Selection.AutoFilter Field:=21, Criteria1:="Long" 'Selecting the long trades
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Long").Select
Range("A1").Select
ActiveSheet.Paste
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the PAYFORMULA in Long Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[6],SEARCH(""%"",RC[6])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Long Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Sheets("Short").Select
'------------------------------------------------------------------------------------------
'Calculating the coupon in column L from the RECFORMULA in Short Tab
'------------------------------------------------------------------------------------------
Columns("L:L").Select
Selection.Insert shift:=xlToRight
Range("L1").Select
ActiveCell.FormulaR1C1 = "Coupon"
Range("L2").Select
ActiveCell.FormulaR1C1 = "=LEFT(RC[-1],SEARCH(""%"",RC[-1])-1)"
Range("A1").Select
lastRow = Selection.End(xlDown).Row
Range("L2").Select
Selection.AutoFill Destination:=Range("L2:L" & lastRow)
Range("L2:L" & lastRow).Select
'------------------------------------------------------------------------------------------
'Calculating Residual Maturity=Maturity-today in Column AC in Short Tab
'------------------------------------------------------------------------------------------
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Residual Maturity"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Maturity"
Range("AD2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-10],4),MID(RC[-10],5,2),RIGHT(RC[-10],2))"
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=RC[1]-TODAY()"
Range("AC2").Select
Selection.NumberFormat = "General"
Range("AC2:AD2").Select
Selection.AutoFill Destination:=Range("AC2:AD" & lastRow)
ActiveSheet.Calculate
Range("AC2:AD" & lastRow).Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("L14").Select
Range("A1").Select
Range("A2:AD2").Select
'Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Short tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
Sheets("Long").Select
Range("O10").Select
Range("A2:AD2").Select
'Selection.End(xlToRight).Select
Range(Selection, Selection.End(xlDown)).Select
'--------------------------------------------------------------------------------------------------------
'Sorting the data in the Long tab. The sort order is by PAYNotional,ResidualMaturity and then by Coupon
'--------------------------------------------------------------------------------------------------------
Selection.Sort Key1:=Range("U2"), Order1:=xlAscending, Key2:=Range _
("AC2"), Order2:=xlAscending, Key3:=Range("L2"), Order3:=xlAscending, _
Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:= _
xlTopToBottom, DataOption1:=xlSortNormal, DataOption2:=xlSortTextAsNumbers _
, DataOption3:=xlSortNormal
'-------------------------------------------------------------------------------------------------------------
'The netting process starts here.
'The Long and Short tab gets netted for exact maturity and rest trades are marked as Not Netted.
'These not netted trades will be netted off once again in the LOng_Re and Short_Re tabs using the maturity band
'-------------------------------------------------------------------------------------------------------------
Application.ScreenUpdating = False
Dim longCoupon, shortCoupon, longNotional, shortNotional, longMaturity, shortMaturity, longPV01, shortPV01 As Double
Dim shortResidualMaturity, longResidualMaturity As Double
Dim bigL, smallL, notnettedPoint, bigloopCount, smallloopCount, finalFlag, sm_i As Integer
Dim vali_Row_Ct As Integer
finalFlag = 0
Sheets("Short").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
Sheets(1).Select
vali_Row_Ct = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
If (bigL = bigloopCount And finalFlag = 1) Then
For sm_i = smallL To smallloopCount
Sheets("Short").Activate
Range("Z" & sm_i).Value = "N"
Next sm_i
Exit For
End If
Sheets("Long").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNotional = shortNotional) Then
If (longResidualMaturity = shortResidualMaturity) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
Else
Sheets("Long").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short").Activate
shortPV01 = Range("Y" & smallL).Value
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short").Range("B" & smallL).Value
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
Else
Sheets("Short").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long").Range("B" & bigL).Value
notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
If (bigL = bigloopCount) Then
finalFlag = 1
Else
Exit For
End If
End If
End If
Else
If ((longResidualMaturity - shortResidualMaturity) > 0) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Activate
Range("z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
Sheets("Short").Range("Z" & smallL).Value = "N"
notnettedPoint = smallL + 1
Else
Sheets("Long").Range("Z" & bigL).Value = "N"
If (bigL = bigloopCount) Then
Sheets("Short").Activate
Range("z" & smallL).Value = "N"
Else
Exit For
End If
End If
End If
Next smallL
Next bigL
'----------------------------------------------------------------------------
' we need to do the netting of band of maturity after the full netting is over.
'
'------------------------------------------------------------------------------
Sheets("Long").Select
Range("A1:AD1").Select
Selection.AutoFilter
Range("K1").Select
Selection.End(xlToRight).Select
Range("Z1").Select
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Range("S1").Activate
Selection.Copy
Sheets("Long_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
'-------------------------------------------------------------------------------
' Copying not netted trades from Long tab to Long_Re tab
'-------------------------------------------------------------------------------
Sheets("Long").Select
Selection.Copy
Sheets("Long_Re").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Short").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AD1").Select
Application.CutCopyMode = False
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
Cells.Select
Selection.Copy
'-------------------------------------------------------------------------------
'Copying not netted trades from Short tab to Short_Re tab
'-------------------------------------------------------------------------------
Sheets("Short_Re").Select
Cells.Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("Short").Select
Selection.Copy
Sheets("Short_Re").Select
Range("A1").Select
ActiveSheet.Paste
'--------------------------------------------------------------------------------
Sheets("Short_Re").Select
Range("A1").Select
shortRows = ActiveCell.End(xlDown).Row
Sheets("Long_Re").Select
Range("A1").Select
longrows = ActiveCell.End(xlDown).Row
bigloopCount = longrows
smallloopCount = shortRows
notnettedPoint = 2
For bigL = 2 To bigloopCount
For smallL = notnettedPoint To smallloopCount
Sheets("Long_Re").Activate
longCoupon = Range("L" & bigL).Value + 0
longNotional = Range("U" & bigL).Value
longMaturity = Range("J" & bigL).Value
longNettedFlag = Range("Z" & bigL).Value
longResidualMaturity = Range("AC" & bigL).Value
Sheets("Short_Re").Activate
shortCoupon = Range("L" & smallL).Value + 0
shortNotional = Range("U" & smallL).Value
shortMaturity = Range("J" & smallL).Value
shortNettedFlag = Range("Z" & smallL).Value
shortResidualMaturity = Range("AC" & smallL).Value
If (longNettedFlag = "Y") Then
Exit For
End If
If (longNettedFlag = "N") Then
If (shortNettedFlag = "N") Then
If (longNotional = shortNotional) Then
If ((longCoupon - shortCoupon) > 0.15) Then
Sheets("Short_Re").Activate
'Range("z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
ElseIf ((shortCoupon - longCoupon) > 0.15) Then
Sheets("Long_Re").Activate
Range("z" & bigL).Value = "N"
'Exit For
Else
Sheets("Long_Re").Activate
longPV01 = Range("Y" & bigL).Value
Sheets("Short_Re").Activate
shortPV01 = Range("Y" & smallL).Value
Range("AT" & smallL).Value = "Diff is less than 0.15"
If (longResidualMaturity < 30) Then
Exit For
ElseIf (shortResidualMaturity < 30) Then
'notnettedPoint = smallL + 1
ElseIf ((longResidualMaturity < 365 And shortResidualMaturity < 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0))) _
_
Or (longResidualMaturity > 365 And shortResidualMaturity > 365 _
And (((longResidualMaturity - shortResidualMaturity) <> 0) _
Or ((shortResidualMaturity - longResidualMaturity) <> 0)))) Then
If ((longPV01 - shortPV01) > 0) Then
Sheets("Long_Re").Select
Range("z" & bigL).Value = "Y"
Range("AA" & bigL).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Short_Re").Select
Range("z" & smallL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
Else
Sheets("Short_Re").Select
Range("z" & smallL).Value = "Y"
Range("AA" & smallL).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Long_Re").Select
Range("z" & bigL).Value = ""
'notnettedPoint = smallL + 1
'--------------------------For Validation --------------------------------------
Sheets("Validation").Range("A" & vali_Row_Ct).Value = Sheets("Short_Re").Range("B" & smallL).Value
Sheets("Validation").Range("B" & vali_Row_Ct).Value = Sheets("Long_Re").Range("B" & bigL).Value
Sheets("Validation").Range("I" & vali_Row_Ct).Value = Sheets("Short_Re").Range("L" & smallL).Value
Sheets("Validation").Range("J" & vali_Row_Ct).Value = Sheets("Long_Re").Range("L" & bigL).Value
Sheets("Validation").Range("M" & vali_Row_Ct).Value = Sheets("Short_Re").Range("AC" & smallL).Value
Sheets("Validation").Range("N" & vali_Row_Ct).Value = Sheets("Long_Re").Range("AC" & bigL).Value
vali_Row_Ct = vali_Row_Ct + 1
'-------------------------------------------------------------------------------
Exit For
End If
End If
End If
Else
If ((longNotional - shortNotional) > 0) Then
'Sheets("Short_Re").Range("Z" & smallL).Value = "N"
'notnettedPoint = smallL + 1
Else
'Sheets("Long_Re").Range("Z" & bigL).Value = "N"
Exit For
End If
End If
Else
'notnettedPoint = smallL + 1
End If
End If
Next
Next
'--------------------------------------------------------------------------------------
' Data needs to be moved from the Long and Short tabs to the Result tab
'--------------------------------------------------------------------------------------
Sheets("Long_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A2").Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Rows("2:2").Select
Selection.Delete shift:=xlUp
Sheets("Short_Re").Select
Range("A1:AA1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=26, Criteria1:="N"
'Range(Selection, Selection.End(xlToRight)).Select
Range("A1:AA1").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Result").Select
Range("A65536").Select
Selection.End(xlUp).Select
Selection.Offset(1, 0).PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
Range("Z2").Select
row_delete = Selection.End(xlDown).Row + 1
If (row_delete >= 65536) Then
row_delete = 2
End If
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Application.CutCopyMode = False
Selection.Cut
Rows("1:1").Select
ActiveSheet.Paste
Cells(row_delete, 1).Select
Range(Selection, "AB" & row_delete).Select
Selection.Delete shift:=xlUp
Rows("1:1").Select
Selection.Font.Bold = True
Range("A1").Select
'----------------------------------------Validation Sheet Checks ----------------------------
Sheets("Validation").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("D2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],Input!C2:C24,23,0)"
Range("E2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-5],Input!C2:C10,9,0)"
Range("H2").Select
ActiveCell.FormulaR1C1 = _
"=DATE(LEFT(RC[-2],4),MID(RC[-2],5,2),RIGHT(RC[-2],2))-DATE(LEFT(RC[-1],4),MID(RC[-1],5,2),RIGHT(RC[-1],2))"
Range("H3").Select
Range("C2:H2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("C2:H" & vali_Row_Ct - 1)
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(ABS(RC[-2]-RC[-1])<0.15,""Ok"",""Check"")"
Range("L2").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-11],Input!C[-10]:C[2],13,0)-VLOOKUP(RC[-10],Input!C[-10]:C[2],13,0)"
Range("K2:L2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("K2:L" & vali_Row_Ct - 1)
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>365,IF(ABS(RC[-1]-RC[-2])<31,""ok"",""check""),if(rc[-1]>30,IF(ABS(RC[-1]-RC[-2])<8,""Ok"",""Check""),IF(RC[-1]=RC[-2],""Ok"",""Check"")))"
Range("O2").Select
If vali_Row_Ct > 3 Then Selection.AutoFill Destination:=Range("O2:O" & vali_Row_Ct - 1)
ActiveSheet.Calculate
Range("A1").Select
'----------------------------------Validation in results sheets ------------------------------
Application.ScreenUpdating = True
Sheets("Result").Select
Range("A1").Select
fin_row = Selection.End(xlDown).Row
Range("Z1").Select
ActiveCell.FormulaR1C1 = "PV01 Orig"
Range("AA1").Select
ActiveCell.FormulaR1C1 = "PV01 Check with Orig"
Range("AB1").Select
ActiveCell.FormulaR1C1 = "Check for inclusion of netted deals"
Range("AC1").Select
ActiveCell.FormulaR1C1 = "Zone Orig"
Range("AD1").Select
ActiveCell.FormulaR1C1 = "Zone Check"
Range("Z2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-24],Input!C[-24]:C[-2],23,0)"
Range("AA2").Select
ActiveCell.FormulaR1C1 = "=RC[-2]-RC[-1]"
Range("AB2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(VLOOKUP(RC[-26],Validation!C[-27],1,0)),""Ok"",""Check"")"
Range("AB3").Select
Range("AC2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-27],Input!C[-27]:C[-6],22,0)"
Range("AD2").Select
ActiveCell.FormulaR1C1 = "=RC[-6]=RC[-1]"
Range("Z2:AD2").Select
Selection.AutoFill Destination:=Range("Z2:AD" & fin_row)
ActiveSheet.Calculate
Range("A1").Select
End Sub
Subscribe to:
Posts (Atom)