Aligning Dividend Dates with Weekly Stock Prices
Summary
The document asks how to include irregularly dated dividends when calculating weekly stock returns over a long history. The proposed workflow currently searches for the closest dividend date for each weekly price date, which is slow. The questioner also reports using pandas as-of merging as a clearer workaround, though they found it slow in their setup.
The answer suggests assigning dates to calendar weeks and matching on the week number, either with a multi-index in Python or by preparing week numbers in a spreadsheet. It notes that week numbering conventions vary and that some systems produce a week 53. This is a practical suggestion rather than a demonstrated performance comparison, and matching on week number alone can confuse weeks from different years unless the year is included. The document does not explain dividend adjustment conventions or how to handle multiple payments within a week, so those choices still need to be defined for a reliable return series.
Key ideas
- Matching dates by calendar week can avoid repeated nearest-date searches.
- Include the year with a week number so dates from different years do not collide.
- Week-number conventions differ, and some calendars include a week 53.
- The answer does not specify how to aggregate multiple dividends within one week.
Tags
Full text
# Efficient computing of stock returns taking dividends into account
# Efficient computing of stock returns taking dividends into account
I have two DataFrames as follows:
Dividends:
```
Ticker1 Ticker2 Ticker3
2018-01-01 NaN NaN 0.39
2018-01-02 0.8 0.73 NaN
2018-01-04 NaN NaN NaN
... ... ... ...
```
Spot price (weekly):
```
Ticker1 Ticker2 Ticker3
2018-01-01 16.95 8.54 21.05
2018-01-08 16.80 9.03 20.56
2018-01-15 16.86 9.52 19.85
... ... ... ...
```
I would like to compute the weekly returns of these stocks (10Y+ historical) while taking into account the dividends. I would have just added the two dataframes and logged the returns but my dates don't line up exactly.
My current solution is to loop through the `DateTimeIndex` of the spot price dataframe and find the one closest to it in the dividend dataframe using `.loc`, and add it if it's not null. While it works, it's very slow even when looping though the underlying numpy arrays instead of the actual dataframe objects.
Hence, my question: is there an efficient way to get the closest last known dividend and add it to my spot price dataframe before computing the returns?
## Temporary workaround
I found a pandas method I didn't know of called pandas.merge_asof, and although it's very slow it produces the expected result in pure Python and improves readability of the code base.
## Answer by amdopt (score 2)
https://quant.stackexchange.com/a/45204
There are two ways of dealing with this:
If you want to keep it all in Python, converting all of your dates in both DataFrames to ISO 8601 format, extracting the week number, and using that week number as a secondary index is easy to do if you are comfortable dealing with a multi-indexed DataFrame.
If changing date formats is a massive headache that will cause all sorts of downstream bugs than you can export your DataFrame to Excel and manipulate it quickly and then send it back into your DataFrame. Within Excel, the `=WEEKNUM()` function, by default, starts Jan 1 of each year as "week 1". You will end up with "week 53's" which you can deal with in any number of ways. However, you will be certain that your week 1 starts on Jan 1 each year. There are other arguments aside from the default which allows you to start week 1 of each year on any day of the week you choose. A further explanation of the Excel WEEKNUM function is here if needed.
Using Excel in this way can be directly through your Python code too if you want to automate it for future use. The xlwings package for Python makes it easy.
Once you have the week number of the dividend, you can match it up with the week number of the spot price — no need to loop through the entire DataFrame.
Hope this helps.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.