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

[NTC]

I question whether a formula can determine: "holidays, and other non-trading dates".

If it was just weekend, those are easily identifiable - if one could presume the following Monday was always a trading date. But it isn't.

I think you are going to need a table for "holidays, and other non-trading dates" - let's call that the NoTrade table, and even that is tricky for floating holidays like Thanksgiving...you also have the strange closing just post 9/11 and there were also some other special situations in the last ten years....

You are going to need the NoTrade date and then the NextTrade date in this NoTrade table.

Sometime one must be pragmatic. You might consider running the ~90% that fall on trade dates stand alone - and move the ~10% with null dates off into their own record set to process separately. Then build that NoTrade table as you go thru them. You need to match those with a join on the no trade date and then provide the next trade date.... Probably the big question is whether someone in your organization or in your industry or the stock exchange itself - has already the NoTrade info already in some format that you can import - - that is worth looking for.

Hope this helps a little.

[Kimbertha]

NTC:

Thanks for your response!

I do have a table of the trade dates as this is available from Yahoo! Finance as well as other sources. You just look up the ticker symbol, give the date range, and download the table in a CSV file. The CSV imports nicely into Access.

In my ugly solution, I built a table to address weekends and recurring USA holidays. Using the IIF statement you can say "if day equals Saturday, add 2 days" and so on. Then you can run a second query and the Saturday Vest date will grab the Monday trade date, but only if there was trading on Monday. And you can run subsequent queries and keep flushing out the null trading dates, but after a while you get too many nested IIF statements and Access gives you an error message about exceeding system resources. That is why I don't think this solution is bullet proof.

Anyway, this works up to a point, but still leaves the question of the dates when no trading occurred because nobody bought or sold XYZ Company's stock. Sometimes there was a gap of 4-5 days when nobody traded any stock, and it was even trickier when these dates fell around holiday dates.

I considered manually populate a table to cover the last 10 years (the date that the client wants), but going forward the client want the table/query to "magically" look-up the dates without having to populate the weekends and no trade dates manually.

So that's my task.

I did notice there is a code library on this site. I'll stroll through there and see if I can get some ideas. I think it should be something like a CASE and CASE ELSE statement, but I am very rusty in this area.

Thanks again for your suggestions.

Kim

[NTC]

I will modify my prior advice. Also, I assume that the trade date is valid for all stocks - that it is not stock specific - meaning the exchanges are open or they are not.

Rather than an iif approach, or my prior advice on a NoTrade table - - I would make a TradeDate table that consists of 2 columns. The first is each calendar date. That's it - just the calendar date. The next column is the trade date. In example of this weekend the records would be:

Calendar Trade
9/17/10 9/17/10
9/18/10 9/20/10
9/19/10 9/20/10
9/20/10 9/20/10

With this table your solution becomes very easy. You simply join the vest date to the calendar date - but use the trade date.

[Kimbertha]

NTC:

Thanks for your input!

I did think of a calendar date and trade date, but how to I automatically associate the calendar date with the next available trade date when the trade date for the specific calendar date is null?

