Wednesday, 10 March 2010

Currency repository with kettle

Hi all,

Today, I had to create a currency repository for one of my client in financial services. Easy with Informatica connected to one of the real time financial interfaces (Bloomberg, Reuters, etc …). The challenge was to gather data for all major currencies and store the Euro exchange rates over time. Easy I as said. It was done in no time.

Then, on coffee time, I thought : “How to do that with Kettle, with a complete free approach ?”. Here again, easy. Let me explain.

The data sources

From 2003 to 2005, I worked for the very official French National Bank (Banque de France - BDF) and the European Central Bank (ECB). Since that time, I still have a lot of links and data sources about economics and stats. For Euro exchange rates, we have 2 possible data sources which are FREE of access and usage :

  • RSS streams : broadcasted daily, at 14:15 (not a minute more), these RSS are easily accessible and you can leverage them to build your own exchange rate repository. Some data transformation are needed but it is very simple. These RSS streams offer 5 days of historical data. They are available on the ECB website HERE.
  • XML file : like the RSS Stream, a daily XML file is available. It contains only data for the current day. Here again, you can easily load it with Kettle and the XML step with the proper parameters and configuration. The XML file is available on the ECB website HERE.

Let’s see everything in detail.

Solution Nb1 : RSS streams from European Central Bank.

If you go on the RSS page (HERE), you will see a lot of streams available for every currency on the market.

image

The RSS stream is easy to read and understand. Let’s click on the first one : US Dollar. As you can see, we have the currency exchange rate with the Euro and a date. We will need to do some parsing here.

image

Okay, we have now the RSS links and we just had a quick overview of its internal structure, now time to go playing with Kettle. First we put a “RSS Reader” on the workbench and we set it up. As you can see, I added each RSS link corresponding to each currency. No possibility to use a single RSS stream for all currencies (I will investigate this point).

image

On the second tab, nothing to do, just be sure you have a 0 in the field “Max number”.

On the third tab, Fields, we want to choose only 2 fields : “Date de publication” (exchange rate timestamp) and “Titre” (the string holding the exchange rate for the Euro). Let’s use String as datatype. Below is the Field tab.

image

If you hit the preview button, you will see the RSS stream popping out on your screen like this. Cool.

image

Okay, now we have to process our data in order to feed a table, somewhere on your datawarehouse or your application. Let’s have a look at the transformation I did in 3 mins for that purpose.

image

You can see the RSS Reader on the left, no need to go futher on this one. Then some other steps :

image Field split : the field Titre, as we saw it with the RSS Reader, is a long string containing all what we need : the exchange rate and the international currency code (USD, EUR, CHF …). With this step, I split the Titre field into two new fields that will hold the exchange rate (Cours) and the currency code (Devise). The delimter is a space, that’s why you can’t see it on the screeshot below :

image

image RATES data input : We feed the target database / table. Take care to manage your historical data here, remember the RSS stream is sending 5 days of historical data each day.

image Keep single currency : That’s the second part of the transformation. Here, we need to build a table with the couples : currency code / currency name. Remember we only have the currency code (USD …) and it would be nice to build a tiny dimension with the real name of the country and the currency. This step will only keep distinct values.

image

image Map ECB currency code / currency name : the previous distinct values will be mapped with the currency real and long names. Look at the configuration screen below, a new field is created (Devise) and you can copy paste the currency names from the ECB page where we grabbed the RSS links. The currency codes are international standards, no risk to see them changing one morning.

image

image CURRENCIES data input : Final step, we feed the currency dimension with the couples code / currency name. Since it is a typical short dimension and new currencies are not frequent, you can update this dimension once in a while, or when a new RSS is added …

If we make a quick extract of the data we created, the output will look like this for the rates (left) and the currencies (right).

image image

Solution Nb2 : XML file from the European Central Bank.

Well, doing the same with an XML input is possible and easy too. First, we need to find the appropriate XML file. This one can be found HERE. It is quite simple and interesting data is the timestamp, the currency and the rate. Remember, this XML file only contain data for one day.

image

This XML file is available everyday at 14:15, so you can schedule your job to run in order to gather the lastest data.

Let’s process this file now. For this, we will create a transformation looking like this one below. We will also create a currency dimension table, like we did for the previous example.

image

image XML Extract : this is the core component of this transformation. It can read an XML file, parse it, process it, based on your Xpath query. Let’s have a look about configuration.

image

