Exchange Rates - Historical

[Centaur1]

Hello,

Can power query download historical FX rates but always to the current date? I have read some posts but it seems there is always a date range that is needin got be selected but the date range doesnt include to current date.  I would be interested in grabbing data from several years ago (mabye 10) to the current date.   This would be updated weekly so if it wasnt being revised with current date rates I woudl not be able to use it.  

thank you

[Centaur1]

Sorry...I thought I did accept the solution.  thank you for the help!

[v-pnaroju-msft]

Hi Centaur1,

We would like to inquire if the solution offered by @Akash_Varuna  and @rohit1991 has resolved your issue. If you have discovered an alternative approach, we encourage you to share it with the community to assist others facing similar challenges.

Should you find the response helpful, please mark it as the accepted solution and add kudos. This recognition benefits other members seeking solutions to related queries.

Thank you.

[v-pnaroju-msft]

Hi Centaur1,

Thank you @Akash_Varuna and @rohit1991 for your responses.

As @rohit1991 mentioned, Open Exchange Rates and CurrencyLayer provide historical currency exchange rates, but their pricing plans are different. Both services require a paid subscription to access historical data, though they offer free plans for real-time rates. Please check your requirements, such as the number of requests and update frequency, before choosing a plan.

The following services offer free plans:

1. freecurrencyapi.com
2. fixer.io

However, these free plans may have some limits, such as the number of requests allowed per month or the supported currencies. Please check their documentation for more details.

Thank you.

[Centaur]

Hello, thank you very much for the follow up. Much appreciated.  

[rohit1991 · Accepted solution]

Hi @Centaur1 
Yes, Power Query can fetch historical FX rates up to the current date dynamically:

1. Use an API: Find a service like Open Exchange Rates or CurrencyLayer that supports historical rates.

2. Dynamic Query: Use DateTime.LocalNow() in Power Query to set the end date dynamically. Example URL for Open Exchange Rates:

   https://openexchangerates.org/api/time-series.json?app_id=YOUR_API_KEY&base=USD&start=2014-01-01&end...<Current Date>

3. Parse Data: Expand the response (JSON/XML) into a tabular format.

4. Refresh Weekly: Set a weekly refresh under Query Properties.

Example script:
let
StartDate = "2014-01-01",
EndDate = Text.From(DateTime.LocalNow()),
Source = Json.Document(Web.Contents("https://openexchangerates.org/api/time-series.json", [
Query = [app_id = "YOUR_API_KEY", base = "USD", start = StartDate, end = EndDate]
])),
Rates = Table.FromRecords(Source[rates])
in
Rates

[Centaur1]

HI Rohit1991, thank you for the response.  

Is this a free service or do you need to sign up and pay to fetch the data?  

thank you...

[Akash_Varuna]

Hi ,
Yes, Power Query can fetch historical FX rates up to the current date using a reliable data source like an API.
Use dynamic date functions (e.g., Date.AddYears(DateTime.LocalNow(), -10)) in Power Query to set a rolling 10-year range.
Schedule weekly updates in Power BI Service to ensure the data stays current.
If this post helped please do kive a kudos and accept this as a solution

Thanks In Advance