6
$\begingroup$

I have developed an analytic platform in .NET, but I now have to tackle the problem of data cleaning which first starts by finding holes in time series, before actually looking to find inconsistent data.

In the universe I considered, there is no intraday data. I store the last prices each day and month. So, first step, how can I make sure that there are no holes?

  1. I generate a vector of all the dates that should have a point
  2. I check that all these dates have a point in the DB

This is ok for monthly data because you just take the last day of the month.

But what about for daily data? Exchange holidays, bank holidays, country specific holidays, etc now come into play.

How do you handle this in .NET? or even in general.

$\endgroup$

3 Answers 3

8
$\begingroup$

As Freddy mentioned, it's not usually a problem to get the holiday data. But there's a few more caveats:

  • sometimes the market where the security is listed doesn't observe the local holidays
  • sometimes the market does observe the holiday but the security doesn't
  • sometimes it's the other way around
  • not all securities listed on one particular market follow the same rules
  • different market places might offer the same security but with different holiday rules
  • a lot of markets/securities have half-days too (it's like half a holiday)

A lot of these caveats apply to intraday data only, that's why I'm not going into too much detail here, it depends on where your platform will go. But as you can see, it's more of a classifcation problem in the long run.

We solved the "holiday problem" by inverting it, i.e. we keep track of trading days/hours. In our system the basic entity is a session. A market to us is a collection of these sessions. A security on the other hand has an OPOL market (primary listing) and possibly other markets, and for each such listing there's a collection of (possibly overlapping) sessions. If at a given date/time there is an active session for a security on any market, data should be there.

Now, holidays might either affect markets or sessions. If the market is affected no sessions will be active on that day for securities listed on that market. If a session is affected, all securities attributed with that session will stay inactive (for the duration of that session).

So in this system the rules for sessions clearly dictate if there are and how to find holes in the data (and also the opposite: data comes in even though there's no active trading session).

For the programmatical bit: we use bitsets and edge triggers, whenever a session becomes active its bit is set, whenever it goes inactive the bit is cleared. So for any point in time we can immediately say what sessions are active and through the security<->session association we have immediately all the securities affected.

$\endgroup$
1
  • $\begingroup$ good points, very true, whether one has a local calendar is irrelevant if the asset in question trades according to different market hours. Especially currencies' schedule can even depend on the specific broker used, not just for intraday trading hours but even at holidays around Christmas and New Year. $\endgroup$
    – Matt Wolf
    Sep 20, 2012 at 9:07
3
$\begingroup$

You can try using the .Net port of QuantLib. Here is a version of QuantLib with C# bindings. QuantLib has calendars for many countries and some countries have multiple calendars (stocks vs. bonds). It's pretty simple to check if a date is a trading day given a calendar.

This solution will not address some of the points brought up by hroptatyr.

$\endgroup$
1
  • $\begingroup$ upvoted, did not think of that, nice. $\endgroup$
    – Matt Wolf
    Sep 20, 2012 at 9:05
2
$\begingroup$

Simple, just import one of the many publicly available holiday calendars (may need to do some import conversion through Excel or RegEx within .Net) such as

http://www.roboforex.com/analytics/national-holidays/

and store in a custom collection such as List, where Holiday is a class that has members such as "Market", "Date", ...

You then just need to iterate over your historical data and generate a price point if the date is not included in the collection for the specific market in question. Note that you can easily accomplish the look up through Linq. Let me know if you are there and have issues with Linq. Happy to help out.

P.S.: Its obviously much better if you have access to Bloomberg or other analytical databases then you can easily import the data through their APIs.

$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.