Question Software to find common list entries ?

[diliptimblo]

Given below are samples of trade list Report and a page of a contract note. The contract note is available only in PDF format and Trade list is available in exel and pdf format. What we want to know is which of the entries is common in both the trade list and the contract note. There will be 40 to 50 pages of contract notes and 20 to 30 pages of trade list. Please direct us to a software which will do this
Contract note sample

​

�

Order No.​	Order Time​	Trade No.​	Trade Time​	Security Description​	Quantity Bought for You​	Quantity Sold for You​	Gross Rate Per Security (Rs.)​
Nse F&O
1300000000143138	09:15:28	39754849	09:15:31	HINDALCO OPT 30JUL 20 PE @ 155	0​	8600​	2.80​
1300000000143138	09:15:28	39830928	09:30:16	HINDALCO OPT 30JUL 20 PE @ 155	0​	4300​	2.80​
1300000000143138	09:15:28	39832075	09:30:32	HINDALCO OPT 30JUL 20 PE @ 155	0​	4300​	2.80​
1300000000143138	09:15:28	39824561	09:28:45	HINDALCO OPT 30JUL 20 PE @ 155	0​	4300​	2.80​

Date​	Series​	Type​	Trade No​	Order No​	B/S​	Qty​	Rate​	Brokerage Per Share​	Stk Price​	Srv Tax​	STT​	CTT​	Stmp​	To Chrg​	CESS​	IGST​	SGST​	CGST​	UTGST​	SEBI FEES​	Net Amount​
10/03/2022	BANKNIFTY FUT 31MAR 22	IF			B	500	34327.84	1.71	0.00	0.00	0.00	0.00	343.28	343.28	0.00	0.00	108.14	108.14	0.00	17.16	-17165696.95
10/03/2022	BANKNIFTY OPT 17MAR 22 PE @ 34000	IO/PE			S	500	659.70	0.80	34000.00	0.00	164.93	0.00	0.00	174.82	0.00	0.00	51.73	51.73	0.00	0.33	329006.46
10/03/2022	CUB FUT 31MAR 22	SF			B	68000	123.40	0.00	0.00	0.00	0.00	0.00	167.82	167.82	0.00	0.00	53.04	53.04	0.00	8.39	-8392071.71
10/03/2022	CUB OPT 31MAR 22 PE @ 120	SO/PE			S	68000	3.61	0.00	120.00	0.00	122.66	0.00	0.00	130.01	0.00	0.00	47.20	47.20	0.00	0.25	244568.28
10/03/2022	NIFTY FUT 31MAR 22	IF			B	1000	16508.88	0.82	0.00	0.00	0.00	0.00	330.18	330.18	0.00	0.00	104.01	104.01	0.00	16.51	-16510585.29
10/03/2022	NIFTY OPT 17MAR 22 PE @ 15700	IO/PE			B	500	27.30	0.40	15700.00	0.00	0.00	0.00	0.41	7.23	0.00	0.00	18.65	18.65	0.00	0.01	-13894.95
10/03/2022	NIFTY OPT 17MAR 22 PE @ 16400	IO/PE			S	1000	232.95	0.40	16400.00	0.00	116.47	0.00	0.00	123.46	0.00	0.00	47.11	47.11	0.00	0.23	232215.62
14/03/2022	IBULHSGFIN FUT 28APR 22	SF			B	31000	148.04	0.00	0.00	0.00	0.00	0.00	91.79	91.79	0.00	0.00	28.91	28.91	0.00	4.59	-4589870.39
14/03/2022	IBULHSGFIN OPT 31MAR 22 PE @ 140	SO/PE			S	31000	6.00	0.00	140.00	0.00	93.00	0.00	0.00	98.58	0.00	0.00	26.73	26.73	0.00	0.19	185556.37
14/03/2022	RECLTD FUT 28APR 22	SF			B	60000	125.61	0.00	0.00	0.00	0.00	0.00	150.74	150.74	0.00	0.00	47.59	47.59	0.00	7.54	-7537682.20
14/03/2022	RECLTD OPT 31MAR 22 PE @ 120	SO/PE			S	60000	1.60	0.00	120.00	0.00	48.00	0.00	0.00	50.88	0.00	0.00	22.40	22.40	0.00	0.10	95658.22
15/03/2022	JKCEMENT OPT 31MAR 22 PE @ 2200	SO/PE			S	1750	66.00	0.11	2200.00	0.00	57.75	0.00	0.00	61.22	0.00	0.00	23.50	23.50	0.00	0.12	115134.06
17/03/2022	BANKNIFTY OPT 17MAR 22 PE @ 34000	IO/PE			B	500	0.00	0.00	34000.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
17/03/2022	NIFTY OPT 17MAR 22 PE @ 16400	IO/PE			B	1000	0.00	0.00	16400.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
																					-53007662.48

 

[Ralston18]

What is a "common entry"? What specifically is being searched for occurences in each list?

= = = =

The upper table being, as labeled, the Contract Note list and the lower table being the Trade List (unlabled) - correct?

Overall it appears to me that you are working with many-to-many match ups between different columns/fields in the two lists (tables).

I do not see a common column/field in the tables that could be used to link the tables. That would be the first requirement before any searches for common entries.

Trade No would be a likely candidate but the Trade List does not appear to have any values in that column.

However, not absolutely necessary for just a brute force search that starts with some value (text, date, numeric) in the Contract List and then looks for the a matching value(s) in the Trade List.

Such a search would require identifying the fields (columns) to parse and then do the necessary string comparisons that indicate a match. And possibly some necessary conversions to compare "123.45" as text (.pdf) to "123.45" as a numeric value (Excel .xls)

Any number of ways that that could be done.

There are any number of apps that can be used to compare documents and flag differences. There may be simpler ways to meet the requirements: use a database or perhaps Python.

The Contract Note List appears structured enough that it could be imported into a database table or even Excel. The Trade List (Excel) should easily import into another table in the same date base and linked to Contract List via Trade No..

Given a choice I would use a database. Even if some of the data is from a linked Excel spreadsheet(s).

Especially if and as the number of pages grow in each list. Or the data requires updates and corrections.

What columns/fields are being compared to find matches and when matches are found how are the results to be presented? What fields to show and what is the desired output format?

Provide some examples of the search process and the expected results.

What, if any, software apps have been tried and tested? What worked and what did not work?

Consider doing it yourself and savings the costs of an app and/or licensing.

Overall more information needed.

That all said how much control, if any, do you have in the creation and maintenance of the two lists? A few changes could make the overall comparison process very straightforward and simple.

Just something to think about.