One more issue I noticed was not every date has an entry in the “fred” CSVs. I believe Sat Sun.
If amount is not NAN, but amount_usd is NAN, try to match the max(previous 2 dates). Something along those lines.
UPDATE: I see you have used
# Interpolate the missing values using linear method
merged_df[currency] = merged_df[currency].interpolate(method='linear')
But that won’t use Friday value which is usually the case right?
UPDATE 2: From what I am reading, conversion happens on settlement date, not transaction date. While most of the world uses Friday rate, USD and CAD transactions are settled next business day. So in this case, missing values will have to look for the next available date.
# First, create a complete date range
date_range = pd.date_range(start=df_conversion.index.min(), end=df_conversion.index.max(), freq='D')
# Reindex the dataframe with the complete date range and backward fill the values
df_filled = df_conversion.reindex(date_range).bfill()
That’s really good information. Thank you @mavwolverine
So for CAD USD conversions we need to use the Friday rate, and for the rest of the conversions we need to use the Monday rate? Did I get that right?
No. Since we are converting to USD, Monday always in our case.
If it was INR to SGD, it would be Friday (I presume based on what I have read so far).
@adamschroeder
simplified version
from datetime import datetime
import math
# import numpy as np
import pandas as pd
import pandas_datareader.data as pd_dr_web
import plotly.graph_objects as go
df_pledges = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-pledges.json")
df_payments = pd.read_json("https://storage.googleapis.com/plotly-app-challenge/one-for-the-world-payments.json")
start_date = datetime.strptime('2024-07-01', '%Y-%m-%d')
end_date = min(
datetime.strptime('2025-06-30', '%Y-%m-%d'),
df_payments['date'].max()
) # .strftime('%Y-%m-%d')
df_payments_ytd = df_payments[
(df_payments['date'] >= start_date)
& (df_payments['date'] <= end_date)
& (~df_payments['portfolio'].isin((
'One for the World Discretionary Fund',
'One for the World Operating Costs')
))
].copy() # copy is important to avoid slice warning
df_payments_ytd.reset_index(drop=True, inplace=True)
df_conversion = pd_dr_web.DataReader(
["DEXUSUK", "DEXCAUS", "DEXUSAL", "DEXUSEU", "DEXSIUS", "DEXSZUS"],
"fred",
start_date,
end_date
)
# First, create a complete date range
date_range = pd.date_range(
start=start_date,
end=min(end_date, df_conversion.index.max()),
freq='D'
)
# Reindex the dataframe with the complete date range and back fill the values
df_conversion = df_conversion.reindex(date_range).bfill()
# Create a dictionary for each currency's conversion rates
conversion_rates = {
'USD': 1.0,
'GBP': df_conversion['DEXUSUK'],
'CAD': 1/df_conversion['DEXCAUS'], # Inverting for division
'AUD': df_conversion['DEXUSAL'],
'EUR': df_conversion['DEXUSEU'],
'SGD': 1/df_conversion['DEXSIUS'], # Inverting for division
'CHF': 1/df_conversion['DEXSZUS'] # Inverting for division
}
# Create the conversion multiplier series based on currency
df_payments_ytd['multiplier'] = df_payments_ytd.apply(
lambda row: 1 if row["currency"] == "USD"
else conversion_rates[row["currency"]][row["date"]],
axis=1
)
# Multiply amount by the conversion rate
df_payments_ytd['amount_usd'] = (
df_payments_ytd['amount'] * df_payments_ytd['multiplier']
).round(2)
# Optionally drop the multiplier column if you don't need it
df_payments_ytd.drop('multiplier', axis=1, inplace=True)
- avoid multiple web.DataReader calls.
- avoid multiple merge
Please validate if you get the same results.
@mavwolverine
can you please share the complete reproducible code, with the libraries and incorporating the df_payments dataset?
@adamschroeder updated the code above
Is there a specific reason you’re limited the dataset to not include One for the World Discretionary Fund and One for the World Operating Costs?
notes from row #9 says to ignore these values. I was working of that.
In the payments dataset, excluding "One for the World Discretionary Fund "One for the World Operating Costs" and "One for the World Operating Costs" from the portfolio column
I see. Ok, Good luck.
Here’s the final exchange-rates.py file based on your suggestions, @mavwolverine . But one would also need to run the currency-converter.py file after that.
import pandas_datareader.data as web
import datetime
import pandas as pd
def get_historical_exchange_rates(start_date="2020-01-01", end_date=None):
"""Fetch historical GBP to USD exchange rates from FRED."""
if end_date is None:
end_date = datetime.datetime.today().strftime("%Y-%m-%d")
try:
for currency in ['DEXUSUK', 'DEXCAUS', 'DEXUSAL', 'DEXUSEU', 'DEXSIUS', 'DEXSZUS']:
df = web.DataReader(currency, "fred", start_date, end_date)
# Create a new DataFrame with all dates in the range
date_range = pd.date_range(start=df.index.min(), end=df.index.max(), freq='D')
df_filled = df.reindex(date_range).bfill()
df_filled.reset_index(inplace=True)
df_filled.rename(columns={'index': 'DATE'}, inplace=True)
# save df as a csv sheet
df_filled.to_csv(f"{currency}_exchange_rates.csv", index=False)
except Exception as e:
print("Error fetching data:", e)
get_historical_exchange_rates("2014-03-01", "2025-02-28")
@adamschroeder
I think this piece covers the currency-converter.py
# Create a dictionary for each currency's conversion rates
conversion_rates = {
'USD': 1.0,
'GBP': df_conversion['DEXUSUK'],
'CAD': 1/df_conversion['DEXCAUS'], # Inverting for division
'AUD': df_conversion['DEXUSAL'],
'EUR': df_conversion['DEXUSEU'],
'SGD': 1/df_conversion['DEXSIUS'], # Inverting for division
'CHF': 1/df_conversion['DEXSZUS'] # Inverting for division
}
# Create the conversion multiplier series based on currency
df_payments_ytd['multiplier'] = df_payments_ytd.apply(
lambda row: 1 if row["currency"] == "USD"
else conversion_rates[row["currency"]][row["date"]],
axis=1
)
# Multiply amount by the conversion rate
df_payments_ytd['amount_usd'] = (
df_payments_ytd['amount'] * df_payments_ytd['multiplier']
).round(2)
# Optionally drop the multiplier column if you don't need it
df_payments_ytd.drop('multiplier', axis=1, inplace=True)
1 Like