I’m working on a project that requires currency conversions between the US dollar and the Euro. In the most basic case, the project has two requirements: backfill historical Euro amounts and get current Euro amounts using a transaction date and USD value. Plugging into an API to get the current currency exchange rate is simple enough, but finding an open or free dataset with 10+ years of currency transactions was another thing. Google and GPT/LLMs are littered with what appear to be free sites, but they are limited to ~100 API calls, or their dataset is not deep enough for my use case. I landed on a great tool provided by the Federal Reserve named FRED, short for Federal Reserve Economic Data. FRED is a free site with APIs sitting on a treasure trove of data. When I started this project, I simply went to the FRED and downloaded the dataset I needed (link), but I wanted to ensure that my process was current and could handle new transactions for years to come. Using the FRED API requires signing up for a free account. You will want a FRED API key to follow along with this demo.

What I’m going to demo in this post: creating a FRED account, using PowerShell to read from an Excel file, querying an API, writing back to the Excel file

FRED account:
Visit the https://fred.stlouisfed.org/ site, click My Account (top right), and click Create New Account when the modal opens. After you’ve created an account, navigate to the My Account page and click API Keys in the left nav. On the API Keys page, click the Request API Key button, input some text in the description box, click the agreement checkbox, and then click Request API Key. Link to the API Key page: https://fredaccount.stlouisfed.org/apikeys

For this demo, I’ve created a simple Excel file with the following columns and datatypes: TransDate (date), USD (currency), ConversionDate (date), EUR (currency)

To interact with an Excel file from PowerShell, I went with the ImportExcel module. In VsCode or your IDE of choice, run this command: Install-Module ImportExcel -Scope CurrentUser

I will test reading from the Excel file, loop through the rows, and output their values to get the ball rolling and ensure the ImportExcel module works.
$excelPath: location of the Excel file
$worksheetName: name of the worksheet/tab where the data is stored (optional)
$excelData: imported Excel data

$excelPath = "C:\code\CurrencyDemo.xlsx"
$worksheetName = "Historical"
$excelData = Import-Excel -Path $excelPath -WorksheetName $worksheetName

foreach($row in $excelData){
    Write-Output "Transction date: $($row.TransDate) Amount: $($row.USD) USD"
}

Next, I will test my connection to the FRED API, returning a sample transaction. There are two important things to note in the next script. The $series variable is bound to the USD to Euro Spot exchange rate value; if you need to work with a different currency, visit the Daily Rates page and filter by the Geographies or use the site search if you cannot find what you are looking for there. If you type Peso in the site search, it will suggest the Mexican Peso to U.S. Dollar. Clicking on the search result will open the page for that conversion, and the page will reveal the $series value needed for the conversion. The Peso to USD series is DEXMXUS (look at the URL or the value to the right of the conversion overview). The next important variable to note is $date; this is obvious for this example, but you can query the API for larger data ranges if needed and work with the larger API response.

# Your FRED API Key
$apiKey = "75fa2e6ce85_taco_805016ea4d764c5"

# Set the parameters
$series = "DEXUSEU"  # This is the series ID for USD to Euro exchange rate
$date = "2024-01-16"

# Construct the API URL
$url = "https://api.stlouisfed.org/fred/series/observations?series_id=$series&observation_start=$date&observation_end=$date&api_key=$apiKey&file_type=json"

# Make the API request
$response = Invoke-RestMethod -Uri $url -Method Get

# Check if we got a result
if ($response.observations.Count -gt 0) {
    $usd_to_eur_rate = [double]$response.observations[0].value
    $eur_to_usd_rate = [math]::Round(1 / $usd_to_eur_rate, 4)
    Write-Output "The USD to Euro conversion rate on $date was: $usd_to_eur_rate"
    Write-Output "The Euro to USD conversion rate on $date was: $eur_to_usd_rate"
} else {
    Write-Output "No data available for the specified date."
}

In the last script for this demo, I will combine all the parts and provide an example for dealing with input dates that are Saturday or Sunday. From what I’ve learned on this journey, currencies are not typically traded seven days a week, so if an input date falls on a weekend, there needs to be an offset to the preceding Friday. This script must be extended in a production scenario to deal with major holidays.

