2
$\begingroup$

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.

Improve this question
$\endgroup$

1 Answer 1

Reset to default
2
$\begingroup$

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.

Improve this answer
$\endgroup$
7
  • $\begingroup$ Hello! Thanks for taking the time to answer. I had tried the ISO week format for secondary indexing and the problem is that creating a Series "ISO week / Year" the last weeks of some years are 1 but their year remains the same. Basically a dividend around Dec 25th 1999 gets assigned "1 / 1999". Also I would like to keep it all in Python : I'm trying to make it as automatic as possible, and would like to avoid spawning unnecessary extra processes. Anyways, I found a pandas method called merge_asof, and although it's slow, it does what I'm looking for. $\endgroup$
    – FranciscoRZ
    Apr 23, 2019 at 8:02
  • $\begingroup$ @FranciscoRZ give the xlwings package a try. You can automate everything you are wanting within your Python code. Lmk if you try and need help. $\endgroup$
    – amdopt
    Apr 23, 2019 at 13:21
  • $\begingroup$ I'm looking at the package docs, and it looks like a Book needs to be spawned when trying to access the Excel functionalities. Is this the case? If everytime I call this method a Workbook is spawned I'm going to kill my CPU. $\endgroup$
    – FranciscoRZ
    Apr 23, 2019 at 14:47
  • $\begingroup$ You can call an existing book...one that does the weeknum change for you! I can post code next week if you don't figure it out. I'm traveling at the moment. $\endgroup$
    – amdopt
    Apr 23, 2019 at 14:50
  • $\begingroup$ Don't worry about it, that's not really what I'm looking for. I don't think a solution involving data transfers between different processes will outperform pandas.merge_asof and it looks like a real hassle if my code reaches production (nightly batches that depend on a Workbook existing or having to be spawned don't seem reliable to me). Given that your answer answers my original question I'll upvote it for now, but leave it open. If nobody comes up with something better I'll accept the answer. Anyways, thanks again, and happy travels! $\endgroup$
    – FranciscoRZ
    Apr 23, 2019 at 14:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.