Kentucky FX - tasmia9/VBA GitHub Wiki

Sub currency_calculations()

For Each Sheet In ActiveWorkbook.Worksheets Application.DisplayAlerts = False If Sheet.Name = "Formatted" Then Sheet.Delete End If Next Sheet

For Each Sheet In ActiveWorkbook.Worksheets Application.DisplayAlerts = False If Sheet.Name = "BKRSSTIF" Then Sheet.Delete End If Next Sheet

For Each Sheet In ActiveWorkbook.Worksheets Application.DisplayAlerts = False If Sheet.Name = "BKRSSTPF" Then Sheet.Delete End If Next Sheet

With Sheets(1) .Activate lr = Sheets(1).Cells(Rows.Count, 1).End(xlUp).Row Set r = .Rows(1) scdid = r.Find(What:="SCD Security ID", LookIn:=xlValues).Column scdid1 = .Cells(2, scdid).Address counterparty = .Rows(1).Find(What:="Counterparty", LookIn:=xlValues).Column counterparty1 = .Cells(lr, counterparty).Address broker = scdid1 & ":" & counterparty1

'fix portfolio name ***** port = .Rows(1).Find(What:="PPortfolio", LookIn:=xlValues).Column localvalue = .Rows(1).Find(What:="Sum of Local Accrued Value", LookIn:=xlValues).Column localcurrency = .Rows(1).Find(What:="Local Currency", LookIn:=xlValues).Column baseunrealized = .Rows(1).Find(What:="Sum of Base Unrealized", LookIn:=xlValues).Column basevalue = .Rows(1).Find(What:="Sum of Base Accrued Value", LookIn:=xlValues).Column

' Add columns and headings

lc = Sheets(1).Cells(1, .Columns.Count).End(xlToLeft).Column .Cells(1, lc + 1).Value = "Buy Currency" .Cells(1, lc + 2).Value = "Sell Currency"

buy = .Rows(1).Find(What:="Buy Currency", LookIn:=xlValues).Column sell = .Rows(1).Find(What:="Sell Currency", LookIn:=xlValues).Column

' Calculate Buy and Sell Currency

.Cells(2, lc + 1).Select ActiveCell.FormulaR1C1 = "=IF([@[Sum of Units]]>0,[@[Local Currency]],"""")"

.Cells(2, lc + 2).Select ActiveCell.FormulaR1C1 = "=IF([@[Sum of Units]]<0,[@[Local Currency]],"""")"

Sheets(1).Name = "Raw"

Set ran = .Range(.Cells(2, buy), .Cells(lr, buy)) For Each a In ran buyrow = a.Row b = a.Value If b = "" Then Exit For Next a

buy1 = .Cells(buyrow, buy).Address buycurrency = scdid1 & ":" & buy1

scdid2 = .Cells(buyrow, scdid).Address sell2 = .Cells(lr, sell).Address sellcurrency = scdid2 & ":" & sell2

' Add new worksheet and name as "Formatted"

Sheets.Add After:=ActiveSheet ActiveSheet.Name = "Formatted"

' Copy SCD Security ID column to new worksheet Sheets(1).Activate Sheets(1).Columns(43).Copy Destination:=Sheets("Formatted").Range("A:A") Sheets(2).Activate Columns("A:A").Select ActiveSheet.Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo

'Add more headings

Range("B1").Value = "Broker" Range("C1").Value = "Port" Range("D1").Value = "Buy Currency" Range("E1").Value = "Sell Currency" Range("F1").Value = "Total Amount Buy Currency" Range("G1").Value = "Total Amount Sell Currency " Range("H1").Value = "Unrealized (Base)" Range("I1").Value = "Total Amount Buy (P)" Range("J1").Value = "Total Amount Sell (P)"

'BROKER FORMULA diff = counterparty - scdid + 1 lr1 = Sheets("Formatted").Cells(Rows.Count, 1).End(xlUp).Row Range("B2:B" & lr1).Formula = "=VLOOKUP(A2, Raw!" & broker & "," & diff & " ,FALSE)"

'BUY CURRENCY FORMULA diff1 = buy - scdid + 1 Range("D2:D" & lr1).Formula = "=VLOOKUP(A2, Raw!" & buycurrency & "," & diff1 & " ,FALSE)"

