Aligning Multi-Market Price Series by Their Shared Dates
Summary
The document describes a data-preparation problem encountered when collecting weekly closing prices for several international equity indices and funds. The returned series have different row counts despite covering the same requested date range. Different exchange calendars, holidays, data-provider conventions, and missing observations can all affect which weekly dates appear, so equal-length vectors cannot be assumed to represent synchronized periods.
For analyses that require aligned observations, the question proposes retaining only dates present in every asset series. This is a practical complete-case approach: matching timestamps before calculating returns or fitting a joint model prevents observations from being paired across different dates. The example’s row-by-row deletion method is inflexible and can also be error-prone when modifying a series during iteration. The document poses the alignment question but does not provide a final recommended function or address alternatives such as calendar reindexing, forward filling, or handling missing values. It also notes a separate concern that prices may need adjustment for corporate actions.
Key ideas
- Price histories from different markets can contain different weekly dates and row counts.
- Joint analyses require aligning observations by their dates rather than assuming rows correspond.
- Keeping only dates shared by every series creates a complete-case dataset for synchronized analysis.
- The example raises corporate-action adjustment as a separate data-quality issue.
- The document does not resolve which missing-data treatment is best for a particular model.
Tags
Full text
# fetch from yahoo! finance database - varying number of ticks
# fetch from yahoo! finance database - varying number of ticks
To test a model with real-life data, I used the fetch-function in matlab to connect to the database of yahoo! finance. My code to try and get 7 different assets' returns is the following:
```
FromDate = '6/1/2001';
ToDate = '12/31/2012';
Period = 'w';
% DAX = ^GDAXI
ASS1 = fetch(yahoo,'^GDAXI','Close',FromDate,ToDate,Period);
% Nikkei 225= ^N225
ASS3 = fetch(yahoo,'^N225','Close',FromDate,ToDate,Period);
% S&P 500 = ^GSPC
ASS5 = fetch(yahoo,'^GSPC','Close',FromDate,ToDate,Period);
% RUSSELL 1000 GROWTH = IWF
ASS6 = fetch(yahoo,'IWF','Close',FromDate,ToDate,Period);
% RUSSELL 1000 VALUE = IWD
ASS7 = fetch(yahoo,'IWD','Close',FromDate,ToDate,Period);
% RUSSELL 2000 GROWTH = IWO
ASS8 = fetch(yahoo,'IWO','Close',FromDate,ToDate,Period);
% RUSSELL 2000 VALUE = IWN
ASS9 = fetch(yahoo,'IWN','Close',FromDate,ToDate,Period);
```
I intentionally took weekly ticks because the indices come from different world regions and SE may be open at some place but closed at another. This period contains 604 weeks and 3 days, according to wolframalpha.com. But now, the length of the vectors are like this:
```
<606x2 double>,
<602x2 double>,
<605x2 double>,
<605x2 double>,
<605x2 double>,
<605x2 double>,
<605x2 double>
```
i.e. the DAX finds 606 weekly ticks in less than 605 weeks, and the Nikkei 225 only 602. Does anybody have an idea what the reason could be - I cannot think of anything else than the stock exchange being closed three times for an entire week in Japan (Earthquake 2011, maybe, but DAX and NIKKEI have an equal number of returns from January 2011 until april of 2012, namely 70.).
It seems I cannot work around this on the core of the yahoo database; just like I had to adjust the Russell Value 2000 index for a 3-for-1 split because I did not get the adjusted price from yahoo.
But more importantly, with an uneven number of ticks and returns, I cannot use any model. I therefore need to remove those rows from an asset where the date does not appear in every other asset (as the fetch function gives a column with the dates along with the ticks). The code I came up with until now is
```
for i=1:size(ASS1(:,1))
if sum(ismember(ASS1(i,1),[ASS3,ASS5,ASS6,ASS7,ASS8,ASS9]))<6;
ASS1(i,:)=[]
end
end
```
to remove row i from asset 1 if it is not in all other Assets. But this is very inflexible with respect to number of assets, and not elegant to run. Is there a function that would help me better?Shown in full with attribution under the source's licence. Licence: CC BY-SA 4.0 (Stack Exchange)
This summary was written by Stratmill's research agent from the original; it is not a copy of the source.