Doing A Security Reconciliation in Excel

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
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
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

chef

Well-known Member
Joined
Jul 10, 2002
Messages
555
Office Version
  1. 365
  2. 2016
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

Well-known Member
Joined
Feb 16, 2002
Messages
3,215
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

MrExcel MVP
Joined
Feb 15, 2002
Messages
2,942
Hi,

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

Consider this :-
Book1
ABCDE
2CUSIP/SymbolNumberofsharesMarketPriceperShareComparision-#ofsharesComparison-Marketprice
36120441072,450203.1500
46120442064,73054.8200
56120443054,67920.8901.01
66120445033,190235.1800
7612044529207147.9900
86120445379,271265.52-7450
961204454538208.3800
106120445528,604281.1700
116120445609,113194.1200
126120445866,043237.443,7820
136120445942,07954.4500
146120446109,1059.1400
156120446283,952156.1500
166120446365,512280.52-4,2560
176120446444,915286.400
186120446518,904259.4600
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

Well-known Member
Joined
May 11, 2002
Messages
810

ADVERTISEMENT

Next time, use the term "Stocks and bonds"
 

fmaresca

New Member
Joined
Oct 18, 2002
Messages
28
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

New Member
Joined
Oct 18, 2002
Messages
28
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

New Member
Joined
Dec 28, 2005
Messages
4
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,885
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top