'SELL CURRENCY FORMULA diff2 = sell - scdid + 1 Range("E2:E" & lr1).Formula = "=VLOOKUP(A2, Raw!" & sellcurrency & "," & diff2 & " ,FALSE)"

'PORT FORMULA port1 = .Cells(2, port).Address scdidlastrow = .Cells(lr, scdid).Address port2 = port1 & ":" & scdidlastrow scdid2 = scdid1 & ":" & scdidlastrow Range("C2:C" & lr1).Formula = "=INDEX(Raw!" & port2 & ", MATCH(A2, Raw!" & scdid2 & ",0),1)"

'TOTAL AMOUNT BUY CURRENCY localv1 = .Cells(2, localvalue).Address localv2 = .Cells(lr, localvalue).Address localval = localv1 & ":" & localv2 localc1 = .Cells(2, localcurrency).Address localc2 = .Cells(lr, localcurrency).Address localcur = localc1 & ":" & localc2 Range("F2:F" & lr1).Formula = "=SUMIFS(Raw!" & localval & ", Raw!" & localcur & ", D2, Raw!" & scdid2 & ", A2)"

'TOTAL AMOUNT SELL CURRENCY Range("G2:G" & lr1).Formula = "=ABS(SUMIFS(Raw!" & localval & ", Raw!" & localcur & ", E2, Raw!" & scdid2 & ", A2))"

'Unrealized (Base) baseunr1 = .Cells(2, baseunrealized).Address baseunr2 = .Cells(lr, baseunrealized).Address baseunr3 = baseunr1 & ":" & baseunr2 Range("H2:H" & lr1).Formula = "=SUMIFS(Raw!" & baseunr3 & ", Raw!" & scdid2 & ", A2)"

'TOTAL AMOUNT BUY (P) basevalue1 = .Cells(2, basevalue).Address basevalue2 = .Cells(lr, basevalue).Address basevalue3 = basevalue1 & ":" & basevalue2 Range("I2:I" & lr1).Formula = "=SUMIFS(Raw!" & basevalue3 & ", Raw!" & localcur & ", D2, Raw!" & scdid2 & ", A2)"

'TOTAL AMOUNT SELL (P) Range("J2:J" & lr1).Formula = "=ABS(SUMIFS(Raw!" & basevalue3 & ", Raw!" & localcur & ", E2, Raw!" & scdid2 & ", A2))"

Columns("F:J").Select
Selection.NumberFormat = "#,##0.00_);(#,##0.00)" ' Formatting

' Add new worksheet and name as "Portfolios ____"

Sheets.Add After:=ActiveSheet ActiveSheet.Name = "BKRSSTIF"

With Sheets(2) .Activate lr2 = Sheets(2).Cells(Rows.Count, 1).End(xlUp).Row Set r2 = .Rows(1) scdid3 = r2.Find(What:="SCD Security ID", LookIn:=xlValues).Column scdid4 = .Cells(2, scdid3).Address tsell1 = r2.Find(What:="Total Amount Sell (P)", LookIn:=xlValues).Column tsell2 = .Cells(lr2, tsell1).Address format1 = scdid4 & ":" & tsell2

Columns("A:J").Select
Selection.AutoFilter
ActiveSheet.Range(format1).AutoFilter Field:=3, Criteria1:= _
    "BKRSSTIF - KRS Strategic Fixed Income Insurance Account"

'Filter and copy the entire formatted worksheet to BKRSSTIF

Sheets("Formatted").Select Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("BKRSSTIF").Select Range("A1").Select ActiveSheet.Paste

'REPEAT FOR THE OTHER PORTFOLIO

Sheets.Add After:=ActiveSheet ActiveSheet.Name = "BKRSSTPF"

With Sheets(2) .Activate Columns("A:J").Select Selection.AutoFilter ActiveSheet.Range(format1).AutoFilter Field:=3, Criteria1:= _ "BKRSSTPF - KRS Strategic Fixed Income Pension Account"

'Filter and copy the entire formatted worksheet to BKRSSTPF

Sheets("Formatted").Select

Range("A1").Select Range(Selection, Selection.End(xlDown)).Select Range(Selection, Selection.End(xlToRight)).Select Selection.Copy Sheets("BKRSSTPF").Select Range("A1").Select ActiveSheet.Paste

With Sheets(2) .Activate Columns("A:J").Select Selection.AutoFilter End With

End With End With End With End Sub