Does anyone know an excel macro for trade reconciliation. The two trades are located on two different sheets. For example: Sheet1 Col A Col B Col C Col D Col E Col F ISIN B/S TD SD Amount Price Row 1 Ch0XXX B 8.7.10 11.7.10 6000 15,9 Row 2 US0xxx S 2.7.10 5.7.10 500 12 Row 3 XXx989 S 4.7.10 7.7.10 60 19 Row 4 DT3243 B 9.7.10 13.7.10 50 13 Sheet2 Col A Col B Col C Col D Col E Col F ISIN B/S TD SD Amount Price Row 1 Ch0XXX B 8.7.10 11.7.10 6000 15,9 Row 2 US0xxx S 2.7.10 5.7.10 450 12 Row 3 XXx989 S 4.7.10 7.7.10 60 16 Row 4 DT3243 B 9.7.10 13.7.10 50 13 The macro should loop through the two excel sheets and highlight the cells in red that don’t match with each other, in this case the cell(Row 2 and Column E) and the cell(Row 3 and Column F) and copy and paste the two trades(Row 2 and Row 3) to Sheet3. Any help is highly appreciated
I’d probably just do conditional formatting in combination with a vlookup. Not sure it would work, but I don’t see why it wouldn’t. It wouldn’t copy/paste to a new sheet, but it should highlight where the differences are. Then again, this should be a pretty basic macro to create.
Thank you very much for the quick reply. The reason why I want to create a macro for this is to make it easy enough for non experience excel user to do an automatic trade reconciliation by clicking on a userform button instead of having to mark all the columns and using excel function. Does anyone know how to do this with an excel macro?
You’d have to write code. I could do it, but don’t have time now. It’s pretty easy. You should be able to do it yourself and it will be very satisfying. In the code you will: 1) select sheet1. 2) count the total number of rows (rowMax) and columns (colMax) 3) for each row on sheet 1 after the header row, select the ticker (in column 1, row = 2 to rowMax) look up the ticker on sheet 2 4) once you’ve identified the row on sheet2 for your ticker, just compare column by column doing if tests cell by cell looking for differences. For example you might preset a counter to 1. Then for each comparison between sheet1 and sheet2, if you get a match, multiply counter by 1, if no match, multiply by 0. The idea is that at the end of the row, if counter = 1 then all entries have matched and you’ve reconciled, else, you’ll then select the row and color it red. also select that row and paste to sheet 3 5) continue incrementing row until you have rowMax. That’s about it.
Assuming you have 3 sheets labeled “Sheet1”, “Sheet2”, and “Sheet3”: Sub Reconciliation() perfectmatch = “Yes” rowmax = Application.WorksheetFunction.CountA(Columns(“A:A”)) 'Define Sheet 1 Elements Sheets(“Sheet1”).Select For i = 2 To rowmax ISIN = Cells(i, 1) BS = Cells(i, 2) TD = Cells(i, 3) SD = Cells(i, 4) AMT = Cells(i, 5) Price = Cells(i, 6) 'Check for ISIN on Sheet 2 ISINcheck = Application.WorksheetFunction.CountIf(Sheets(“Sheet2”).Columns(“A:A”), ISIN) If ISINcheck = 0 Then Cells(i, 7).Value = “Unable to find ISIN on Sheet 2” Else 'Find correct ISIN row Sheets(“Sheet2”).Select ISINrow = Application.WorksheetFunction.Match(ISIN, Columns(“A:A”), 0) 'Check Sheet 2 Elements If Cells(ISINrow, 1) <> ISIN Then Cells(ISINrow, 1).Font.Color = -16776961 perfectmatch = “No” End If If Cells(ISINrow, 2) <> BS Then Cells(ISINrow, 2).Font.Color = -16776961 perfectmatch = “No” End If If Cells(ISINrow, 3) <> TD Then Cells(ISINrow, 3).Font.Color = -16776961 perfectmatch = “No” End If If Cells(ISINrow, 4) <> SD Then Cells(ISINrow, 4).Font.Color = -16776961 perfectmatch = “No” End If If Cells(ISINrow, 5) <> AMT Then Cells(ISINrow, 5).Font.Color = -16776961 perfectmatch = “No” End If If Cells(ISINrow, 6) <> Price Then Cells(ISINrow, 6).Font.Color = -16776961 perfectmatch = “No” End If Cells(ISINrow, 7).Value = perfectmatch Sheets(“Sheet1”).Select perfectmatch = “Yes” End If Next i 'Create Sheet 3 Sheets(“Sheet2”).Select Cells.Copy Sheets(“Sheet3”).Select Range(“A1”).PasteSpecial Range(“G1”) = “Delete?” deletecheck = Application.WorksheetFunction.CountIf(Columns(“G:G”), “No”) If deletecheck = 0 Then Cells.Clear Range(“A1”).Value = “No Discrepancies” Else Range(“G1”).Select Application.CutCopyMode = False Selection.AutoFilter ActiveSheet.Range("$A$1:$G$65000").AutoFilter Field:=7, Criteria1:=“Yes” Rows(“2:65000”).Delete Selection.AutoFilter End If Columns(“G:G”).Delete Sheets(“Sheet2”).Select Columns(“G:G”).Delete Sheets(“Sheet3”).Select End Sub
^^^ Also assumes that the ISIN is a unique identifier. If you trade more than one ISIN, the Macro won’t work and the required Macro becomes a little more complex.
Golden_Slacks thank you very much. I really appreciate this.
Hi Golden_Slacks! What books would you recommend to read to learn how to write macros? Any advice is appreciated, Alex.
This is clearly a topic for the back office.
Golden_Slacks Wrote: ------------------------------------------------------- > ^^^ Also assumes that the ISIN is a unique > identifier. If you trade more than one ISIN, the > Macro won’t work and the required Macro becomes a > little more complex. Does anyone know how to rewrite this macro to make it work if you trade more than one ISIN(to reconcile trades with the same ISIN on the excel sheet)? Thanks in advance
Are we assuming the sheet is formatted in the same way? I mean both sheets?
Sub Macro2() Dim batman As String Dim robin As String Dim trades As Integer traders = 0 For i = 1 To 4 batman = Sheets(1).Cells(i, 1).Value & Sheets(1).Cells(i, 2).Value & Sheets(1).Cells(i, 3).Value & Sheets(1).Cells(i, 4).Value & Sheets(1).Cells(i, 5).Value & Sheets(1).Cells(i, 6).Value robin = Sheets(2).Cells(i, 1).Value & Sheets(2).Cells(i, 2).Value & Sheets(2).Cells(i, 3).Value & Sheets(2).Cells(i, 4).Value & Sheets(2).Cells(i, 5).Value & Sheets(2).Cells(i, 6).Value If batman = robin Then Else MsgBox “batgirl” End If Next i End Sub You have the other code. Aka use Golden_Slacks to fix the above to do what you need.
The problem you may face is that what if you have the below trade in sheet 1: XXx989 S 4.7.10 7.7.10 60 19 but the below two trades in sheet 2: XXx989 S 4.7.10 7.8.10 60 19 XXx989 S 4.7.10 7.7.10 60 16 What would you want the Macro to do?
One Possible solution (and this will be the last one–sorry just don’t have the time) Sub Reconciliation() Sheets(“Sheet1”).Select sheetonerows = Range(“A1”).CurrentRegion.Rows.Count Cells.Copy 'Find All perfect matches Sheets(“Sheet3”).Select Range(“A1”).PasteSpecial Range(“G1”).Value = “Key” Range(“G2”).Value = “=A2 & B2 & C2 & D2 & E2 & F2” Range(“G2”).Copy Range(“G2:G” & sheetonerows).PasteSpecial Sheets(“Sheet2”).Select sheettworows = Range(“A1”).CurrentRegion.Rows.Count Range(“G1”).Value = “Key” Range(“G2”).Value = “=A2 & B2 & C2 & D2 & E2 & F2” Range(“G2”).Copy Range(“G2:G” & sheettworows).PasteSpecial Sheets(“Sheet3”).Select Range(“H1”).Value = “Matched?” Range(“H2”).Value = “=IF(COUNTIF(Sheet2!G:G,G2)=1,”“Matched”","""")" Range(“H2”).Copy Range(“H2:H” & sheetonerows).PasteSpecial Columns(“H:H”).Copy Columns(“H:H”).PasteSpecial xlValues Sheets(“Sheet2”).Select Range(“H1”).Value = “Matched?” Range(“H2”).Value = “=IF(COUNTIF(Sheet3!G:G,G2)=1,”“Matched”","""")" Range(“H2”).Copy Range(“H2:H” & sheettworows).PasteSpecial Columns(“H:H”).Copy Columns(“H:H”).PasteSpecial xlValues ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Clear ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Add Key:=Range(“H2:H” & sheettworows), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Sheet2”).Sort .SetRange Range(“A1:H” & sheettworows) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Columns(“G:G”).Delete Sheets(“Sheet3”).Select ActiveSheet.Range("$A$1:H" & sheetonerows).AutoFilter Field:=8, Criteria1:=“Matched” Rows(“2:65000”).Delete Selection.AutoFilter 'Find all 1 field breaks Columns(“G:H”).Delete Range(“G1”).Value = “Notes” i = 2 Do Until Range(“A” & i) = “” ISIN = Cells(i, 1) BS = Cells(i, 2) TD = Cells(i, 3) SD = Cells(i, 4) AMT = Cells(i, 5) Price = Cells(i, 6) Sheets(“Sheet2”).Select rowcheck = 2 Do Until Range(“G” & rowcheck) = “Matched” If Cells(rowcheck, 1) <> ISIN Then errorcol = errorcol & “1” End If If Cells(rowcheck, 2) <> BS Then errorcol = errorcol & “2” End If If Cells(rowcheck, 3) <> TD Then errorcol = errorcol & “3” End If If Cells(rowcheck, 4) <> SD Then errorcol = errorcol & “4” End If If Cells(rowcheck, 5) <> AMT Then errorcol = errorcol & “5” End If If Cells(rowcheck, 6) <> Price Then errorcol = errorcol & “6” End If If Len(errorcol) = 1 Then If Cells(rowcheck, 7) = “” Then Cells(rowcheck, 7) = “1 possible match found” Else mynote = Left(Range(“G” & rowcheck), 1) + 1 & " possible matches found" Cells(rowcheck, 7) = mynote End If Cells(rowcheck, 8) = errorcol End If errorcol = “” rowcheck = rowcheck + 1 Loop matchcount = Application.WorksheetFunction.CountA(Columns(“H:H”)) If matchcount = 0 Then Columns(“H:H”).ClearContents Sheets(“Sheet3”).Select Cells(i, 7) = “From Sheet1 with no possible matches” Else If matchcount = 1 Then Range(“H1”).End(xlDown).Select myhighlight = Cells(ActiveCell.Row, 8) myvalue = Cells(ActiveCell.Row, myhighlight) Columns(“H:H”).ClearContents Sheets(“Sheet3”).Select Cells(i, myhighlight).Font.Color = -16776961 Cells(i, 7).Value = “Sheet2 Value: " & myvalue Else ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Clear ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Add Key:=Range(“H2:H” & sheettworows), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal ActiveWorkbook.Worksheets(“Sheet2”).Sort.SortFields.Add Key:=Range(“G2:G” & sheettworows), _ SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal With ActiveWorkbook.Worksheets(“Sheet2”).Sort .SetRange Range(“A1:H” & sheettworows) .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With Rows(“2:” & matchcount + 1).Copy Sheets(“Sheet3”).Select Rows(i + 1).Insert Cells(i, 7).Value = matchcount & " possible matches from Sheet2 below:” Rows(i + 1 & “:” & i + matchcount).Select With Selection.Font .ThemeColor = xlThemeColorAccent5 .TintAndShade = -0.249977111117893 End With For n = i + 1 To i + matchcount Cells(n, 7).Clear myhighlight = Cells(n, 8) Cells(n, myhighlight).Font.Color = -16776961 Cells(n, 8).Clear Next n Sheets(“Sheet2”).Select Columns(“H:H”).ClearContents Sheets(“Sheet3”).Select i = i + matchcount End If End If Rows(i + 1).Insert i = i + 2 Loop 'Add unmatched from Sheet 2 Sheets(“Sheet2”).Select For j = 2 To sheettworows If Range(“G” & j) = “” Then Range(“G” & j) = “From Sheet2 with no possible matches” End If Next j nomatch = Application.WorksheetFunction.CountIf(Columns(“G:G”), “From Sheet2 with no possible matches”) > 0 If nomatch = True Then ActiveSheet.Range("$A$1:G" & sheettworows).AutoFilter Field:=7, Criteria1:=“From Sheet2 with no possible matches” Rows(“2:65000”).Copy Sheets(“Sheet3”).Select Range(“A” & i).PasteSpecial Do Until Range(“A” & i) = “” Rows(i + 1).Insert i = i + 2 Loop Sheets(“Sheet2”).Select Selection.AutoFilter Cells.Columns.AutoFit Sheets(“Sheet3”).Select Cells.Columns.AutoFit End If Sheets(“Sheet3”).Select Range(“A1”).Select End Sub
Try it out with this data: Sheet1: ISIN B/S TD SD Amount Price Ch0XXX B 8.7.10 11.7.10 6000 15.9 US0xxx S 2.7.10 5.7.10 500 12 XXx989 S 4.7.10 7.7.10 60 19 DT3243 B 9.7.10 13.7.10 50 13 E46457 B 11.7.09 11.8.09 431 65.4 Sheet2: ISIN B/S TD SD Amount Price XXx989 S 4.7.10 7.8.10 60 19 XXx989 S 4.7.10 7.7.10 60 16 FE5645 S 11.7.09 11.8.09 900 2 XXX767 S 10.14.10 10.15.10 740 5.67 US0xxx S 2.7.10 5.7.10 450 12 Ch0XXX B 8.7.10 11.7.10 6000 15.9 DT3243 B 9.7.10 13.7.10 50 13 This macro will only catch perfect matches and single breaks. It will not catch breaks of two or more. You’ll have to tweak it if you want it to do that. Hope this helps. Golden_Slacks
I wouldn’t use vba for any of this. I would build a few Excel formulas using concatenation and vlookups and be done with it with an “exception-based” report. I just quickly put together something for you that will be a good start (using the initial data example from the first post). Try these initial steps to get everything set up for a report you can use going forward: 1. Sheet 1: Insert a column on column A, so now you have a blank column. 2. Sheet 1. Copy/Paste this in cell A2 and drag down: =B2&C2&D2 3. Sheet 2: Insert TWO columns on column A, so now you have TWO blank columns. 4. Sheet 2: Copy/Paste this in cell A2 and drag down: =C2&D2&E2 5. Sheet 2: Copy/Paste this in cell B2 and drag down: =IF(COUNTIF(Sheet1!A:A,A2)=0,“NOT FOUND”,"") 6. Sheet 1: Copy/Paste this in cell H2 and drag down: =IF(ISNA(VLOOKUP($A2,Sheet2!$A:$M,6,0)),“NOT FOUND”,IF(VLOOKUP($A2,Sheet2!$A:$M,6,0)=E2,"",“SD Unmatched: “&VLOOKUP($A2,Sheet2!$A:$M,6,0))) 7. Sheet 1: Copy/Paste this in cell I2 and drag down: =IF(ISNA(VLOOKUP($A2,Sheet2!$A:$M,7,0)),“NOT FOUND”,IF(VLOOKUP($A2,Sheet2!$A:$M,7,0)=F2,””,“Amount Unmatched: “&VLOOKUP($A2,Sheet2!$A:$M,7,0))) 8. Sheet 1: Copy/Paste this in cell J2 and drag down: =IF(ISNA(VLOOKUP($A2,Sheet2!$A:$M,8,0)),“NOT FOUND”,IF(VLOOKUP($A2,Sheet2!$A:$M,8,0)=G2,””,"Price Unmatched: "&VLOOKUP($A2,Sheet2!$A:$M,8,0))) For the data set you provided, that should give you a warning message for an “Amount” break for your row 2 (sheet’s row 3) and it should have a price break for your row 3 (sheet’s row 4). In addition to notifying you that there’s a break, it will also show what sheet 2 has for its value. On Sheet 2, the formula in column B will show any transactions that are on sheet 2, but not found on Sheet 1. With your data set, there aren’t any breaks on this column. I hope this can possibly help… EDIT: My cell references that I use in the copy/paste info are assuming you have a header row, so keep that in mind.
^^^ would agree with the above. Its a much easier process. However, it is based on the assumption that there are no ISIN, B/S, or TD breaks.
Golden_Slacks Wrote: ------------------------------------------------------- > ^^^ would agree with the above. Its a much easier > process. However, it is based on the assumption > that there are no ISIN, B/S, or TD breaks. I believe those would indirectly be caught since the concatenation that is driving everything (steps 2 and 4 on my list) is a combo of ISIN, B/S and TD. So those would automatically shake out since the vlookups wouldn’t find matches. Also, you can have multiple accounts on this too, and would just need to add that to the concatenation parts so it would be a combo of account, ISIN, B/S and TD. At my old firm I built something similar where the transactions automatically fed in from a SQL database, and the Excel recon was used to check breaks. Similar concept to what I outlined above. When I left, the book had been used for a couple of years and was up to close to 100,000 transactions that had been auto-reconciled. EDIT: golden_slacks. I just used your data set you had provided and the recon found the below breaks. Sheet 1: Unmatched Amount for this line (Sheet 2 had 450): US0xxxS2.7.10 US0xxx S 2.7.10 5.7.10 500 12 Sheet 1: Unmatched SD for this one (Sheet 2 had 7.8.10): XXx989S4.7.10 XXx989 S 4.7.10 7.7.10 60 19 Also, Sheet 2 had the below two trades that sheet 1 did not have (noted by Sheet 2’s column B that has the warning “NOT FOUND”): FE5645 S 11.7.09 11.8.09 900 2 XXX767 S 10.14.10 10.15.10 740 5.67
Makes sense. Thanh1977, you may want to include Account # to your spreadsheet as it will avoid the issues that arise with block trades which may have the same ISIN, B/S, and TD (etc) values. Good call, LukeBBC251.
Why not use Access for that?