View Full Version : Solved: Excel Macro for trade reconciliation
buhay
09-12-2010, 12:48 PM
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
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 450 12
Row 3 XXx989 S 4.7.10 7.7.10 60 15
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
shawnhet
09-12-2010, 07:52 PM
Am I correct in assuming that the ISIN (column A) is a unique identifier of the transaction? That is to say, that there are not multiple trades with the same ISIN on the same sheet. I have attached a sample sheet that allows you to point out the differences btw the two if the ISIN is only listed once on each sheet.
You can copy the formulas I have added onto your sheet1 and Sheet2 from columns G-K(G lets you know if there is a difference in column2, H in column 3 ...) and copy them down through the whole sheet and use the autofilter to find ones that differ, you can then format them or copy them as needed.
Assuming that this is more or less what you are looking for, you can obviously automate this using VBA, but let's see if this is on track first.
Cheers, :)
buhay
09-13-2010, 01:38 PM
Thank you very much. I will try to write a macro and post it as soon as possible.
rhughes
09-20-2010, 05:50 AM
I believe i need to post something in order to access the Macro you wrote......here it is.
Thanks
buhay
09-20-2010, 01:38 PM
As promised here is the macro(though I didn't write it myself) but for some reason it's not perfect. It never loops through all the rows on sheet1 and always leaves the last row unreconciled(please see sample file for further clarification). Does anyone know how to fix it? Thanks in advance
Assuming you have 2 sheets labeled "Sheet1", "Sheet2",
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
End Sub
buhay
09-20-2010, 01:44 PM
I forgot to include the attachement but now it's up
slamet Harto
09-21-2010, 07:42 AM
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
what do you mean with copy and paste the two trades?
from your sample attached, I don;t find any data in the column E and F
Is that mean to compare row by row in each sheet? if so, you can use this code
Sub bla()
Dim i As Long
Dim XRow As Long
XRow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = 2 To XRow
If Sheet1.Range("B" & i) <> Sheet2.Range("B" & i) Then
Sheet1.Cells(i, 2).Interior.ColorIndex = 3
Sheet2.Cells(i, 2).Interior.ColorIndex = 3
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Sheets("Sheet1").Select
End Sub
buhay
09-21-2010, 01:57 PM
thanks, that's exactely what I have been looking for.
The procedure I posted doesn't copy the cells that don't match with each other and paste them to sheet3. If you know such a code that only copy and paste the mistaken rows(with the cells that don't match with each other, you're welcome to post it here.
Thanks again for helping me out
slamet Harto
09-21-2010, 08:06 PM
bla()
Dim i As Long
Dim XRow As Long
XRow = Sheet1.Cells(Rows.Count, 2).End(xlUp).Row
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
End With
For i = 2 To XRow
If Sheet1.Range("B" & i) <> Sheet2.Range("B" & i) Then
Sheet1.Cells(i, 2).Interior.ColorIndex = 3
Sheet1.Cells(i, 2).Offset(0, -1).Resize(1, 2).Copy Sheet3.Cells(i, 1)
Sheet2.Cells(i, 2).Interior.ColorIndex = 3
Sheet2.Cells(i, 2).Copy Sheet3.Cells(i, 3)
End If
Next i
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
Sheets("Sheet3").Select
End Sub
buhay
09-22-2010, 12:24 PM
Thanks a lot mate
slamet Harto
09-22-2010, 12:54 PM
No problem
Please clean this thread by marking as solve. thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.