Skip to content

Exciting times!

You've been running our very successful gadget webshop 'DataGadgets' for a few years and have recently expanded into new territories. While you've been focussed on the US market for the first five years of our existence, you now are shipping our cool data gadgets to the UK and Europe, too! But now our in-house built reporting has broken! Transactions don't only come in USD, but you're also receiving transactions in EUR and GPB.

To better understand the volume of transactions being made, you should convert the non-USD transactions to USD and sum up the total. To do this, however, you'll need to use the proper exchange rates.

In this project, you'll start with a CSV file containing all e-commerce transactions made on January 21st, but in their original currencies. Your job is to calculate the total sum in USD of all transactions so you know how much USD you sold on January 21st. To do this, you'll need to convert any non-USD transactions to USD using the exchange rate from January 21st, 2024.

To get the exchange rates from January 21st, 2024, you'll rely on VAT Comply rates API's public and free currency exchange API. You'll also use pandas to load the CSV file into a DataFrame and the requests package to make interacting with the API easier.

You need to update the orders DataFrame so the final version has two new columns: exchange_rate and amount_usd. The final version should look as follows:

amountcurrencyexchange_rateamount_usd
43.75EUR......
385.5GBP......
495.5GBP......
117.99GBP......
624USD......

Import package & Read csv file

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

# Read the CSV file into a DataFrame
orders = pd.read_csv('data/orders-2024-01-21.csv')
orders.head()

Request

# Start coding here...
url = 'https://api.vatcomply.com/rates'
params = {'base' : 'USD'
          , 'date' : '2024-01-21' 
         }
response = requests.get(url, params= params)
if response.status_code == 200:
    # Parse the JSON response
    exchange_rates = response.json()
else:
    raise Exception(f"API request failed with status code {response.status_code}")
exchange_rates

Total amount sold in USD

rates = exchange_rates.get('rates', {})
print(rates)
orders['exchange_rate'] = orders['currency'].map(rates)
print(orders)
orders['amount_usd']=orders['amount'] * orders['exchange_rate']
print(orders)
total_usd_sales = orders['amount_usd'].sum()
print(total_usd_sales)