Best Practice: XRate Retreival

Post Reply
User avatar
Eric
MVP
Posts: 373
Joined: Wed May 14, 2008 1:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2003
Location: Chicago, IL USA

Best Practice: XRate Retreival

Post by Eric » Mon Jun 02, 2008 8:10 pm

I know this is not TM1, but someone here is probably doing this. I know what I want in theory, but need some guidance in practice. At the End of the day I would like to Run a TI process to execute and grab the daily exchange rate from a website for two currencies.

Anyone have any ideas they are willing to share?
Regards,
Eric
Blog: http://tm1-tipz.blogspot.com
Articles: http://www.google.com/reader/shared/use ... /label/TM1


Production: 32 bit 9.0 SP2, Windows 2000 Advanced Server. Web: 32 bit 9.0 SP2, Windows 2000 Server. Excel 2003

User avatar
John Hobson
Site Admin
Posts: 329
Joined: Sun May 11, 2008 4:58 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007
Location: Lytham UK
Contact:

Re: Best Practice: XRate Retreival

Post by John Hobson » Mon Jun 02, 2008 8:39 pm

http://www.xe.com/dfs/

These guys can send a daily email with the rates (payable for commercial use)

You just need to be able to parse it in some way then. (Never tried - just cut an pasted for demos in the past)

Hope this helps

John
John Hobson
The Planning Factory

TM1 10.2.0 / Win7 / XL 2010

User avatar
Michel Zijlema
Site Admin
Posts: 706
Joined: Wed May 14, 2008 5:22 am
OLAP Product: TM1, PALO
Version: both 2.5 and higher
Excel Version: 2003-2007-2010
Location: Netherlands
Contact:

Re: Best Practice: XRate Retreival

Post by Michel Zijlema » Tue Jun 03, 2008 7:00 am

Another option (also commercial, but I think cheaper) is: http://www.oanda.com/channels/business/ ... shtml#data

Michel

TJMurphy
Posts: 71
Joined: Mon May 12, 2008 12:25 pm
OLAP Product: TM1
Version: PA 2.0.6 Local
Excel Version: Excel 2016

Re: Best Practice: XRate Retreival

Post by TJMurphy » Tue Jun 03, 2008 8:28 am

Does your general ledger system already get a feed of exchange rates from somewhere? Maybe you could grab it from there?

We get our treasury people (who have a Reuters system) to generate a query - they have an Excel add-in that can connect and pull down rates - and send it to us for import into TM1. Mind you, a Reuter's system would make either XE or OANDA look very, very cheap ;)

In my idle moments I've wondered about setting up a web query from Excel but I've never gotten very far in trying one out.

Tony

Paul Segal
Community Contributor
Posts: 275
Joined: Mon May 12, 2008 8:11 am
OLAP Product: TM1
Version: TM1 11 and up
Excel Version: Too many to count

Re: Best Practice: XRate Retreival

Post by Paul Segal » Tue Jun 03, 2008 8:39 am

Or you can scrape the values from an exchange rate web page into Excel and then either run code to upload the results directly to your exchange cube, or save a text file for TI to upload. A simple version that works well is at http://petko.bossakov.eu/category/in-english/ (page down a bit), or for different ways of doing web queries from Excel see http://msdn.microsoft.com/en-us/library ... e.11).aspx. It would kind of depend on whether you're wedded to a particular exchange rate site.

User avatar
mattgoff
MVP
Posts: 515
Joined: Fri May 16, 2008 1:37 pm
OLAP Product: TM1
Version: 10.2.2.6
Excel Version: 2016
Location: Florida, USA
Contact:

Re: Best Practice: XRate Retreival

Post by mattgoff » Tue Jun 03, 2008 10:09 am

I think the best option is Tony's-- I'm sure it's easier to grab the data from an internal ODBC source, and your data will be aligned with other internal users. If it is not available internally, the next best way to get it is to use Perl. You can write a really trivial script (literally a one-liner) using LWP to pull XML or CSV to a local file for TM1 to import. I'd strongly recommend licensing a data feed (as described by John and Michel). If you decide to screen scrape, your setup will be much more fragile (any layout changes will break your import), and you may be violating the site's license agreement.

Matt
Please read and follow the Request for Assistance Guidelines. It helps us answer your question and saves everyone a lot of time.

User avatar
bihints.com
Posts: 52
Joined: Tue May 20, 2008 8:56 am
OLAP Product: TM1
Version: 9.0.3
Excel Version: 2003
Contact:

Re: Best Practice: XRate Retreival

Post by bihints.com » Tue Jun 03, 2008 10:22 am

I don't think excel or external coding needs to be involved.

Download GNU wget for windows, it is a standalone .exe

in TI prolog:
executecommand('cmd /c \\path\to\wget.exe http://www.ecb.eu/stats/eurofxref/eurof ... ?id=536163' ,1);
+another executecommand to unzip that file

in TI data tab, parse and load the resulting .csv in your cube

done

Post Reply