Airflow + Bank of Canada API for daily foreign exchange rates
Hi everyone! Last month, I created a small ETL project based on Spotify and Airflow for newcomers to the data world. This month, I am introducing something new and very simple to implement: an API. As some of you may know, I am currently located in Toronto, Canada, and that’s the reason I am using the Bank of Canada API (you can use any other API with some modifications).
The reason for showcasing this is that many businesses here in Canada have international exposure and deal with different currencies. The Bank of Canada deals with 24 registered currencies and shares exchange rates every day after 04:00 PM EST (Monday to Friday, except public holidays). This API will help those businesses keep daily exchange rates handy in their databases.
Not to make this small project too complex but we can consume another API which pulls the date of all public holidays and we can skip our DAG run if those dates matches with today’s date.
You can check that hoilday’s API here →https://canada-holidays.ca/api/v1/holidays
Lets jump back to our Bank of Canada API
https://www.bankofcanada.ca/valet/observations/group/FX_RATES_DAILY/json?start_date=2024-06-15&end_date=2024-06-15Above API (with params) will give you below JSON file (You can test that with diff parameters as I just wanted to deal with daily I am passing both start and end date same ie, 2024–06–15.
{
"groupDetail": {
"label": "Daily exchange rates",
"description": "Daily average exchange rates - published once each business day by 16:30 ET. All Bank of Canada exchange rates are indicative rates only.",
"link": null
},
"terms": {
"url": "https://www.bankofcanada.ca/terms/"
},
"seriesDetail": {
"FXAUDCAD": {
"label": "AUD/CAD",
"description": "Australian dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXBRLCAD": {
"label": "BRL/CAD",
"description": "Brazilian real to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXCNYCAD": {
"label": "CNY/CAD",
"description": "Chinese renminbi to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXEURCAD": {
"label": "EUR/CAD",
"description": "European euro to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXHKDCAD": {
"label": "HKD/CAD",
"description": "Hong Kong dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXINRCAD": {
"label": "INR/CAD",
"description": "Indian rupee to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXIDRCAD": {
"label": "IDR/CAD",
"description": "Indonesian rupiah to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXJPYCAD": {
"label": "JPY/CAD",
"description": "Japanese yen to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXMYRCAD": {
"label": "MYR/CAD",
"description": "Malaysian ringgit to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXMXNCAD": {
"label": "MXN/CAD",
"description": "Mexican peso to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXNZDCAD": {
"label": "NZD/CAD",
"description": "New Zealand dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXNOKCAD": {
"label": "NOK/CAD",
"description": "Norwegian krone to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXPENCAD": {
"label": "PEN/CAD",
"description": "Peruvian new sol to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXRUBCAD": {
"label": "RUB/CAD",
"description": "Russian ruble to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXSARCAD": {
"label": "SAR/CAD",
"description": "Saudi riyal to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXSGDCAD": {
"label": "SGD/CAD",
"description": "Singapore dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXZARCAD": {
"label": "ZAR/CAD",
"description": "South African rand to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXKRWCAD": {
"label": "KRW/CAD",
"description": "South Korean won to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXSEKCAD": {
"label": "SEK/CAD",
"description": "Swedish krona to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXCHFCAD": {
"label": "CHF/CAD",
"description": "Swiss franc to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXTWDCAD": {
"label": "TWD/CAD",
"description": "Taiwanese dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXTHBCAD": {
"label": "THB/CAD",
"description": "Thai baht to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXTRYCAD": {
"label": "TRY/CAD",
"description": "Turkish lira to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXGBPCAD": {
"label": "GBP/CAD",
"description": "UK pound sterling to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXUSDCAD": {
"label": "USD/CAD",
"description": "US dollar to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
},
"FXVNDCAD": {
"label": "VND/CAD",
"description": "Vietnamese dong to Canadian dollar daily exchange rate",
"dimension": {
"key": "d",
"name": "Date"
}
}
},
"observations": []
}Just to make it little simpler make sure that you have all connections in your Airflow specially DB and Slack
Check out my Slack Notification
Also in my code I have created a function called slack_messages import send_alerts which will take an emoji:str,df,columns:list,date, txt:str,channel_name:str how to send notifications to slack channel
from slack_sdk import WebClient
from airflow.contrib.operators.slack_webhook_operator import SlackWebhookOperator
import os
from tabulate import tabulate
token="XXXXXXXXXXXXXXXXXXXXXXX"
def send_alerts(emoji:str,df,columns:list,date, txt:str,channel_name:str):
"""
This message will send a notification to the Slack channel in Tabular format
"""
slack_client = WebClient(token=token) # Creating Slack Client to send message
tbl1 = tabulate(df,headers=columns,
showindex=False,tablefmt='rst') # Creating a table with Tabulate() function
slack_msg_1 = f">{emoji} {txt} on :{date} {emoji} \n" f"```\n{tbl1}\n ```"
slack_client.chat_postMessage(channel=channel_name,text=slack_msg_1)
return 1I am also uploading a full code on my Git-Hub. Please find the link below
This DAG will run everyday and collect all your Foriegn Exchage Rates straight to your DB.
And with these steps now you can easily consume an API and load the record in your DataBase.
You can also add failure notifications to this if your DAG fails at any point or specially on weekends or on public holiday.
Check my below blog on how to send notifications to slack channel
Follow me on LinkedIn for more Airflow related content
https://www.linkedin.com/in/misamant
Also you can check