function CurrencyConversion {
    param (
        $convDate,
        $usdAmount
    )

    # Parse the input string into a datetime object
    $parsedDate = [datetime]::ParseExact($convDate.Date, "M/d/yyyy HH:mm:ss", [Globalization.CultureInfo]::InvariantCulture)
    $apiDateValue = $parsedDate.ToString("yyyy-MM-dd")

    # Your FRED API Key
    $apiKey = "75fa2e6ce85_taco_805016ea4d764c5"
    $seriesId = "EXUSEU"

    # Construct the API URL
    $apiUrl = "https://api.stlouisfed.org/fred/series/observations?series_id=$seriesId&api_key=$apiKey&file_type=json&observation_start=$apiDateValue&observation_end=$apiDateValue"

    # Make the API call
    $response = Invoke-RestMethod -Uri $apiUrl

    # Check if there are any observations for the given date
    if ($response.observations.count -gt 0) {
        # Assuming the first observation is the one we're interested in
        $usd_to_eur_rate = [double]$response.observations[0].value
        $eur_to_usd_rate = [math]::Round(1 / $usd_to_eur_rate, 4)
    }
    else {
        Write-Host "No data found for ................................................ $parsedDate"
    }

    $convertedValue = $usdAmount * $eur_to_usd_rate
    return $convertedValue
    
}

function DateConversion {
    param (
        $conversionDate
    )

    # Check if 'conversionDate' is not null or empty
    if (-not [string]::IsNullOrWhiteSpace($conversionDate)) {
        # Parse the input date into a datetime object
        $targetDate = [datetime]::Parse($conversionDate)

        # Check if the day is Saturday or Sunday
        if ($targetDate.DayOfWeek -eq [DayOfWeek]::Saturday) {
            $conversionDate = $targetDate.AddDays(-1).ToString("yyyy-MM-dd")
        }
        elseif ($targetDate.DayOfWeek -eq [DayOfWeek]::Sunday) {
            $conversionDate = $targetDate.AddDays(-2).ToString("yyyy-MM-dd")
        }
    }

    return $conversionDate
}

$excelPath = "C:\code\CurrencyDemo.xlsx"
$worksheetName = "Historical"
$excelData = Import-Excel -Path $excelPath -WorksheetName $worksheetName

foreach ($row in $excelData) {

    $transDate = $row.TransDate
    $amountUSD = $row.USD
    $submittedDate = $null

    # Get the date for the currency conversion
    if (-not [string]::IsNullOrWhiteSpace($transDate)) {
        $submittedDate = DateConversion -conversionDate $transDate
    }

    # Check if both Submitted Date and USD are not null or empty
    if (-not [string]::IsNullOrWhiteSpace($submittedDate) -and 
        -not [string]::IsNullOrWhiteSpace($amountUSD)) {
        $convertedValue = CurrencyConversion -convDate $submittedDate -usdAmount $amountUSD
    }

    Write-Output "Converted value for $($amountUSD) USD on $($submittedDate.ToShortDateString()): $convertedValue"

    #update the excel row with the output
    $row.EUR = $convertedValue
    $row.ConversionDate = $submittedDate
}

# Export the updated data to Excel
$excelData | Export-Excel -Path $excelPath -WorksheetName $worksheetName 

To streamline the script, I created two helper functions. One handles the weekend-to-Friday conversion, and the other makes the API call to FRED. The script will loop over all of the rows in the spreadsheet, handle the currency conversion, and then bulk-write the output to the target Excel file. The highlighted values notate where a weekend date was passed, and the script handled the offset to the preceding Friday.

Yes, some places in the script need improvement, but I wanted to provide a simple example for handling currency conversion with PowerShell and historical dates. As always, please don’t hesitate to reach out or leave a comment if any part of this doesn’t make sense or if there’s a more-better way of doing things.

Note:
Be mindful of the number of calls you make to the API in a given timeframe. I was testing this process and hammered on the API with ~1,000 calls and hit an API limit error. Adding a simple pause to the script fixed the problem. i.e. after X calls, pause for X seconds.