The calendar and trade dates go back to 01/01/2000 and will continue into the foreseeable future. The client does not want to manually edit this table each quarter to populate the dates where trade date is null for a given calendar date : (

Kim

[NTC]

there is always a trade date - not a null; per my weekend example. the trade date is the next date of trading and is applied to every calendar date.

Now building/maintaining this TradeDate table is a challenge...(or let me include 'obtaining'.) Floating holidays (easter/thanksgiving i.e.) and special situation closings, preclude any fixed formula approach. Definitely the table approach is simpler & more robust - in fact it really is the only way to go as the formula approach will not work. The challenge is finding as much of that table as possible that can be imported to minimize manual tweaks - vs - building it all yourself.

One can build out to the future because the floating holidays are known - but one never knows about special situations (flash crash) so there is some maintenance that simply must be done as required.

In the end my advice really is to go with a table approach not formula (nested iifs) - but building/obtaining that table is easier said than done. can't help you with that.

hope this helps.

[Kimbertha]

I have a "solution," but it's not as elegant as I would like . . .

First I have two tables:

Table 1: Calendar
Has just one column named Vesting D. Has sequential calendar dates from 01/01/2000 to the 12/31/2020.

Table 2: Yahoo Finance
Has just one column named Date. (Well actually there are other columns with trade volume, open, and closing price, but they are not relevant to the discussion below, so I am ignoring them in this example. Those columns will be used in other calculations once I have the date thing worked out.) The download period from Yahoo! Finance starts on 01/01/2000 and runs through the present day. The client will update this table on a periodic basis to add new trade dates, which is why the Calendar table runs through 2020.

Next I have two queries:

Query 1: Yahoo Date Calc:

SELECT
Calendar.[Vesting D],
[Yahoo Finance].Date,
[Yahoo Finance_1].Date,
[Yahoo Finance_2].Date,
[Yahoo Finance_3].Date,
[Yahoo Finance_4].Date,
[Yahoo Finance_5].Date,
[Yahoo Finance_6].Date,
[Yahoo Finance_7].Date

FROM
(((((((Calendar
LEFT JOIN [Yahoo Finance] ON Calendar.[Vesting D]=[Yahoo Finance].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_1] ON Calendar.[Vesting D]+1=[Yahoo Finance_1].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_2] ON Calendar.[Vesting D]+2=[Yahoo Finance_2].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_3] ON Calendar.[Vesting D]+3=[Yahoo Finance_3].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_4] ON Calendar.[Vesting D]+4=[Yahoo Finance_4].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_5] ON Calendar.[Vesting D]+5=[Yahoo Finance_5].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_6] ON Calendar.[Vesting D]+6=[Yahoo Finance_6].Date)
LEFT JOIN [Yahoo Finance] AS [Yahoo Finance_7] ON Calendar.[Vesting D]+7=[Yahoo Finance_7].Date;

This gives me a query with the original [Calendar].Vesting D and it associates the [Yahoo Finance].Date with [Calendar].Vesting D, [Calendar].Vesting D +1, [Calendar].Vesting D +2, and so on. That's all this query does.

Note that Access 2007 cannot represent the above relationships in Design View, so I only view/edit it in SQL View.

Query 2: Yahoo Date Match

SELECT
[Yahoo Date Calc].[Vesting D],
[Yahoo Date Calc].[Yahoo Finance].Date,
[Yahoo Date Calc].[Yahoo Finance_1].Date,
[Yahoo Date Calc].[Yahoo Finance_2].Date,
[Yahoo Date Calc].[Yahoo Finance_3].Date,
[Yahoo Date Calc].[Yahoo Finance_4].Date,
[Yahoo Date Calc].[Yahoo Finance_5].Date,
[Yahoo Date Calc].[Yahoo Finance_6].Date,
[Yahoo Date Calc].[Yahoo Finance_7].Date,

IIf([Yahoo Finance.Date] Is Not Null,[Yahoo Finance.Date],
IIf([Yahoo Finance_1.Date] Is Not Null,[Yahoo Finance_1.Date],
IIf([Yahoo Finance_2.Date] Is Not Null,[Yahoo Finance_2.Date],
IIf([Yahoo Finance_3.Date] Is Not Null,[Yahoo Finance_3.Date],
IIf([Yahoo Finance_4.Date] Is Not Null,[Yahoo Finance_4.Date],
IIf([Yahoo Finance_5.Date] Is Not Null,[Yahoo Finance_5.Date],
IIf([Yahoo Finance_6.Date] Is Not Null,[Yahoo Finance_6.Date],
IIf([Yahoo Finance_7.Date] Is Not Null,[Yahoo Finance_7.Date],
"Null")))))))) AS [Yahoo Date Match]

FROM [Yahoo Date Calc]
ORDER BY [Yahoo Date Calc].[Vesting D];

This query returns the smallest non-null value for [Yahoo Finance_X].Date, or it returns the word "Null" if I have no values. In the above exercise, I have no null returns, but I suppose I could get a null return if the client's stock had no NYSE trades for a gap greater than 7 days. At this point I'm not certain if there will ever be a non-trading gap longer than 7 days.

The one thing that I like about the above query is that it's easy for the client to review the resulting table and gain comfort that the appropriate trading date is being associated with the vesting date. Ultimately this query will be used query that does some value calculations based on trade dates, thus the dates here need to be solid.

=======

So there's my "solution." It's not as simple and future-proof as I'd like, but it works. If anyone has any suggestions, please chime in. I'm an accountant by trade so I don't always think like a programmer!

Kim