Populating Null data with Next Available Record

[Kimbertha]

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 employee stock vesting dates for the same period.

The employee 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 employee 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

[ssanfu]

Hi Kim,

Create a UDF (User defined function) that returns the trade date from the NYSE Trades table and use it in a query.


In a standard module, paste this code;

Public Function GetTradeDate(pDate As Date)
   Dim R As DAO.Recordset
   Dim sSQL As String
   GetTradeDate = Null
   sSQL = "SELECT TOP 1 NYSETrades.NYSE_ID, NYSETrades.TradingDate"
   sSQL = sSQL & " FROM NYSETrades"
   sSQL = sSQL & " WHERE  NYSETrades.TradingDate >= #" & pDate & "#;"
   Set R = CurrentDb.OpenRecordset(sSQL)
   If Not R.BOF And Not R.EOF Then
      R.MoveFirst
      GetTradeDate = R!TradingDate
   End If
   R.Close
   Set R = Nothing
End Function

The query would look like this:

SELECT TOP 1 Vesting.EmpID, Vesting.VestDate, GetTradeDate([vestdate]) AS TradeDate
FROM Vesting;


You will have to use your field names and add any other fields you need in the query.

Without your field names and some sample data, this is as close as I can get with the code.

[Kimbertha]

Hello Steve:

I tried to insert your query syntax and I keep gettng errors. I must be doing something wrong when I edit the names.

Here are examples of the actual tables and field names.

VestingVesting D1/1/20001/2/20001/3/20001/4/20001/5/20001/6/20001/7/20001/8/20001/9/20001/10/2000

Here is an example of the Yahoo Finance table:

Yahoo FinanceDateAdj Close1/3/200041.11/4/200040.711/5/200039.771/6/200039.271/7/200041.621/10/200046.191/11/200044.581/12/200047.11/13/200047.61/14/200051.811/18/200052.19


I also posted my question in Programming here https://www.accessforums.net/program...cord-8258.html

and as you can see, I put together a functional but clumsy solution. If I could get your sytax to work, that would be so much better!

Thanks for reading my post.

Kim

[ssanfu]

Kim,

I think I have parsed the example correctly. I created a form that displays the trade date based on the vesting date. There is a column in the forms recordsource query that has a function to return a date if it is in the table "Yahoo Finance". BTW, in my example, I removed any spaces in the object names, so "Yahoo Finance" is "YahooFinance".


Attached is an example database (A2K3 format)....

[weekend00]

If I understood you well, a simple query can do it:

select vestingTable.vestingDate, min(tradingTable.tradeDate) from vestingTable inner join tradingTable on vestingTable.vestingDate<=tradingTable.tradingDate group by vestingTable.vestingDate

[Kimbertha]

Hi weekend00:

Your idea turned out to be the solution! Hip hip hurrah!

select vestingTable.vestingDate, min(tradingTable.tradeDate) from vestingTable inner join tradingTable on vestingTable.vestingDate<=tradingTable.tradingDate group by vestingTable.vestingDate

Many thanks to you and also to ssanfu for taking the time to share your expertise.

Kim