Excel Macro for trade reconciliation

T

Thanh1977

New member
Joined
Jun 18, 2026
Messages
0
Reaction score
0
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.
 
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.
 
You must log in or register to reply here.
Back
Top