0

Currency Conversion

Has anyone successfully implemented the European Central Bank's currency API for getting real-time currency exchange rates to use in a Ninox database?

I managed to use fixer.io to calculate the price a 3 services (VO, TL, and PR) based on the character count of a text field (Characters). I put the following code in the "On Click" field of a button field:

let mykey := "-redacted-";

let response := http("GET", "http://data.fixer.io/api/latest?access_key=" + mykey + "&base=EUR&symbols=USD,HUF");

if response.error then

alert(text(response.error))

else

let fx := response.result;

let fxusd := fx.rates.USD;

let fxhuf := fx.rates.HUF;

let fxusdx := round(sqrt(Characters / 5 / 3 / 60 / 0.000045) / 10) * 10;

let fxeurx := round(number(fxusdx) / number(fxusd) / 10) * 10;

let fxhufx := round((number(fxusdx) / number(fxusd) * number(fxhuf)) / 1000) * 1000;

let hufPR := ceil(Characters / 100) * 300;

let hufTL := ceil(Characters / 100) * 600;

'VO (USD)' := fxusdx;

'VO (EUR)' := fxeurx;

'VO (HUF)' := fxhufx;

'PR (HUF)' := hufPR;

'TL (HUF)' := hufTL

end

 

Then I discovered that the ECB has a similar API which I'd love to implement, seeing as it seems to be free for unlimited use without registration. (fixer.io has a limit of up to 1000 calls per month for registered users of the free service).

 

Please forgive the less inefficient math. We are still working on the pricing formula logic. Also, fixer.io calculates everything from EUR as a base currency.

2 replies

null
    • Admin.7
    • 2 yrs ago
    • Reported - view

    I would like to have the same 

    • Medgyesy_Andras
    • 2 wk ago
    • Reported - view

    I have worked this out with ChatGPT recently. I use my bank's currency api, but it doesn't provide data for the holidays, so I decided to use the last available rate within 10 days. There are code lines which seems unnecessary (alert, text(blabla), so on), but without them it doesn't work.

    if text('Invoice currency') = "HUF" then
    'Original invoice value'
else
    let maxRetries := 10;
    let retries := 0;
    let rate := 0;
    let exchangeDate := 'Invoice date';
    let foundValidRate := false;
    while foundValidRate = false and retries < maxRetries do
        let JSON := do as server
                http("GET", "https://www.otpbank.hu/apps/exchangerate/api/exchangerate/chart/" +
                text('Invoice currency') +
                "/" +
                format(date(exchangeDate), "YYYY-MM-DD") +
                "/" +
                format(date(exchangeDate), "YYYY-MM-DD"))
            end;
        text("JSON Response: " + text(JSON));
        if JSON.error then
            text("Currency API Error: " + JSON.error);
            foundValidRate := true
        else
            let rateRaw := item(JSON.result, 0).foreignExchangeSellingRate;
            if rateRaw and rateRaw > 0 then
                rate := number(rateRaw);
                foundValidRate := true;
                text(formatJSON(JSON))
            else
                retries := retries + 1;
                exchangeDate := date(exchangeDate) - 1;
                text(formatJSON(JSON));
                text("Rate was 0 or invalid. Trying previous day. Retries: " + text(retries))
            end
        end
    end
;
    if foundValidRate = true and rate > 0 then
        number(rate) * number('Original invoice value')
    else
        alert('Invoice code' + "Could not find a valid exchange rate after " + text(retries) +
        " retries.")
    end
end
Login to reply

Content aside

  • 2 wk agoLast active
  • 2Replies
  • 616Views
  • 1 Following