Correlations with Missing Prices Across Different Market Calendars

# Correlation Matrix - NaN Values


# Correlation Matrix - NaN Values












I am trying to create a Correlation Matrix of one particular industry (in the consumer goods sector) with around 15 securities across multiple stock exchanges for the year 2020.

However, I have a lot NaN values on my data frame due to holidays such as Berchtolds Day in Switzerland (January, 2nd, 2020), Lunar New Year in Hong Kong (January 27/28, 2020), Thanksgiving (Nov 26, 2020)… or Presidents Day in America (17 Feb, 2020), Christmas in Italia (December 24, 2020), and NYE in Switzerland /Italia (31 December, 2020).

On python, log_returns = np.log(1 + data.pct_change())

When I look at the log_returns.head(), it seems to give NaN for Berchtolds Day on January 2 and 3 2020 since it doesn’t have the previous day. On the log_returns.tail(), Panda is excluding directly NaN values of the other holidays by writing 0.000000.

Should I be worried about the validity of this correlation matrix? Or find all the NaN values and delete them to be sure?

Optional question: Should I annualize the daily returns by * 250 (trading days)? Or * 250 – minus various holidays? : )

Thank you in advance,

## Answer by Dimitri Vulis (score 5, accepted)

https://quant.stackexchange.com/a/60656

Your biggest problem is with computing the pairwise correlations of returns.

Suppose for simplicity that you have 2 assets A and B. For asset A, you have closing prices for all 3 days $t_0$, $t_1$, and $t_2$. If you also have dividends, you calculate A's total return from $t_0$ to $t_1$:

$$R_{A,t_0,t_1}=\frac{P_{A,t_1}+D_{A,t_1}-P_{A,t_0}}{P_{A,t_0}}$$.

and likewise $R_{A,t_1,t_2}$.

But for asset B, you have closing prices for $t_0$ and $t_2$, not for $t_1$. What do you do?

I've actually seen people take all of the following approaches:

- If B has an ADR whose price on $t_1$ is available, and you have the FX rate for $t_1$, then you can back out underlying B's price. (You should test that the underlying B and the ADR are in sync on other days when both are available.)

- interpolate B's price for the missing day, e.g. linearly: $P_{B,t_1}=\frac{P_{B,t_0}+P_{B,t_2}}{2}$.

- assume that $R_{B,t_0,t_1}=R_{B,t_1,t_2}=\sqrt{1+R_{B,t_0,t_2}}-1$

These are all plausible guesses, but you don't know whether it really would have happened if B had a price on $t_1$. If A was very volatile during those two days, so $R_{A,t_0,t_1}$ differed a lot from $R_{A,t_1,t_2}$, then guessing that B behaved differently introduces information that you don't really have.

- allocate $R_{B,t_0,t_2}$ into $R_{B,t_0,t_1}$ and $R_{B,t_1,t_2}$ proportional to $R_{A,t_0,t_1}$ and $R_{A,t_1,t_2}$ or some other asset's returns. This too is a plausible guess... but what if in reality B would have done something else?

- assume that $P_{B,t_1}=P_{B,t_0}$ (i.e. $R_{B,t_0,t_1}=0$, $R_{B,t_1,t_2}=R_{B,t_0,t_2}$), or that $P_{B,t_1}=P_{B,t_2}$. These guesses look even less plausible. Unfortunately, they're also the easiest to program on a computer.

So if we don't want to take the route of making up $P_{B,t_1}$ that we don't know, but instead use 1 fewer day for $A$, what can we do?

The easiest approach to program is to "throw away" $R_{A,t_0,t_1}$, and to correlate $R_{A,t_1,t_2}$ (one day) versus $R_{B,t_0,t_2}$ (two days). It is, however, better to use $R_{A,t_0,t_2}=(1+R_{A,t_0,t_1})(1+R_{A,t_1,t_2})-1$, i.e. ignore $P_{A,t_1}$ and compare the known returns over the same periods. The last one is my favorite.

As a special case, if you don't have B's prices at the beginning of the series, then you have no choice but to ignore A's return before the day when B's returns become available.

Your correlation matrix is not guaranteed to be positive definite. You can get small negative eigenvalues as the consequence of using series of different lengths. if the negative eigenvalues are too large, then you probably need to exclude some of the assets from your matrix. If they are not too large, there are ways to "fix" the matrix so it's positive definite and not too different from the original one.

Also you should consider the fact that you're looking at closes that happen at different times during the day, even if they are from the same day. There may not be much you can do about that, but it would be an improvement to look for intraday prices with the same time stamp.