# time series management system

I'm happy how we store a single time series but we somehow lack a system that glues them all together. I'm talking about a few million time series coming from ~50 data vendors and representing maybe a million contracts.

It appears to me that hydrologists(!) have a pretty decent framework (KiTSM) but it takes A LOT of imagination to apply their (GIS-based) system to financial time series.

I imagine something with a comprehensive set of command line tools for batch processing, a neat web interface for tagging and generating custom compilations and maybe something to allow users to subscribe certain compilations, as well as some bindings for the big systems, maybe a matlab/R/octave/SAS/you-name-it plugin.

I'm not particularly fixated on exactly these features, my (daily) work flow in detail:

• schedule data retrieval plans (somewhat like cron jobs) and monitor them, i.e. get a list of time series that haven't been updated this morning
• get all back-adjustments and other corrections to time series
• inform research groups that currently use those time series about corrections
• provide fail-over time series/data vendors on request, e.g. resort to CSI settlement prices when CME's DataMine service is down

Something like this is best given into the hands of the end-users because they probably have a better idea of what might suit their specific needs.

Does a tool like this exist?

-
Well, have you considered vendor solutions? If you have a million series from fifty vendors, something like Kdb or OneTick might be more suitable than a bunch a of binary mmap files on an NFS server... –  Dirk Eddelbuettel Nov 7 '11 at 19:40
well, yes, I've tried Sungard's MarketMap Analytic Platform which in principle is what I want, only that they require my data vendor to support them which is a case of inverted hierarchy, not the data vendor adapts to the tool but the tool needs to adapt to the data vendor. –  hroptatyr Nov 7 '11 at 19:45
As for kdb and onetick, they sort of focus on the data storage aspect more than the management aspect. I've yet to convince Kx to give me a 64bit trial of kdb+taq but I'm pretty sure that the management aspect isn't covered, e.g. I need all versions of a particular time series, not just the current one. –  hroptatyr Nov 7 '11 at 19:51
what about building your own framework? It is not so complex. A relational database with different field per time serie revision will do the last trick –  RockScience Nov 8 '11 at 9:24
@RockScience: I do have my own (shell-based) framework and so far it scales well and the performance is a dream, that said the downside is that the researchers I'm working with aren't exactly shell power users, they need some GUI/web goo. I'm currently trying to (ab)use semantic-scuttle (semanticscuttle.sourceforge.net), their feature set does what I want but their performance is horrendous. –  hroptatyr Nov 8 '11 at 9:41

I like couchdb + couchapp for this. Each timeseries is a doc with a reference to a file somewhere, and you can just update with metadata as you go.

It's nice because all of your web views / interfaces are just a pure JavaScript / HTML + the js map/reduce view. Each one is small and self-contained, and doesn't require a separate app running somewhere.

In addition, you build some views for finding datasets according to your criteria. Everything is REST, so it's easy to wire up an app to query this.

To run a simulation / analysis job, you search for the datasets, pick up the right files, and run. Simulation results can then be stored in couch, with custom views to see results. Because entries are versioned, you can store a reference to a given piece of data, and then if you update it later, future searches pick up the newest version but old results are still valid.

Finally, couch lets you subscribe to an event stream of updates. So you can write something that listens for interesting updates / datasets and notifies the right people very easily.

Couchdb is good for availability but some pure js views are not extremely performant. For those, you can write native erlang map/reduce functions that are faster.

-
Sounds definitely good, I'll look into that. I guess I wasn't after high performance when I asked for a web-based approach. But anything that can do a maintenance job in less than 24 hours every day is enough. I'll report back. –  hroptatyr Nov 24 '11 at 21:28
Ok, here's the report as promised. In essence all the features Dan described are there but it's not tailored to my specific needs, i.e. I had to more or less code a lot, not to say, all of it. I could turn the stuff I've written into some sort of (open-source) project but I'm a C and lisp programmer and so my js code is heavily influenced by that and probably not understandable enough to attract contributors. Also, the web design is something I'd rather clone from someone with experience, my understanding of UX and UI is nothing I should be paid for, to put it mildly. –  hroptatyr Dec 29 '11 at 21:35

Like most software projects, your decision should be based on what your researchers intend to use the data for. Do they need mark-to-market for real-time or end-of-day reports or is it just for statistical analysis? Do they need bid-ask data? Tick data or minute bars? Is the data periodic or aperiodic?

For statistical analysis, the data format should be amenable to S-Plus, R, Matlab, or whatever statistical software is being used. For reports, your data might need to be stored in a column-based RDBMS or a kdb-type database. For simulations, it will depend on what the simulations are written in (C++, Java?) - a binary format may be fastest here, but it wouldn't be very portable to other uses. You may end up storing your data in multiple formats, but then you have to deal with keeping all your data in-sync.

The success of your project (measured by how much your data is used) will totally depend on how accessible your data is to your users. So, as you briefly touched upon, the aptitude of your users towards software/data will factor into your decisions. Talk to all your users to assess their needs - you need to discern their requirements clearly before you design your project into a sub-optimal direction.

-
Well this question is specifically NOT about the data format as such, I want to find a way to manage the sheer number of time series, and not the contents of one time series. It's exactly the question is my data accessible to my users, and at the moment it's not, I'm talking about 20 million time series, of which only, say, 3000 represent e.g. coffee contracts (spot, futures, futures options). I'd like to tag all coffee contracts with the tag coffee and end-of-day spot summaries with, say, eod and spot. That's what I'm looking for. –  hroptatyr Nov 21 '11 at 22:50
Just write to a network/shared drive and store your tag, filepath/name in a RDBMS for easy lookup of the time-series? You could even store those summaries (eod, spot) in the RDBMS or some kind of data warehouse as none of these numbers will change over time (write once, read many). This may seem overly simple at first, but it is actually flexible enough so that if an end-user wants to find, say, all options on US 10Y futures which are still active, they can query the DB for locating the time-series. –  kfmfe04 Nov 22 '11 at 2:17
Yes, I completely agree with you. Though, that's the system we have in place right now and I was looking to turn it into a per-user tag cloud, so users can retag their and other tiny little subsets they're interested in. Also, users should be able to subscribe to a news feed so I can notify them about updates for their data. At one point I was thinking something like this stackexchange webapp could do it, with time series being single questions but now I'm starting my own thing based on what I saw in scuttle and KiTSM. –  hroptatyr Nov 22 '11 at 8:03