Hi,
I'm trying to download the fx rates of the Polish National Bank using their API ( http://api.nbp.pl/en.html )
Currently I was unable to do so, therefore I manually downloaded the XLS overview http://www.nbp.pl/kursy/archiwum/archiwum_tab_a_2017.xls and started from there.
I have attached the flow that I came up with so far but it's not doing the trick.
The result I'm trying to achieve is a table simple table containing the date / currency / rate.
In addition, I need to have a currency for each day (weekends are not provided by the nbp) or a formula where the fx rate will be calculated on the previous date if the date is not included ( comparable as a vlookup,true function in excel)
I would really appreciate if you could get me started on this one.
Is there anyone that did the same exercise?
Solved! Go to Solution.
Try the attached:
The REGEX tool in parse mode splits the JSON Name into fields:
0\.([^\.]+)\.?(\d*)\.?(.*)
The first group '([^\.]+) will have the table level field names (table, no, effectiveDate, rates)
The second target gets the row of the rates data
The final group gets the field of the row (currency, code, mid)
After this, you can then cross tab the results to get data in the format you need.
Thank you for your quick reply, this indeed does the tric.
However, I'm only getting the FX rates of today, while, based on the website http://api.nbp.pl/en.html I should be able to see all of them.
Could this be caused by the link or Alteryx?
You need to use a different URL to get the history of rates:
http://api.nbp.pl/api/exchangerates/tables/{table}/{startDate}/{endDate}/
I have updated the workflow to cope with data from this end point and changed it to request the last 3 months.
The API has limit of 93 days for a single request.
Thanks, any advice on the vlookup;true for the missing dates?
An other option is probably to add date lines and copy the rates of the date -1.
Really appreciate your help here.
Thanks! This works just perfect