Wondered if someone was able to provide me a clean excel solution to a problem I am having.
I have downloaded 9 different currency spot prices from 2016 all the way back to 2000 (daily ). However, due to different holidays etc the dates for each column of currencies do not match eachother. Therefore we cannot analyse it apples for apples. I have been told to match all the dates up and essentially whenever there is a gap in the data in one of the currencies to just insert the last days price again.
Can someone please explain what the easiest way to do this is? All the columns have their own dates which do not match with the previous columns.
I think the best way is to just have one date column using the workday function covering the full 16 years, but then what can I insert to make all the other FX columns match like written above?
Then all you need is a list of dates to do the index match. If you are searching across columns as well, then use a second match in the index formula for the heading.
I don’t think there are official FX holidays, just “recommended” holidays that most people will take. If you and I decide to enter an FX trade on January 1st, we can still do it, unlike say, for stocks that must be crossed on an exchange. The other complication is that banks are closed, so we cannot settle cash payments. It’s not a real limitation for forwards though.
That’s in theory anyway. Practically, liquidity is going to be so bad that you are better off waiting until a normal trading day.
Yeah, my point with “What days are holidays in FX??” was that FX is arguably a continuous market that never closes, though there are times when volume may be considerably thinner.
Yet the OP was clearly getting some null data points and attributed them to holidays or something similar. Perhaps it’s when the collection source was on holiday, rather than the FX market as a whole. So if there are holidays in there, Jan 1st is probably the most widely observed day off around the world, so it is the most likely to have null data points.
And if you use rawraw’s suggestion (which is otherwise a good one), then Jan 1st won’t work as a start date, unless you either use Dec 31st as the start date or use some other technique on the first day (like set it equal to the first valid price thereafter).
It’s easy enough to solve, but I have had cases where a null data point in the first slot can propagate down and nullify the whole column (usually when calculating returns rather than levels).
Actually, even fixed income trading has no official holidays - just “SIFMA recommended” holidays. In theory, they can still trade. The problem is no one publishes market rates, so you don’t know the price of anything, and since most market makers are closed, you cannot further hedge rate exposure.
Anyway… I think best practice for OP might be to delete all dates where any fixing is missing. Otherwise, you might come up with false positive correlations if several currencies are interpreted as “unchanged” for multiple days together. Populating the data itself seems like a trivial problem, which is why I have nothing else to say…
That’s a good point, Ohai, though it depends a bit on whether the OP is using correlation and how sensitive their strategy is to small changes in correlation.
Also, if the data is daily, I suspect the effect on correlations is going to be pretty mild. Basically you have New Years’. Easter and Christmas, possibly Armistice day. Four days out of 365 that are likely to be highly correlated because of simultaneous holiday issues. Maybe a fifth if you include Chinese New Year in Asia. Individual pairs that have more holidays in common might be affected more, which is why the sensitivity to changes in correlation is worth knowing.
The value in having a continuous series without a bunch of null values in it at quasi-random intervals may be worth the additional increase in errors.
Another option might be to fill missing values by interpolating them and see if it makes any difference to your results. Comparing results via the three methods (last recorded, interpolated, and leave-as-missing) could be useful in telling you if the biases introduced by those methods are large enough to cause problems, and help you choose a best response among those in the future.
(EDIT: If weekends or sundays count as holidays, then yes, it will likely have a material effect on correlations)