Creating a Database for Economic Calendar Events from FXStreet
Posted by Daniel Cano
In the dynamic and data-driven realm of trading, access to timely and accurate economic data is paramount. FXStreet, a trusted provider of financial news and analysis, offers a detailed economic calendar that is indispensable for traders and financial analysts. This calendar includes crucial economic events and indicators that can significantly impact market movements.
This post will provide a comprehensive guide on how to build a robust and professional database to capture and manage economic calendar events from FXStreet. By creating this database, you will be equipped with a powerful tool to systematically track, store and analyze economic data. This will enhance your ability to make informed trading decisions, improve your market strategies and stay ahead of economic trends while keeping risks controled.
Importance of Economic Calendar
Economic calendar events are crucial for traders and investors across all asset classes, both financial and non-financial. These events provide insights into the economic health and policy directions of various countries, which can have far-reaching impacts on market sentiment and asset prices. Here’s why they are important, regardless of the asset being traded:
- Market Sentiment: Economic indicators such as GDP growth, unemployment rates, and inflation figures influence market sentiment. Positive data can boost confidence and lead to market rallies, while negative data can cause fear and sell-offs. Understanding market sentiment helps traders make informed decisions about their positions.
- Interest Rates and Monetary Policy: Central bank meetings and announcements are key events on the economic calendar. Decisions on interest rates and monetary policy directly impact currency values, bond yields, and stock markets. Changes in interest rates can also affect commodities like gold and oil, as well as real estate markets.
- Global Economic Interdependence: In today's interconnected global economy, economic events in one country can have ripple effects worldwide. For instance, a significant economic event in the US, such as the release of the Non-Farm Payrolls report, can influence global markets, affecting everything from forex pairs to international stocks and commodities.
- Corporate Earnings and Business Cycles: Economic data influences business cycles and corporate earnings. Companies in various sectors are affected by changes in economic conditions, such as consumer spending and industrial production. Traders in stocks and corporate bonds must pay attention to these indicators to gauge the health of the sectors they are invested in.
- Commodity Prices: Economic indicators can also affect the supply and demand dynamics of commodities. For example, crude oil prices can be influenced by data on industrial production, while agricultural commodities might be affected by changes in consumer prices and trade balances.
- Currency Fluctuations: For those trading in forex, economic calendar events are indispensable. Currency values are highly sensitive to economic news, and traders must stay informed about data releases to anticipate currency movements.
- Risk Management: Being aware of upcoming economic events allows traders to manage risks more effectively. They can adjust their portfolios, hedge positions, or set stop-loss orders to protect against adverse market movements resulting from unexpected economic data.
In conclusion, economic calendar events provide vital information that influences a wide range of assets. Whether trading stocks, bonds, forex, commodities, or even real estate, staying informed about economic data releases is essential for making strategic and informed trading decisions.
Value in Having Historical Data
Having a historical record of economic events is essential for backtesting any trading strategy that incorporates macroeconomic news. Here’s why maintaining this historical data is crucial:
-
Strategy Validation: Historical data allows traders to validate their strategies against past events. By applying a trading strategy to historical data, traders can assess its effectiveness and robustness in different market conditions. This helps in identifying strengths and weaknesses of the strategy.
-
Understanding Market Reactions: By analyzing historical economic events and the corresponding market reactions, traders can gain insights into how different types of news impact asset prices. This understanding helps in predicting potential market movements in response to similar events in the future.
-
Pattern Recognition: Historical economic data helps in identifying patterns and correlations between economic indicators and market performance. Recognizing these patterns is crucial for developing strategies that can capitalize on predictable market behaviors following specific economic events.
-
Risk Assessment: Backtesting with historical data helps in evaluating the risk associated with a trading strategy. Traders can analyze how the strategy performed during periods of high volatility or economic uncertainty, providing a clearer picture of potential risks and rewards.
-
Improving Strategy Accuracy: Continuous backtesting with historical data allows traders to refine their strategies. They can tweak parameters, adjust entry and exit points, and optimize their approach based on historical performance, leading to more accurate and reliable strategies.
-
Market Evolution: Markets evolve over time, and the impact of economic events can change. Historical data provides a timeline of market reactions, helping traders understand how the influence of certain economic indicators has evolved. This context is crucial for adapting strategies to current market dynamics.
-
Compliance and Reporting: For institutional traders and fund managers, having a historical record of economic events and strategy performance is often necessary for compliance and reporting purposes. It ensures transparency and accountability in trading activities.
In summary, a historical record of economic events is indispensable for backtesting macroeconomic news-based trading strategies. It enables traders to validate and refine their strategies, understand market reactions, assess risks, and build confidence in their trading approach. Without this historical data, it would be challenging to develop and optimize strategies that effectively incorporate the influence of macroeconomic news.
Source of Data
FXStreet is a leading financial news and analysis platform that specializes in providing real-time information on the forex market. Established in 2000, FXStreet has built a reputation for delivering accurate and up-to-date market insights, including news, technical analysis, and economic data. The platform is widely recognized for its comprehensive economic calendar, which features a detailed schedule of upcoming economic events and indicators from around the world.
One of the main reasons FXStreet is an excellent choice for downloading economic calendar data is its reliability and accuracy. The economic calendar is meticulously updated and covers a broad range of economic indicators, including GDP figures, employment reports, inflation data, and central bank announcements. This ensures that traders and analysts have access to the most current and relevant information to make informed decisions.
Moreover, FXStreet's economic calendar provides detailed event descriptions, historical data, and consensus forecasts, which are invaluable for understanding the potential market impact of each event. The user-friendly interface and customizable features allow users to filter events based on their preferences, making it easier to focus on the most pertinent data.
Python Code
The code now presented provides a structured way to interact with the FXStreet API, handling token generation and making both GET and POST requests to fetch economic calendar events. It allows filtering data based on various parameters and supports returning the data in different formats.
import enum
import datetime as dt
import requests
import pandas as pd
class FXStreet:
class Volatility(enum.Enum):
NONE: str = 'NONE'
LOW: str = 'LOW'
MEDIUM: str = 'MEDIUM'
HIGH: str = 'HIGH'
class Category(enum.Enum):
BONDAUCTIONS: str = '8896AA26-A50C-4F8B-AA11-8B3FCCDA1DFD'
CAPITALFLOWS: str = 'FA6570F6-E494-4563-A363-00D0F2ABEC37'
CENTRALBANKS: str = 'C94405B5-5F85-4397-AB11-002A481C4B92'
CONSUMPTION: str = 'E229C890-80FC-40F3-B6F4-B658F3A02635'
ECONOMICACTIVITY: str = '24127F3B-EDCE-4DC4-AFDF-0B3BD8A964BE'
ENERGY: str = 'DD332FD3-6996-41BE-8C41-33F277074FA7'
HOLIDAYS: str = '7DFAEF86-C3FE-4E76-9421-8958CC2F9A0D'
HOUSINGMARKET: str = '1E06A304-FAC6-440C-9CED-9225A6277A55'
INFLATION: str = '33303F5E-1E3C-4016-AB2D-AC87E98F57CA'
INTERESTRATES: str = '9C4A731A-D993-4D55-89F3-DC707CC1D596'
LABORMARKET: str = '91DA97BD-D94A-4CE8-A02B-B96EE2944E4C'
POLITICS: str = 'E9E957EC-2927-4A77-AE0C-F5E4B5807C16'
class Country(enum.Enum):
UNITEDSTATES = 'US'
UNITEDKINGDOM = 'UK'
EUROPEANMONETARYUNION = 'EMU'
GERMANY = 'DE'
CHINA = 'CN'
JAPAN = 'JP'
CANADA = 'CA'
AUSTRALIA = 'AU'
NEWZEALAND = 'NZ'
SWITZERLAND = 'CH'
FRANCE = 'FR'
ITALY = 'IT'
SPAIN = 'ES'
UKRAINE = 'UA'
INDIA = 'IN'
RUSSIA = 'RU'
TURKEY = 'TR'
SOUTHAFRICA = 'ZA'
BRAZIL = 'BR'
SOUTHKOREA = 'KR'
INDONESIA = 'ID'
SINGAPORE = 'SG'
MEXICO = 'MX'
SWEDEN = 'SE'
NORWAY = 'NO'
DENMARK = 'DK'
GREECE = 'GR'
PORTUGAL = 'PT'
IRELAND = 'IE'
AUSTRIA = 'AT'
BELGIUM = 'BE'
NETHERLANDS = 'NL'
FINLAND = 'FI'
CZECHREPUBLIC = 'CZ'
POLAND = 'PL'
HUNGARY = 'HU'
ROMANIA = 'RO'
CHILE = 'CL'
COLOMBIA = 'CO'
ARGENTINA = 'AR'
ICELAND = 'IS'
HONGKONG = 'HK'
SLOVAKIA = 'SK'
ISRAEL = 'IL'
SAUDIARABIA = 'SA'
VIETNAM = 'VN'
KUWAIT = 'KW'
EGYPT = 'EG'
UNITEDARABEMIRATES = 'AE'
QATAR = 'QA'
THAILAND = 'TH'
class Version(enum.Enum):
V1: str = 'v1'
V2: str = 'v2'
class TokenGrant(enum.Enum):
DOMAIN: str = 'domain'
CLIENT: str = 'client_credentials'
BASE_URL: str = 'https://calendar-api.fxstreet.com'
headers: dict = {}
def __init__(self, culture:str='es', version:Version=Version.V1) -> None:
self.culture: str= culture
self.version: str = version.value
self.getToken()
def __post(self, url:str, params:dict=None, headers:dict=None) -> (list | dict):
'''
Create a POST request.
Parameters
----------
url: str
URL to post the request.
params: dict
Data to pass in the request.
headers: dict
Headers to include in the request.
Returns
-------
r: (list | dict)
JSON data retrieve by the request.
'''
self.r = requests.post(url=url, data=params, headers=headers)
return self.r.json()
def __get(self, url:str, params:dict=None, headers:dict={}) -> (list | dict):
'''
Create a GET request.
Parameters
----------
url: str
URL to request.
params: dict
Params to pass in the request.
headers: dict
Headers to include in the request.
Returns
-------
r: (list | dict)
JSON data retrieve by the request.
'''
self.r = requests.get(url=url, params=params, headers={**self.headers, **headers})
return self.r.json()
def getToken(self, client_id:str=None, client_secret:str=None, grant_type:TokenGrant=TokenGrant.DOMAIN, version:Version=Version.V2) -> None:
'''
Get a valid token for the API.
Parameters
----------
client_id: str
Client ID to get a token for.
client_secret: str
Client Secret for the API.
grant_type: TokenGrant
Method used to get the token.
version: Version
API version to request.
'''
params = {'grant_type': grant_type.value}
if version == self.Version.V1:
url: str = 'https://authorization.fxstreet.com/token'
elif version == self.Version.V2:
url: str = 'https://authorization.fxstreet.com/v2/token'
params['scope'] = 'calendar'
if client_id != None:
params['client_id'] = client_id
if client_secret != None:
params['client_secret'] = client_secret
current_time = dt.datetime.now()
self.token: dict = self.__post(url=url, params=params, headers={'Origin':'https://www.fxstreet.es'})
self.token['creation'] = current_time
self.authorization: str = self.token['token_type'] + ' ' + self.token['access_token']
self.headers: dict = {
'Authorization': self.authorization,
'User-Agent':'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/121.0.0.0 Safari/537.36',
}
def getCalendar(self, start:str=None, end:str=None, countries:list=None, categories:list=None, volatilities:list=None, df:bool=False) -> (list[dict] | pd.DataFrame):
'''
Get calendar Events.
Parameters
----------
start: str
Date from which to start. The format must be: YYYY-MM-DDTHH:MM:SSZ
end: str
Date till which to get data. The format must be: YYYY-MM-DDTHH:MM:SSZ
countries: list
List of countries to filter. You can get the countries from the Countries class.
categories: list
List of categories to filter. You can get the categories from the Category class.
volatilities: list
List of volatilities to filter. You can get the volatilities from the Volatility class.
df: bool
True to retrieve the data as pandas.DataFrame.
Returns
-------
data: (list[dict] | pd.DataFrame)
Categories data.
'''
url: str = f'{self.BASE_URL}/{self.culture}/api/{self.version}/eventDates/{start}/{end}'
volatilities = '&'.join([f'volatilities={v.value if isinstance(v, self.Volatility) else v}' for v in volatilities]) if volatilities != None else ''
countries = '&'.join([f'countries={v.value if isinstance(v, self.Country) else v}' for v in countries]) if countries != None else ''
categories = '&'.join([f'categories={v.value if isinstance(v, self.Category) else v}' for v in categories]) if categories != None else ''
filters: str = '&'.join([volatilities, countries, categories])
data: dict = self.__get(url=f'{url}?{filters}', headers=headers)
return pd.DataFrame(data) if df else data
Next is an example where we get the calendar events for the last month for all the countries, cateogries and volatilites options available through FXStreet filters.
fx = FXStreet(culture='en')
countries: list = FXStreet.Country.__members__.values()
categories: list = FXStreet.Category.__members__.values()
volatilities: list = FXStreet.Volatility.__members__.values()
end: str = (dt.datetime.today() - dt.timedelta(days=1)).strftime('%Y-%m-%dT%H:%M:%SZ')
start: str = (dt.datetime.today() - dt.timedelta(days=31)).strftime('%Y-%m-%dT%H:%M:%SZ')
calendar: list[dict] = fx.getCalendar(start=start, end=end, countries=countries, categories=categories, volatilities=volatilities)
cal_df = pd.DataFrame(calendar)
Which returns ah DataTable like the following one.
This code would have to be edited to download the data you need.
Database Structure
Once you have the economic calendar events it must be formated to fit the tables structure of the database. The database is designed to store and manage the economic calendar events and consists of four entities: Country, Currency, EventType, and Event. Here is a detailed description of each entity:
-
Country:
- code (Primary Key): A unique identifier for each country, typically following the ISO 3166-1 alpha-2 format.
- name: The name of the country.
{'code': 'US', 'name': 'UNITEDSTATES'} -
Currency:
- code (Primary Key): A unique identifier for each currency, typically following the ISO 4217 format.
- name: The name of the currency, which comes empty and should be given after creating the database.
{'code': 'USD', 'name': ''} -
EventType:
- id (Primary Key): A unique identifier for each type of event.
- country_code (Foreign Key to Country.code): The country associated with the event type, referencing the Country entity.
- name: The name of the event type (e.g., 'Non-Farm Payrolls').
- period_type: The kind of publishing period (MONTH or YEAR).
- unit: Unit of the the associated events values.
- is_speech: True if it is a speech.
- is_all_day: Usually is marked as True in holidays and festivities.
- is_report: True if is a report.
{ 'id': '0391ac9e-1073-4a93-90fd-f9427303ab00', 'period_type': 'MONTH', 'name': 'Consumer Confidence', 'country_code': 'US', 'unit': None, 'potency': 'ZERO', 'is_speech': False, 'is_all_day': False, 'is_report': False } -
Event:
- id (Primary Key): A unique identifier for each event.
- event_type_id (Foreign Key to EventType.id): The type of event, referencing the EventType entity.
- date_utc: The date and time when the event data is published.
- period_date_utc: Period used for the event data calculation.
- currency_code (Foreign Key to Currency.code): The currency associated with the event, referencing the Currency entity.
- actual: The current value of the event data (e.g., the latest reported figure).
- revised: The revised value if it was revised.
- consensus: The estimated value.
- ratio_deviation: The ratio between actual value and the consensed value.
- previous: The previous value of the event data (e.g., the figure reported in the previous period).
- is_better_than_expected: True if the actual value is greater than the consensed value.
- volatility: An indicator of the event's importance or expected impact level (e.g., 'High', 'Medium', 'Low').
{
'id': '0b6e6a86-d3f1-4066-a3dd-5f96fe94a62a',
'event_type_id': '4abee304-9984-47cf-80ab-dca1114165f5',
'date_utc': '2024-05-15T12:30:00Z',
'period_date_utc': '2024-04-01T00:00:00Z',
'currency_code': 'USD',
'actual': 0.3,
'revised': nan,
'consensus': 0.3,
'ratio_deviation': 0.0,
'previous': 0.4,
'is_better_than_expected': None,
'volatility': 'HIGH',
'is_tentative': False,
'is_preliminary': False,
'last_updated': 1715776642,
'previous_is_preliminary': False
}
Country and Currency entities are basic entities that store information about countries and currencies. The code fields in both entities act as primary keys, uniquely identifying each country and currency.
EventType entity describes different types of economic events. Each event type is associated with a country (via country_code). The id field is the primary key.
Event entity records individual economic events. Each event is linked to a currency (via currency_code) and an event type (via event_type_id). It includes details such as the publication date, current and previous values of the event data, and its volatility. The id field is the primary key.
Database Structure Python Code
The next code is used to create lists of dictionaries with the data which will be inserted in the database.
COUNTRY_CODES_FILTER_OPTIONS: dict = {v.value: k for k, v in dict(FXStreet.Country.__members__).items()}
countries: list = [{'code': country[0], 'name': COUNTRY_CODES_FILTER_OPTIONS[country[0]]} \
for country in cal_df.groupby('countryCode')]
currencies: list = [{'code': currency[0], 'name': ''} for currency in cal_df.groupby('currencyCode')]
events: list = [{
'id': event[0],
'period_type': event[1]['periodType'].iloc[-1],
'name': event[1]['name'].iloc[-1],
'country_code': event[1]['countryCode'].iloc[-1],
'unit': event[1]['unit'].iloc[-1],
'potency': event[1]['potency'].iloc[-1],
'is_speech': event[1]['isSpeech'].iloc[-1],
'is_all_day': event[1]['isAllDay'].iloc[-1],
'is_report': event[1]['isReport'].iloc[-1],
} for event in cal_df.groupby('eventId')]
values: list = [{
'id': value[0],
'event_type_id': value[1]['eventId'].iloc[-1],
'date_utc': value[1]['dateUtc'].iloc[-1],
'period_date_utc': value[1]['periodDateUtc'].iloc[-1],
'currency_code': value[1]['currencyCode'].iloc[-1],
'actual': value[1]['actual'].iloc[-1],
'revised': value[1]['revised'].iloc[-1],
'consensus': value[1]['consensus'].iloc[-1],
'ratio_deviation': value[1]['ratioDeviation'].iloc[-1],
'previous': value[1]['previous'].iloc[-1],
'is_better_than_expected': value[1]['isBetterThanExpected'].iloc[-1],
'volatility': value[1]['volatility'].iloc[-1],
'is_tentative': value[1]['isTentative'].iloc[-1],
'is_preliminary': value[1]['isPreliminary'].iloc[-1],
'last_updated': value[1]['lastUpdated'].iloc[-1],
'previous_is_preliminary': value[1]['previousIsPreliminary'].iloc[-1],
} for value in cal_df.groupby('id')]
For this example I will use PostgreSQL database so I will be using psycopg2 library to achieve this, leveraging the database structure we previously defined.
import psycopg2
from psycopg2 import sql
# Create the connection and cursor
conn = psycopg2.connect(
dbname='your_database',
user='your_username',
password='your_password',
host='your_host',
port='your_port'
)
cursor = conn.cursor()
def insert(values:list[dict], table:str, cursor:psycopg2.cursor) -> None:
columns: list = values[0].keys()
query: sql.SQL = sql.SQL("INSERT INTO {} ({}) VALUES ({})").format(
sql.Identifier(table),
sql.SQL(', ').join(map(sql.Identifier, columns)),
sql.SQL(', ').join(sql.Placeholder() * len(columns))
)
cursor.executemany(query, [tuple(v.values()) for v in values])
conn.commit()
insert(values=countries, table='country', cursor=cursor)
insert(values=currencies, table='currency', cursor=cursor)
insert(values=events, table='event_type', cursor=cursor)
insert(values=values, table='event', cursor=cursor)
# Close the cursor and connection
cursor.close()
conn.close()
Conclusion
In the realm of financial analysis and strategic trading, having access to accurate and timely economic calendar events is essential. The database structure we discussed—comprising the entities Country, Currency, EventType, and Event—provides a robust foundation for organizing and managing these critical data points. By leveraging Python and the psycopg2 library, we demonstrated an efficient method to upload a list of dictionaries representing economic events into a PostgreSQL database. You can create the database structure you desire but the principles are the same.
For financial analysts, traders, and data scientists, this method provides a reliable way to store and access historical economic events. Such data is very important for backtesting trading strategies, conducting economic research, and making informed investment decisions. The ability to filter and analyze this data based on various criteria, such as country, currency, event type, and volatility, further enhances its utility.
As the financial markets continue to evolve, maintaining a comprehensive and well-structured database of economic events will become increasingly important. This approach not only streamlines the data ingestion process but also lays the groundwork for more sophisticated data analysis and application development.
Thank you for reading, and I hope this guide provides you with the tools and knowledge to manage your economic data effectively. Stay tuned for more insights and tutorials on financial data management and analysis.