Compare Submitted Data to Confirmed Data, Show difference if one found

[tstone484]

This is to try to find a quick way of balancing Stock trade books. The placed trades need to be compared against the executed trades across various source systems.

I need to find a way to compare multiple data elements in multidimensional array 1(placed trades) against multidimensional array 2 (confirmed trades). The elements are CUSIP(Unique identifier for the investment instrument), TIME(HH:MM:SS), Side, Price, Quantity and FIX Trace ID.

I need to script something that will find the sent and confirmed orders against eachother and then find the delta between the Price and Quantity for that trade. In the example below you can see that all the data is not in the same format and normalizing it prior to running a script takes most of the advantage out of scripting this.

Any ideas how I could 1)confirm the trades are the same and 2)write the difference in Quantity and Price to the right of the data. Not every trade can be matched so it is not as simple as comparing every 2 rows and looking for the delta. Any help would be much appreciated

Ex:

<TABLE style="WIDTH: 495pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=660><COLGROUP><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 3035" width=83><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4169" width=114><COL style="WIDTH: 32pt; mso-width-source: userset; mso-width-alt: 1572" width=43><COL style="WIDTH: 63pt; mso-width-source: userset; mso-width-alt: 3072" width=84><COL style="WIDTH: 99pt; mso-width-source: userset; mso-width-alt: 4827" width=132><COL style="WIDTH: 73pt; mso-width-source: userset; mso-width-alt: 3547" width=97><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 62pt; HEIGHT: 25.5pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=34 width=83>Trd Cusip</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=114>Original Time</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 32pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=43>B/S</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=84>Orig Par Amount</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 99pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=132>Trd Price/D.R. (dec)</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 73pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=97>Trade Dt</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #d8d8d8; WIDTH: 80pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=107>TRACE FIX Id</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71 height=17>01F032450</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; WIDTH: 86pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl72 width=114>10:17:11</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>S</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; WIDTH: 63pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70 width=84>5000000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>100</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>20110507</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #a5a5a5; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl71>1000001484</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; HEIGHT: 12.75pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=17>01F032450</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69> 10:17:41</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>S-Sell</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>5000000</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>100</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>5/7/2011</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #ffffcc; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67>1000001484</TD></TR></TBODY></TABLE>