Project Description

In this project, we will work with a public API and a CSV file containing sales data from an online gadget store. Our main goal is to convert all sales amounts into US dollars so we can understand the total revenue in a single currency.

The dataset includes transactions made in different currencies. To calculate the total amount of sales in USD, we need to convert all non-USD amounts using the correct exchange rates.

To do this, we will use our data manipulation skills along with the VAT Comply Currency Exchange API, which is a public and free API. It provides historical and real-time exchange rate data. Specifically, we will fetch exchange rates from January 21st, 2024, and apply them to convert the sales data.

By the end of this project, we will have a clear summary showing the original amount, the currency it was in, the exchange rate used, and the final amount in USD.

Here is an example of what our final output will look like:

amount	currency	exchange_rate	amount_usd
43.75	EUR	...	...
385.5	GBP	...	...
495.5	GBP	...	...
117.99	GBP	...	...
624	USD	...	...

This process will help us accurately calculate the total sales and better understand the financial performance of the store.

# Import required packages/libraries
import pandas as pd
import requests

# Load the data 
orders = pd.read_csv('orders-2024-01-21.csv')


# Get exchange rates from API
request_url = 'https://api.vatcomply.com/rates'
params = {
    'date' : '2024-01-21',
    'base': 'USD'
} 
response = requests.get(request_url, params=params)
rates = response.json()['rates']


# Apply exchange rates
orders['exchange_rate'] = orders['currency'].map(rates)


# Calculate sales in US dollars
orders['amount_usd'] = orders['amount'] * orders['exchange_rate']


# Total amount of sales in US dollars
total_usd_sales = orders['amount_usd'].sum()

print(total_usd_sales)

326864.39599246805

orders

	amount	currency	exchange_rate	amount_usd
0	43.75	EUR	0.918527	40.185542
1	385.50	GBP	0.788326	303.899490
2	495.50	GBP	0.788326	390.615298
3	117.99	GBP	0.788326	93.014529
4	624.00	USD	1.000000	624.000000
...	...	...	...	...
718	915.50	USD	1.000000	915.500000
719	835.05	EUR	0.918527	767.015707
720	891.50	GBP	0.788326	702.792206
721	414.75	USD	1.000000	414.750000
722	766.90	USD	1.000000	766.900000

723 rows × 4 columns