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

user_13227647 0
Viewed 1997

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.

price stock python
2 Answers

To deal with such a case, we can

  1. To combine stock price and exchange rate after acquiring them separately
  2. Acquire the exchange rate at the same time as the stock price is acquired and store it in the data frame.

These approaches may differ depending on the number of stocks and currencies to be acquired, and the period to be covered. The approach I have taken is the latter type, where the stock price and exchange rate are acquired and stored at the time of acquisition.

The reason is that the exchange rate does not change in the past, so the rate is acquired at the time of acquisition and converted to the base rate. We believe this effect will reduce the amount of code and the only reuse of the data will be extraction.

import yfinance as yf
import pandas as pd
import numpy as np

start = '2020-07-01'
end = '2021-01-01'

rates = ['USDEUR=X', 'GBPEUR=X']
tickers = yf.Tickers(' '.join(rates))

exchange_rates = []
for i in tickers.tickers:
    exchange_rates.append(tickers.tickers[i].history(start=start, end=end).Close)

ex_df = pd.DataFrame(exchange_rates).T
ex_df.columns = rates
ex_df['EUREUR=X'] = 1.0

assets = {'^AEX':'EUR', 'AAPL':'USD', 'AZN.L':'GBP', 'AD.AS':'EUR', 'ASML.AS':'EUR'}

stock_df = pd.DataFrame()
for k,v in assets.items():
    data = yf.download(k, start=start, end=end, progress=False).Close.to_frame()
    data['ticker'] = k
    if v[:3] == 'EUR':
        data['rating'] = ex_df['EUREUR=X']
        data['price'] = data['Close'] / data['rating'] 
    elif v[:3] == 'USD':
        data['rating'] = ex_df['USDEUR=X']
        data['price'] = data['Close'] / data['rating'] 
    elif v[:3] == 'GBP':
        data['rating'] = ex_df['GBPEUR=X']
        data['price'] = data['Close'] / data['rating'] 
    else:
        data['rating'] = np.NaN
        data['price'] = np.NaN
stock_df = pd.concat([stock_df, data], axis=0)

stock_df
    Close   ticker  rating  price
Date                
2020-06-30  559.729980  ^AEX    1.0     559.729980
2020-07-01  562.669983  ^AEX    1.0     562.669983
2020-07-02  570.750000  ^AEX    1.0     570.750000
2020-07-03  568.630005  ^AEX    1.0     568.630005
2020-07-06  578.520020  ^AEX    1.0     578.520020
...     ...     ...     ...     ...
2020-12-24  393.500000  ASML.AS     1.0     393.500000
2020-12-28  396.850006  ASML.AS     1.0     396.850006
2020-12-29  396.299988  ASML.AS     1.0     396.299988
2020-12-30  398.500000  ASML.AS     1.0     398.500000
2020-12-31  397.549988  ASML.AS     1.0     397.549988

stock_df.loc[stock_df['ticker'] == 'AAPL'].head()

    Close   ticker  rating  price

    Close   ticker  rating  price
Date                
2020-06-30  91.199997   AAPL    0.88911     102.574478
2020-07-01  91.027496   AAPL    0.89000     102.278087
2020-07-02  91.027496   AAPL    0.88878     102.418480
2020-07-06  93.462502   AAPL    0.88911     105.119162
2020-07-07  93.172501   AAPL    0.88395     105.404719
user_131078040

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']
user_24644430
Related