Use Trading Dates and Holiday Calendars to Index Financial Data
Summary
The document shows why subtracting calendar dates does not reliably locate observations in daily financial data. Weekends and market holidays create gaps, so the number of elapsed days can differ from the number of rows between two trading dates. The example uses a dataframe of daily prices and volume to illustrate how this can select an earlier row than the intended month-to-date start.
The proposed approach is to represent the date column as a pandas datetime index and locate the requested dates directly in that index, then use those positions to retrieve corresponding rows. The answer also notes that pandas holiday calendars can be constructed when calendar-aware date handling is needed. Direct lookup fits dates that exist in the data; the supplied example does not explain how to handle requested dates that fall on a non-trading day, missing observations, or multiple markets with different calendars.
Key ideas
- Calendar-day offsets do not match row offsets in daily market data because weekends and holidays are absent.
- A datetime index allows date-based lookup of observations instead of inferring row positions from elapsed days.
- Holiday calendars can be built when trading-calendar-aware calculations are needed.
- Direct date lookup requires a policy for requested dates that are not present in the dataset.
Tags
Full text
# Dealing with weekends/gaps in financial data
# Dealing with weekends/gaps in financial data
My script takes some data from IEX and then outputs a pandas dataframe:
```
date open high low close volume timestamp
148 2018-08-03 206.3064 208.0105 204.7621 207.2631 33447396 736909.0
149 2018-08-06 207.2730 208.5187 206.3463 208.3393 25425387 736912.0
150 2018-08-07 208.5884 208.7678 206.0374 206.3862 25587387 736913.0
151 2018-08-08 205.3299 207.0837 203.8052 206.5257 22525487 736914.0
152 2018-08-09 206.5556 209.0468 206.4758 208.1500 23492626 736915.0
153 2018-08-10 207.3600 209.1000 206.6700 207.5300 24611202 736916.0
154 2018-08-13 207.7000 210.9520 207.7000 208.8700 25890880 736919.0
155 2018-08-14 210.1550 210.5600 208.2600 209.7500 20748010 736920.0
156 2018-08-15 209.2200 210.7400 208.3300 210.2400 28807564 736921.0
157 2018-08-16 211.7500 213.8121 211.4700 213.3200 28500367 736922.0
158 2018-08-17 213.4400 217.9500 213.1600 217.5800 35426997 736923.0
159 2018-08-20 218.1000 219.1800 215.1100 215.4600 30287695 736926.0
160 2018-08-21 216.8000 217.1900 214.0250 215.0400 26159755 736927.0
161 2018-08-22 214.1000 216.3600 213.8400 215.0500 19018131 736928.0
162 2018-08-23 214.6500 217.0500 214.6000 215.4900 18883224 736929.0
163 2018-08-24 216.6000 216.9000 215.1100 216.1600 18476356 736930.0
164 2018-08-27 217.1500 218.7400 216.3300 217.9400 20525117 736933.0
165 2018-08-28 219.0100 220.5400 218.9200 219.7000 22776766 736934.0
166 2018-08-29 220.1500 223.4900 219.4100 222.9800 27254804 736935.0
167 2018-08-30 223.2500 228.2600 222.4000 225.0300 48793824 736936.0
168 2018-08-31 226.5100 228.8700 226.0000 227.6300 43340134 736937.0
169 2018-09-04 228.4100 229.1800 226.6300 228.3600 27390132 736941.0
170 2018-09-05 228.9900 229.6700 225.1000 226.8700 33332960 736942.0
171 2018-09-06 226.2300 227.3500 221.3000 223.1000 34289976 736943.0
172 2018-09-07 221.8500 225.3700 220.7100 221.3000 37619810 736944.0
173 2018-09-10 220.9500 221.8500 216.4700 218.3300 39516453 736947.0
174 2018-09-11 218.0100 224.2990 216.5600 223.8500 35749049 736948.0
175 2018-09-12 224.9400 225.0000 219.8400 221.0700 49278740 736949.0
176 2018-09-13 223.5200 228.3500 222.5700 226.4100 41706377 736950.0
177 2018-09-14 225.7500 226.8400 222.5220 223.8400 31999289 736951.0
```
When indexing this table I use different functions to select for example Week to date or month to date values such as this:
```
def mtd():
today = date.today()
start = date.today().replace(day=1)
delta = today.days-start.days
```
The issue is, that the delta calculation doesn't exclude weekends, meaning my index is off when trying to extract data from this range of dates from the df. For example if I want data for the month of September, my program would calculate `2018-09-17 - 2018-09-01 = 16`, then $177 - 16 = 161$, the value I need get get the first data point in September is $169$. Is there a way to do this also for holidays ?
## Answer by Attack68 (score 1)
https://quant.stackexchange.com/a/41776
Yes you can build holiday calendars using Pandas, see here: Pandas Timeseries Docs
But you don't really need it here, just create a DateTimeIndex from your given column and lookup the specific date in the list:
```
import pandas as pd
df = pd.DataFrame(data=[['2018-08-03', 206.3064],
['2018-08-06', 207.2730],
['2018-08-07', 208.5884],
['2018-08-08', 205.3299]], columns=['date', 'open'])
dt_idx = pd.DateTimeIndex(df['date'])
start_date = '2018-08-03'
today_date = '2018-08-08'
start_idx = dt_idx.get_loc(start_date)
# >>> 0
today_idx = dt_idx.get_loc(today_date)
# >>> 3
print(df.loc[start_idx, 'open']
# >>> 206.3064
print(df.loc[today_idx, 'open']
# >>> 205.3299
```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.