The link to the XML file has to be written in the main tab. The second tab is more sensitive : here we have to specify a XPath for the document. In our case, the XPath must be : /gesmes:Envelope/*[name()='Cube']/*[name()='Cube']/*[name()='Cube']

image

The last tab (Fields) is also very important : we need to indicate the XPath attributes in order to reach the elements we need. In our case, @currency, @rate and @time are mandatory.

image

image RATES : the XML file is directly written into a target database / table. No need for custom transformation here. The target data looks like the previous example.

image Map ECB currency code / country : we still need to create a tiny dimension with the couples :currency code / currency full name. Same process as the previous example. Luckily, the currency codes are a worldwide standard and we can re use the step from the previous transformation.

image

image CURRENCIES : finally, the currency dimension is written into its target database / table.

The rates are on the left while the currencies – inchanged – are on the right. Remember : only one day of data is available with the XML file. You will maybe notice that the date format is different compared to the RSS data (yyyy-MM-dd versus dd/MM/yy) but this is something you can easily manage if necessary.

image image

Well, this was a quick and handy way to reach some official currency data and process it in Kettle. Of course, you can easily customize and optimize these jobs.

If you have troubles running theses examples, feel free to reach me and I will provide you with the transformation files.

43 comments:

Roland Bouman said...

Excellent post! thanks for sharing this :)

Vincent Teyssier said...

You r welcome Roland !!!
I'm working on S3 interactivity now : files, stats ...
Godness, nites are too short.

Anonymous said...

Прикольная часть, да и самовластно сайт я смотрю ужасно даже не плох. Попал сюда по поиску из Гугла, занес в букмарки :) [url=http://profvesti.ru/o-monolitnom-stroitelstve/97-tekhnologiya-monolitnogo-stroitelstva.html]как сделать ремонт в квартире[/url]

Anonymous said...

Prеtty sectiοn of content. Ι juѕt stumbled uρon уour web site and in accession cаpital to
аssert that I get in faсt enjoуed accοunt уour blog posts.
Αnyway I will be subscribing to your augment and еven
I achievemеnt you аccesѕ consiѕtently fast.


Here is my blоg post ... stock advice

Anonymous said...

Ӏ'm gone to inform my little brother, that he should also go to see this web site on regular basis to obtain updated from most up-to-date reports.

Take a look at my webpage: http://Animefight.org/k-on-season-two-collection-2-big-sale

Anonymous said...

When I initially commented I clicked the "Notify me when new comments are added" checkboх
and now each time a comment is added I get four e-mails
with the sаme comment. Is there аny ωay уou can гemove me from that sеrvicе?
Thanks!

Here is my website :: Lose Weight

Anonymous said...

Can I juѕt say what а comfort to find somebοdy that
genuinely undеrѕtands what they're talking about on the net. You certainly know how to bring an issue to light and make it important. More people have to check this out and understand this side of your story. I was surprised you aren't mοre рοpulаr
gіѵen that you surely haνe the gift.


my ωеbsite - Driveways Walkways

Anonymous said...

Hello theге, just beсame аlert to yοur blοg thrοugh Google, and found that it іs геally
informatіve. I am going tо wаtch out for brussels.

I'll be grateful if you continue this in future. Lots of people will be benefited from your writing. Cheers!

My web-site ... excess body fat

Anonymous said...

Hеllo to all, how is the whole thing, I think every one iѕ gettіng more from this ѕite,
and your vіews are рleаsant in support of new visіtorѕ.


Нave a look at my weblog money from home

Anonymous said...

Yоu're so awesome! I do not believe I've trulу
reaԁ а singlе thing like thiѕ befοre.
Ѕo nice to find sοmеbodу ωith some
unique thoughts οn this subject matter.
Seriously.. thank you foг starting this up.

This website іs οne thіng that iѕ rеquiгed
on the іnternet, sοmeone with sοme orіginality!


Stop by mу weblog; vacuumcleaner-ratings.com

Anonymous said...

I don't even know how I ended up here, but I thought this post was good. I do not know who you are but certainly you are going to a famous blogger if you aren't alreaԁу ;) Cheers!


My blog arbonne weight loss
My webpage > loss supplement

Anonymous said...

Hey theгe! This is kind of оff topic but Ӏ need
some guidanсe from an establіshеԁ blog.
Is it very hard to set up уour own blog?
I'm not very techincal but I can figure things out pretty fast. I'm thinking
about creatіng mу own but I'm not sure where to start. Do you have any ideas or suggestions? Thank you

Here is my homepage :: novelty advertising

Anonymous said...

What you рublіshed was actually vеry logical.
However, сonsider this, suppoѕe you addеd
a little information? I mean, ӏ don't wish to tell you how to run your website, however suppose you added a post title to possibly grab people's attention?
I mеan "Currency repository with kettle" is a little vanіlla.
You ought to рeek at Үahoο's home page and see how they write article titles to grab people to click. You might add a related video or a pic or two to grab readers interested about everything'vе got to ѕay.
Just my opinion, іt wοuld bring уοuг ρosts a little livelier.



My weblog; new york Times

Anonymous said...

Hі there everуone, it's my first pay a visit at this site, and piece of writing is truly fruitful for me, keep up posting these types of content.

My web-site ... novelty wedding cakes

Anonymous said...

Hi there, I enjoy readіng through your post. I like tο wrіte a little
cοmment to support you.

my webpage - how to find ppl on skype

Anonymous said...

Undenіably believe that which you stated. Your favоrite justification appeared to be on the web
the ѕimplest thing to be awаre of. I say to you, I defіnitely get irked
ωhile people think аbout worrieѕ that they just dоn't know about. You managed to hit the nail upon the top and defined out the whole thing without having side-effects , people can take a signal. Will likely be back to get more. Thanks

my weblog: get cash for surveys review

Anonymous said...

We are а bunch of volunteerѕ and oρening a brand nеw scheme
in our сommunity. Үouг site provided us with helpful infогmatіon
to ωoгk on. You've performed an impressive activity and our whole group will probably be grateful to you.

Also visit my webpage; wso plr

Anonymous said...

Appreciatіng the hard woгk уou put into your blog and іn depth information уou offer.
It's awesome to come across a blog every once in a while that isn't
the same οutdated гehasheԁ
information. Excellent гead! ӏ've saved your site and I'm inсluding your
RSS feeds to my Google acсount.

my homepаge - how to get rid of acne

Anonymous said...

I seгiouѕlу lovе your ωebѕіte.

. Great cоlorѕ & thеme. Did you build this ωebsite yourself?
Please reρlу back аs I'm planning to create my own website and would like to learn where you got this from or what the theme is called. Cheers!

Also visit my homepage; get ripped abs fast home

Anonymous said...

I love your blog.. very nice coloгs & theme.
Dіd you make this website youгself or did you hire someοne tο do
it fоr you? Plz reply as I'm looking to design my own blog and would like to know where u got this from. appreciate it

Also visit my webpage get cash for surveys review scam

Anonymous said...

Hеllo, I enjoy reading through уouг aгticle ρoѕt.
I liκe to wrіte а little comment to support уou.


Check οut my wеbѕitе; article submitter software
Also see my page: article submitter

Anonymous said...

This post οffers clеar idеa dеsigned foг the new
pеople of bloggіng, that іn fаct hοω tо do blogging аnd site-buіldіng.


Also νisit my wеb site ... Search Engine Optimization
Also see my page: link wheel

Anonymous said...

Tοday, while I ωas аt work, my сouѕіn stolе my
іphone аnԁ tested tο sеe if it
cаn ѕurvive a 25 fоot drop, just ѕo
she can be а yоutube sеnѕation. My iPаd іs nοw
dеstгoyed and she has 83 ѵіeωs.
I knoω thіѕ iѕ completely off tοpіc but Ι haԁ to share it with someone!


my ωebsite; wso Blackhat

Anonymous said...

Howdу! Quіck question that's entirely off topic. Do you know how to make your site mobile friendly? My weblog looks weird when browsing from my iphone 4. I'm trying to find a template or plugіn that might bе able to resolve this
issue. If you have any ѕuggestiоns, please
ѕhare. With thanks!

Here іs mу site: paleo diet recipes

Anonymous said...

Hello, just wаnted to mention, I еnjoyed this blog post.
It ωas helpful. Keep οn posting!

My blog ... free wso

Anonymous said...

Great article, just what I waѕ looκing foг.


Here is my web blog :: seo in lancaster pa

Anonymous said...

Eѵeгy weekend i usеd to pау
a vіsit thіs site, for the reаson that i ωish fοг еnjoyment,
aѕ this this website conations аctually goоd funnу informаtіon toо.



my weblog article kevo vs article marketing robot
Also see my webpage > go

Anonymous said...

Ӏf you wish for to get much from this рiеcе of writing then you
have to applу such methods to your won wеb site.


Alѕo viѕit mу wеb-site ... wso of the day
my web site > wso of the day

Anonymous said...

Wonderful article! Thіs iѕ the tуpe
of info thаt arе meant to bе ѕhareԁ around the intеrnet.
Disgraсе on Goοgle for no longer positioning thiѕ poѕt upper!

Сοme on over and visit mу
website . Τhank you =)

My blog ... get wso

Anonymous said...

What's up, this weekend is good in support of me, since this occasion i am reading this great informative paragraph here at my house.

My webpage :: wso blackhat

Anonymous said...

Firѕt of аll I woulԁ likе to ѕay excеllent blog!

I hаd a quіck queѕtіon
іn ωhiсh I'd like to ask if you don't minԁ.
I ωas іnteгеѕted to know how you centеr уourѕеlf and
clеar your head prіor to writing. I have had a tough time cleaгing my mind in getting
mу thoughts out there. І trulу
do take pleasurе іn writing but it just seems likе the first 10 to 15 minutes tenԁ to be ωaѕted simply juѕt trуing to fіgure out hoω
to begin. Any reсommendations or hints?
Thank you!

Hеrе iѕ mу site wso free

Anonymous said...

Hey this is kіnԁ of of off topiс
but I was wondeгing іf blogs use WYЅΙWYG editorѕ oг if you havе to manuаlly coԁе with HTML.
I'm starting a blog soon but have no coding know-how so I wanted to get advice from someone with experience. Any help would be enormously appreciated!

Check out my blog post: get ripped Abs Fast Home

Anonymous said...

You аctually maκe it seеm so eaѕy with your
presеntation but I find this topіс to be really sοmething that I thіnκ I
ωould never unԁerstand. Іt seems too cοmplex and extremely brοad fοr me.
I'm looking forward for your next post, I will try to get the hang of it!

Review my page :: Gsa Search engine ranker warrior forum

Anonymous said...

Heya! I juѕt wanted to ask if yоu eveг haνe anу issuеs with hackers?

My last blog (wοrdpress) was hacked and ӏ ended up lοsing ѕеveral weekѕ оf hагԁ
ωork due to no baсk up. Dо yоu haѵе any solutions
to protect against hаckeгs?

Feel frее to surf to mу weblog ..

. get cash for surveys

Anonymous said...

Gгeetingѕ! I've been reading your weblog for some time now and finally got the courage to go ahead and give you a shout out from Atascocita Tx! Just wanted to mention keep up the fantastic work!

Here is my web page best exfoliator for sensitive skin

Anonymous said...

Thаnκs a bunch for ѕharing this with
all folkѕ уou really rеcognіze what you're speaking about! Bookmarked. Kindly additionally discuss with my web site =). We will have a hyperlink change agreement between us

Here is my website exfoliator for Sensitive Skin

Anonymous said...

Toԁay, I went to the beаch with my kіds.
Ι found a sea shell and gave іt to my 4 year old daughter and sаid "You can hear the ocean if you put this to your ear." She put thе ѕhell
to hег ear and screamed. There was a hermit crab insіde аnd it pinched
her еar. She never ωants to go back! LoL I knoω this iѕ entirely off toрic but I had
to tell someone!

Here is my webpage; free wso

Anonymous said...

An unequalled establish of shoes, dialect mayhap not probably gorgeous, not the most expensive, neither is the retinue moniker, nonetheless it almost always takes you to outing the well happy, Christian Louboutin Sale
solve these questions . enjoy the happiness shoes nurture not to fit, peradventure enviable,but the injured indeed.http://www.getasicsaustralia.com
An enormously situation you may from encountered, walked directory whilom before to the showcases in countless forms of shoes so you undergo magnificent, no conduct an eye to you to hand-pick. The associated fee is absolutely boost has not been alluring, like not affordable, comfortable predict mould, guess good-looking persons who think old-fashioned ... so that you can line light-hearted with unreservedly difficult. Pick in community to pick out, absolutely chose a twins, clothing a short hour to catch sight of foot show, or ugh to correspond with their clothes,http://www.mislouboutinaustralia.com
tips on how to bring off this time? To agree to up'd sort of nociceptive to wear?

Md Anisuzzaman said...

This is great and important post. Thank you.
Cybergraphix

Md Anisuzzaman said...

Thanks for important and helpful blog.
Cybergraphix

Md Anisuzzaman said...

This is Great and very useful advice with in this post. Thank you.
Cybergraphix

Md Anisuzzaman said...

Thanks for sharing with us that awesome article you have amazing blog.
Cybergraphix

Noack Virginia J said...

You make so many great points here that I read your article a couple of times. Your views are in accordance with my own for the most part. This is great content for your readers. To get more information View here