Creating exchange rates from the extractor output

by martin.matejka@keboola.com


Description: As the currency extractor shows only conversion from EUR to other currencies, here’s the SQL statement to create conversion table in the currencies we want.


Tables:


  • <exchange_rates>


Columns:

  • <date>

  • <rate>

  • <toCurrency>

  • `eur_FROMcurr` - rate column representing the currency we want to convert FROM

  • `eur_TOcurr` - rate column representing the currency we want to convert TO

  • `FROM_TO_rate` - final exchange rate column



#creating FROMcurr_TOcurr conversion table (starting on 2010-01-01)

CREATE TABLE `FROMcurr_TOcurr_temp` AS

SELECT

t.<date>,

t.<rate> AS `eur_TOcurr`,

f.<rate> AS `eur_FROMcurr`

FROM <exchange_rates> t

LEFT JOIN (SELECT

<date>,

<rate>

FROM <exchange_rates>

WHERE <toCurrency>='FROMcurrency' AND <date> >= '2010-01-01') f

ON t.<date>=f.<date>

WHERE <toCurrency>='TOcurrency'

AND t.<date> >= '2010-01-01';


CREATE TABLE `FROMcurr_TOcurr` AS

SELECT

<date>,

`eur_FROMcurr`/`eur_TOcurr` AS `FROM_TO_rate`

FROM `FROMcurr_TOcurr_temp`;



Examples:

  • `FROMcurr_TOcurr_temp` temp table


  • `FROMcurr_TOcurr` output table



Example CZK to USD SQL


#creating czk_usd conversion table

CREATE TABLE `czk_usd_temp` AS

SELECT

cz.`date`,

cz.`rate` AS `eur_czk`,

us.`rate` AS `eur_usd`

FROM `exchange_rates` cz

LEFT JOIN (SELECT

`date`,

`rate`

FROM `exchange_rates`

WHERE `toCurrency`='USD'

AND `date` >= '2010-01-01') us

ON cz.`date`=us.`date`

WHERE `toCurrency`='CZK'

AND cz.`date` >= '2010-01-01'

;


CREATE TABLE `czk_usd` AS

SELECT

`date`,

`eur_usd`/`eur_czk` AS `czk_usd`

FROM `czk_usd_temp`;


Comments