Calculating excess returns for every company for every date

[Pepijn Hafkamp]

Dear community,

• I have encountered a problem whilst trying to calculate the excess returns for every company at every time period. I have variables "Returns" and "Rf" (risk free). I want to create a variable: "Exc_returns" for every return of every month for every company (CompanyName). The problem is that my variable "Rf" only ranges from 31-1-1980 to 31-12-2019, whereas I have a lot of companies that that all range within the time span of 31-1-1980 to 31-12-2019, but they vary in when their returns appear. Another way of wording this, I have 480 observations for the risk-free rate (1 observation for every month), but I have 38.284 observations of returns for every company, each spanning a different timeline).

My question: Is there a way calculating the excess return for each company by subtracting "Rf" from "Returns" for every month and with corresponding dates: Example: CompanyName: Amazon, Date: 31-01-2000 --> Return=10% Rf=5% --> Exc_return=5% on 31-01-2000 and then do this for every company in the dataset.

Thanks in advance and please let me know if you need any additional information.

[Joro Kolev]

If your data is in two different datasets, you need to -merge- them by your date.

[Pepijn Hafkamp]

If your data is in two different datasets, you need to -merge- them by your date.

Currently the data is in the same dataset, however that is because I copied the data from one Excel sheet to the other. Should I merge them? How would this command look?