Converting currency

user03144
user03144
in Magic ETL

I have some data which contains revenue but in USD, because the data vendor is US-based, but all our other numbers are GBP. Any ideas on a way to convert tjhis into GBP? Perhaps pull some public currency data into a table and then I can do a currency calculation in beast mode or a tranform? Mainly interested in where and how to pull the data

Best Answer

  • Valiant
    Valiant
    Answer ✓

    So I found the European Central Bank has a link with conversion rates on it:

    http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml

     

    And info on it:

    https://www.ecb.europa.eu/stats/policy_and_exchange_rates/euro_reference_exchange_rates/html/index.en.html

     

    So here's how you can put it to use (the data updates daily btw).

     

    Go to your Data Center and add a new Cloud App. Search for XML and choose the XML Advanced connector.

     

    First setup your XML account (this can be gibberish as it's not actually needed for this, but here's the info on the connector XML Info)

     

    Now here's the info you actually need:

    Connection Method set to 

    HTTP Request

    Enter the URL to your XML File: 

    http://www.ecb.int/stats/eurofxref/eurofxref-daily.xml

    Do you Require Additional Options: 

    Yes

    Do you Require Attributes in Data: 

    Yes

    Enter Fields to Exclude:

    subject,Sender_name,Cube_Cube_Cube

    Parser:

    Recursive XML Parser

    The rest you can leave blank or default. After that schedule your dataset and here's your results:

    image.png

     

    Hopefully that helps get you what you need.

     

    Sincerely,
    Valiant

     

    **Please mark "Accept as Solution" if this post solves your problem
    **Say "Thanks" by clicking the "heart" in the post that helped you.

Answers

Categories