Search code examples
pythoncurrency

Convert historical stock prices from Yahoo finance to a different currency with Python

I am trying to adjust the historical stock price of non-euro listed firms to euro. Hence, I would like to convert the stock price of Apple to euro. I have scraped the historical exchange rates as well as the historical adjusted stock prices. I have now two data-frames, the first data-frame shows the currency of the stock and the stock price. I use multiple stocks for my portfolio. I do so by using this code:

assets =  ["^AEX", "AAPL", "AZN.L", "AD.AS ", "ASML.AS"]

.

                     ^AEX   AAPL    AZN.L   AD.AS   ASML.AS    idx
                      EUR   USD     GBP     EUR     EUR         currency
2021-02-02 00:00:00 652.87  134.789 51.46   23.54   457.5       NaN
2021-02-03 00:00:00 654.89  133.74  51.42   23.6    457.15      NaN
2021-02-04 00:00:00 654.38  137.185 50.84   23.46   459.55      NaN
2021-02-05 00:00:00 653.24  136.76  51      23.35   460         NaN
2021-02-08 00:00:00 656.39  135.942 51.48   23.3    467.1       NaN

The second data-frame shows the historical exchange rate data.

       Currency code    Currency name   Units per EUR   EUR per Unit    Date
0   0   USD             US Dollar       1.320339        0.757381    2021-02-03
1   1   EUR             Euro            1.000000        1.000000    2021-02-03
2   2   GBP             British Pound   0.812044        1.231461    2021-02-03
3   3   INR             Indian Rupee    72.359647       0.013820    2021-02-03
4   4   AUD             Australian Dollar 1.269889      0.787470    2021-02-03

The idea is now to merge both frames, and to show the stock price in euro. I think we have to put both dates in the merged data-frame.

We need a for loop to identify if the stock is not in euro, search for a match in date, search for a match in currency code, and if the currency code is for instance US dollar, then multiply the stock * 0.757381 (if the stock date is 2012-02-03 in data-frame 1).

If the stock is in euro, we can multiply *1. If the stock price is for instance in GBP then multiply *1.231461.

The ideal situation would be to have a new data-frame with only the date and the stock price in euro.

Solution

  • The previous answer has a bug in the code.

    This line:

    stock_df = pd.concat([stock_df, data], axis=0)

    should be indented to the same level as the if and elif statements, otherwise the results from the last loop always overwrite the previous.

    EDIT 2: Another issue in the answer is that the exchange rates should multiply the price in the original currency instead of dividing... In other words the loops should be doing this:

    data['price'] = data['Close'] * data['rating']