# Fastest Way to Create Rectangular Array of TimeSeries Values

A very common problem in finance is how to create a rectangular array of values (price, or returns) for a list of timeseries. The following illustrates one way to do this, but it is very slow: it takes over 12 secs for just two timeseries on this machine and typically one is looking to process lists that comprise hundreds of series.

I have chosen TWTR because it was delisted after being purchased by Elon Musk in Oct 27, 2022, so there are "missing" values after that date. More generally, there may be values missing at the start, in the middle, or at the end of a series.

tsList =
QuantityMagnitude /@
FinancialData[{"AAPL", "TWTR"}, "Return", {2022, 1, 3}]


The code I am using runs as follows:

AbsoluteTiming[
dates = DateRange[DateObject[{2022, 1, 4}],
returnsArray = ReturnsArray[tsList, dates];
Print[Dimensions@returnsArray];
Print[returnsArray[[;; 2]]];
Print[returnsArray[[-2 ;;]]];]


ReturnsArray[tsList_List, dates_] :=
Module[{abstimes, validDates, nseries, returnsArray},
nseries = Length@tsList;
abstimes = AbsoluteTime /@ dates;
returnsArray = ConstantArray[0.0, {Length@dates, nseries}];
Do[validDates =
Intersection[abstimes, AbsoluteTime /@ tsList[[i]]["Dates"]];
returnsArray[[Flatten[Position[abstimes, #] & /@ validDates], i]] =
tsList[[i]][validDates], {i, nseries}];
returnsArray]


This solution is correct, but pretty horrible and terribly slow, because I am amending values in place in an array.

Also, I am "approximating" the dates for the output, generating them using DateRange, which is also slow when generating business days. What we actually want is the longest list of dates from amongst the timeseries. You can do this with Union[#["Dates"]&/@tsList], but that's also slow.

Another issue is that we have to convert dates to AbsoluteTime- often dates look identical when in fact they are not (or MMA thinks they are not), perhaps because of e.g. different TimeZones.

You can't simply extract the values for each series using tsList[[i]][dates], because if timeseries i doesnt have values for some of the dates, MMA will try to interpolate or extrapolate values, rather than returning a constant 0, or Missing[] on those dates. There is no way to stop it extrapolating, as far as I know. You could convert all the time-series to event-series, which don't extrapolate values, but again that's very slow.

Another approach is to create a TemporalData object td=TemporalData[tsList] and then use td=TimeSeriesResample[td,"Intersection"]. But that's even slower.

So I need to find a solution to this general, common problem that ideally is at least an order of magnitude faster. Perhaps using sparse arrays?

Your code takes 5.6 seconds on my computer with Mathematica 13.2. All the numbers in this post will differ from your computer.

It might sound funny but if you just change DateRange to DayRange (which in your case doesn't have any difference), you'll get the result around 165 times faster !! in your example.

We can stop here since the new code run in 0.03 seconds, but let's see how much we can push.

In this post I mentioned in the Association section, that first duplicate entries will be discarded. We can use this behaviour to fill in the gaps with 0 and then Merge them, without much effort on our side.
Since we have time series, we can also get the min and max date to calculate the dates variable.

ClearAll[ReturnsArray2];

(* Use this function if you don't have dates *)
ReturnsArray2[tsList_] :=
IntegerPart @* AbsoluteTime /@
DayRange[MinDate[MinDate /@ tsList], MaxDate[MaxDate /@ tsList],

]

(* Use this function if you have dates *)

associations =
AssociationThread @@ Join[temp0, Transpose[#["Path"]], 2] & /@
tsList;

(* DeleteCases will remove all the days that are not business days *)
DeleteCases[Merge[associations, Join],
Except @ {Repeated[_, {Length @ tsList}]}]

]


ReturnsArray2 accepts two input:

1. is the list of time series
2. [Optional] is the dates variable as integer or all the dates exist in the first argument (if it does not cover all the dates, the result could be in bad form). Could be used for caching

It outputs an association with time (in integer AbsoluteTime form) and values in the form (v1, v2, ...) relate to timeseries1, timeseries2, ... - You can use the Values function to get the array which should work but it's safer to use Values @ KeySort

You can replace 0. in ConstantArray with any value representing a missing value.

There is one assumption, that tsList[[n]]["Path"] that output {{t1,v1}, {t1,v2}, ...}, all the t* are Integer, if they are not, remove the IntegerPart.

### Result

(* For *)
FinancialData[{"AAPL", "TWTR"}, "Return", {2022, 1, 3}];
(* 278 and 206 data points *)

(* ReturnsArray with DateRange:            6.28  *)
(* ReturnsArray with DayRange:             0.039 *)
(* ReturnsArray2:                          0.031 *)
(* TimeSeriesResample[td, "Intersection"]: 0.021 *)


But the real difference will appear if we have more complex input:

(* For *)
FinancialData[{"GOOGL", "MSFT", "AAPL", "TWTR", "GE"}, "Return", {2012, 1, 1}];
(* 4 x 2795 points, 1 x 2258 points *)

(* ReturnsArray with DateRange:            62.65 *)
(* ReturnsArray with DayRange:             1.25  *)
(* ReturnsArray2:                          0.166 *)
(* TimeSeriesResample[td, "Intersection"]: 3.36  *)


So around 380 times faster with the new solution, and 50 times faster with just replacing DateRange with DayRange.

All the tests were done on Mathematica 13.2 on Windows 11 using AbsoluteTime without caching the dates variable.

The slowness of some of Mathematica's calendrical functions was the main reason I re-wrote part of them from scratch. You can read my detailed post on Wolfram Community, and access the code on Github which can probably (haven't test) give you another boost but is limited to U.S. holidays and is not as robust as the built-ins.

• Wow! That's astonishingly good, Ben Commented Feb 12, 2023 at 17:24
• @MMAUser Thanks. I'm sure it can be improved further. Commented Feb 12, 2023 at 17:29