Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

API currency rates Polish National Bank

Highlighted
7 - Meteor

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?

Highlighted
16 - Nebula
16 - Nebula

Try the attached:

2018-01-04_17-43-17.jpg

 

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.

 

Highlighted
7 - Meteor

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?

 

 

16 - Nebula
16 - Nebula

 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. 

Highlighted
7 - Meteor

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.

Highlighted
16 - Nebula
16 - Nebula

I would suggest using a multi row formula to get the next day for a currency code

 

You can then use a generate rows tool to fill in the missing days.

 

Have amended the sample

Highlighted
7 - Meteor

Thanks! This works just perfect

Labels