Greetings!
I am working on an Access data base that includes:
1) a table with with NYSE trading dates for a single stock for the last 10 years, and
2) a table with stock vesting dates for the same period.
The vesting dates can occur on weekdays, weekends, holidays, and other non-trading dates. The NYSE dates, in contrast, do not occur on weekends, holidays, and certain other non-trading dates, but do occur on weekdays.
I need to create a mechanism that will look at the Vesting Date in the Vesting table, and return the Trading Date from the NYSE Trades table, and if the date in the NYSE Trades table is Null, then return the next date in the NYSE Trades table that is not null.
For example, if a Vesting Date occurs on Saturday, then return the Trading Date from Monday, or if Monday is a non-trading date (Is Null), then pick Tuesday, and so on. By the way, the trading days don't always follow with traditional banking holidays, and there are even days when no stock is traded for this entity even though it was a regular business day for the NYSE. So I can't rely on simple rules around weekends or holidays.
I am familiar with how to create a query that shows the matches between the two tables. That's easy! But I am struggling with the no matches.
I created a bunch of nested IIF statements in multiple queries that actually gets the job done for now, but it's ugly, hard to QC, and I'm not entirely sure it's bullet proof for future dates.
I am going crazy trying to figure this out. I would appreciate any suggestions!
Regards,
Kim