Doing A Security Reconciliation in Excel

[fmaresca]

I am a bit of a novice on Excel, so if you had covered this before, please indulge me.

I have access to two Excel files (both from different sources), assuming I saved them in the same spreadsheet (workbook) under their own sheet, how would I write a macro to do the following: Look-up by security identifer (either a symbol, CUSIP or other identifer) on one sheet then find it on the other sheet, once done, I would be comparing the number of shares (or par) amount, market price per share from one sheet to the other. Ideally the macro would create a third sheet displaying exceptions, showing the security identifer, share (or par) difference and/or market price per share difference for further investigation.

Any thoughts on what Excel functions would be best suited for Data Lookup and then comparison? Assume all data for each security is identified in a row and each sheet would have the columns arranged the same way (the securities in each row, may have been sorted differently, so the first security on one sheet, may or may not be the first security on the next sheet). There are about 300 individual securities to be compared (off the two criteria indicated).

Thanks

 

[chef]

not sure if this is what you are looking for but...re security in a company...you can ask your computer department to set security rights for named users...down to read only or able to save changes..

on a personal level you can protect your worksheet..formula etc in various ways..post back if you want more info

regards
chef

 

[Jack in the UK]

Hi

I have avoided this feed as it will lead to all sorts of questions but what i will say is the post has been made il add a few bits, the main issue to me is securing data in this care a workbook

I will and always do SHOUT excel security is so bad its shocking!! I personally would not bother wasting your time protecting a document as i will un pick it and nothing will take that long to access.

There are a few protections

VBA modules password protection up to 15 characters [string] which i can always open in less than the time it takes you to type the password, The protect workbook is a shocker and takes me even less time to open.

All in all its very poor so be aware of this, and remember i can get in, with some special methods i have, Jack is just making you think things are not that safe.

Like a bank forgetting to lock the door at night – someone will remove the money!!

The harder one is the document protection so you cant open it, this takes time to open but I can always do that to when I need to.

XP is better but version don’t matter, that are all the same at the end of the day

>>>> Before I get request to open document I answer polity no sorry I do not offer this service, WHY ??? I do not know what your up to and why you want the document un protected, most are work documents so sorry im sure you understand Jacks point – could be a doggy document

 

[dk]

Hi,

I think you may be able to get away with just using formulas, in particular VLOOKUP.

Consider this :-

Book1
	A	B	C	D	E
2	CUSIP/Symbol	Numberofshares	MarketPriceperShare	Comparision-#ofshares	Comparison-Marketprice
3	612044107	2,450	203.15	0	0
4	612044206	4,730	54.82	0	0
5	612044305	4,679	20.89	0	1.01
6	612044503	3,190	235.18	0	0
7	612044529	207	147.99	0	0
8	612044537	9,271	265.52	-745	0
9	612044545	38	208.38	0	0
10	612044552	8,604	281.17	0	0
11	612044560	9,113	194.12	0	0
12	612044586	6,043	237.44	3,782	0
13	612044594	2,079	54.45	0	0
14	612044610	9,105	9.14	0	0
15	612044628	3,952	156.15	0	0
16	612044636	5,512	280.52	-4,256	0
17	612044644	4,915	286.4	0	0
18	612044651	8,904	259.46	0	0
Sheet1

It assumes there are 2 worksheets, both with the same layout for columns A, B and C. However, for some CUSIP numbers the values for number of shares and share price are different. Look at the formula in D8 -

=VLOOKUP(A8,Sheet2!$A$3:$C$18,2,FALSE)-B8

This is saying, take the value in A8 (CUSIP number we're working with), then go to sheet 2, look for that CUSIP number in the first column of the range specified ($A$3:$C$18) and return the value found in the second column. The FALSE part tells Excel that the data in sheet2 isn't necessarily sorted and to find an exact match. The -B8 subtracts the value of Sheet1. If the result of the formula is not zero then there are differences for that CUSIP between the two sheets.

Once you had your data in this way then you could use Autofilter to filter out the zeros and just show data where there are differences between the two sheets.

I hope that's clear (and that I've understood your question)! I think your first step should be to take a look at VLOOKUP in the Excel help files. If you have any specific questions then please ask.


_________________<font face="Impact">Hope this helps,
Dan</font>
This message was edited by dk on 2002-10-19 19:01

 

[stevebausch]

Next time, use the term "Stocks and bonds"

 

[fmaresca]

Dan,

Thanks for your reply, it appears that you understood my request and your response should put me on the right track. I will try it out and let you know if I need further assistance.

 

[fmaresca]

Well I tried Dan's very good suggestion and it only seems to work if the CUSIP is a number. For some reason if it is Alphanumeric (or just Alpha), it will not work (as illustrated).

Any other suggestions would be greatly appreciated.

 

[fitzroy]

fmaresca,
I have a colleague who has designed a similiar spreadsheet already. It is basically security level reconciliation between two sets of data based on CUSIP /ISIN/SEDOL/Bloomberg Ticker matching criteria. As he has spend considerable amount of time developing this I don't think it would be appropriate to share the spreadsheet, but I can put you in touch with him or make suggestions/solutions for problems that we ran into.
Pls let me know if you're still working on this.