Need a unique gift idea?
A Pastebin account makes a great Christmas gift
SHARE
TWEET

Untitled

a guest Aug 10th, 2018 56 Never
Upgrade to PRO!
ENDING IN00days00hours00mins00secs
 
  1. Public Sub oldcode()
  2.  
  3. Application.ScreenUpdating = False
  4.  
  5. Dim i As Long, thisScen As Long, nRows As Long, nCols As Long
  6.  
  7.  
  8.  
  9. Dim stressWS As Worksheet
  10. Set stressWS = Worksheets("EQ_Shocks")
  11. Unprotect_Tab ("EQ_Shocks")
  12. nRows = lastWSrow(stressWS)
  13. nCols = lastWScol(stressWS)
  14.  
  15. Dim readcols() As Long
  16. ReDim readcols(1 To nCols)
  17. For i = 1 To nCols
  18.     readcols(i) = i
  19. Next i
  20.  
  21. Dim eqShocks() As Variant
  22. eqShocks = colsFromWStoArr(stressWS, readcols, False)
  23.  
  24.  
  25. 'read in database columns
  26. Dim dataWs As Worksheet
  27. Set dataWs = Worksheets("database")
  28.  
  29.  
  30. nRows = lastRow(dataWs)
  31. nCols = lastCol(dataWs)
  32.  
  33. Dim dataCols() As Variant
  34. Dim riskSourceCol As Long
  35. riskSourceCol = getWScolNum("RiskSource", dataWs)
  36.  
  37. ReDim readcols(1 To 4)
  38. readcols(1) = getWScolNum("RiskReportProductType", dataWs)
  39. readcols(2) = getWScolNum("Fair Value (USD)", dataWs)
  40. readcols(3) = getWScolNum("Source Currency of the CUSIP that is denominated in", dataWs)
  41. readcols(4) = riskSourceCol
  42.  
  43. dataCols = colsFromWStoArr(dataWs, readcols, True)
  44.  
  45. 'read in scenario mappings
  46. Dim mappingWS As Worksheet
  47. Set mappingWS = Worksheets("mapping_ScenNames")
  48.  
  49. Dim stressScenMapping() As Variant
  50. ReDim readcols(1 To 2): readcols(1) = 1: readcols(2) = 2
  51. stressScenMapping = colsFromWStoArr(mappingWS, readcols, False, 2) 'include two extra columns to hold column number for IR and CR shocks
  52.  
  53. For i = 1 To UBound(stressScenMapping, 1)
  54.     stressScenMapping(i, 3) = getWScolNum(stressScenMapping(i, 2), dataWs)
  55.     If stressScenMapping(i, 2) <> "NA" And stressScenMapping(i, 3) = 0 Then
  56.         MsgBox ("Could not find " & stressScenMapping(i, 2) & " column in database")
  57.         Exit Sub
  58.     End If
  59. Next i
  60.  
  61. ReDim readcols(1 To 4): readcols(1) = 1: readcols(2) = 2: readcols(3) = 3: readcols(4) = 4
  62. stressScenMapping = filterOut(stressScenMapping, 2, "NA", readcols)
  63.  
  64. 'calculate stress and write to database
  65. Dim thisEqShocks() As Variant
  66.  
  67. Dim keepcols() As Long
  68. ReDim keepcols(1 To UBound(eqShocks, 2))
  69. For i = 1 To UBound(keepcols)
  70.     keepcols(i) = i
  71. Next i
  72.  
  73. Dim thisCurrRow As Long
  74.  
  75. For thisScen = 1 To UBound(stressScenMapping, 1)
  76.  
  77.     thisEqShocks = filterIn(eqShocks, 2, stressScenMapping(thisScen, 1), keepcols)
  78.  
  79.     If thisEqShocks(1, 1) = "#EMPTY" Then
  80.         For i = 2 To nRows
  81.             If dataCols(i, 4) <> "Excel" And dataCols(i, 4) <> "OBI" And (dataCols(i, 1) = "value1" Or dataCols(i, 1) = "value2") Then
  82.                 dataWs.Cells(i, stressScenMapping(thisScen, 3)).value = "No shock found"
  83.             End If
  84.         Next i
  85.     Else 'calculate shocks
  86.         Call quicksort(thisEqShocks, 3, 1, UBound(thisEqShocks, 1))
  87.         For i = 2 To nRows
  88.             If dataCols(i, 4) <> "Excel" And dataCols(i, 4) <> "ITS" And (dataCols(i, 1) = "value1" Or dataCols(i, 1) = "value2" Or dataCols(i, 1) = "value3") Then
  89.                 thisCurrRow = findInArrCol(dataCols(i, 3), 3, thisEqShocks)
  90.                 If thisCurrRow = 0 Then 'could not find currency so use generic shock
  91.                     thisCurrRow = findInArrCol("OTHERS", 3, thisEqShocks)
  92.                 End If
  93.                 If thisCurrRow = 0 Then
  94.                     dataWs.Cells(i, stressScenMapping(thisScen, 3)).value = "No shock found"
  95.                 Else
  96.                     dataWs.Cells(i, stressScenMapping(thisScen, 3)).value = Replace(dataCols(i, 2), "-", 0) * (thisEqShocks(thisCurrRow, 4) - 1)
  97.                 End If
  98.             End If
  99.         Next i
  100.     End If
  101.  
  102. Next thisScen
  103.  
  104.  
  105. Application.ScreenUpdating = True
  106.  
  107. End Sub
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top