Dear Members,
We are basically trying to automate the Currency exchange data in TM1. we don't have any source for loading FX values in TM1, thus we need to rely on websites to feed the data into TM1.
But every day gathering information from website and manually create the excel files is a pain, so did any one has automated this process (Excel file will be generated every day by picking up values from website automatically and we can use this file to load values into TM1 cube on daily basis).
Cheers
Automation of Currency data from the open source into TM1
- macsir
- MVP
- Posts: 785
- Joined: Wed May 30, 2012 6:50 am
- OLAP Product: TM1
- Version: PAL 2.0.9
- Excel Version: Office 365
- Contact:
Re: Automation of Currency data from the open source into TM
Sounds like you need a web programmer to extract a fixed format of data file for you rather than a TM1 developer.
-
- Posts: 98
- Joined: Sat Feb 11, 2012 11:13 am
- OLAP Product: TM1 9x, BPC, Hyperion, HANA
- Version: TM1 10
- Excel Version: Excel 2003 - 2010
Re: Automation of Currency data from the open source into TM
I am sure there is plenty of ways to skin this cat but from the top of my head !!
Option 1
1) Use Powershell to call a Web Service and then load the CSV into TM1. http://blogs.technet.com/b/heyscripting ... -2009.aspx
2) Fetch Website data into Excel using VBA and then save it as a CSV and use TI into TM1.
I am sure others will have a few awesome suggestions.
Cheerio
Option 1
1) Use Powershell to call a Web Service and then load the CSV into TM1. http://blogs.technet.com/b/heyscripting ... -2009.aspx
2) Fetch Website data into Excel using VBA and then save it as a CSV and use TI into TM1.
I am sure others will have a few awesome suggestions.
Cheerio
- Mike Cowie
- Site Admin
- Posts: 482
- Joined: Sun May 11, 2008 7:07 pm
- OLAP Product: IBM TM1/PA, SSAS, and more
- Version: Anything thru 11.x
- Excel Version: 2003 - Office 365
- Location: Alabama, USA
- Contact:
Re: Automation of Currency data from the open source into TM
As Macsir says there is likely some programming involved here - the data you're looking for can likely be extracted from the web pages you're currently using or through other web services that provide this data. Some examples here:
http://stackoverflow.com/questions/4873 ... webservice
Once you've chosen an option/source there are quite a few ways to get at that information, like Nick points out. There are many examples you can find out there for using web services and web content in your programming language of choice (including VBA). You can turn that data into a format that is Excel or TM1 friendly (like CSV) and even schedule it to happen automatically as often as needed.
One new option for getting that data that's more directly TM1-integrated would be Turbo Integrator Java Extensions (new to TM1 10.2.2). Java Extensions involve developing classes (basically a class with a function of the same name as the class that has to follow a rigid structure in order for TI to use) that can be called from TI (via ExecuteJavaS and ExecuteJavaN functions). You could make your web service calls in one of those classes (Java includes the building blocks to do this) and then either have your class return a specific exchange rate to TM1 or write all rates that you need to a file that you can load in a later step. Up-to-the minute info on Java Extensions is not in the official IBM documentation right now, but can instead be found here:
https://www.ibm.com/developerworks/comm ... 3c4a9c8010
Hope that helps.
Regards,
Mike
http://stackoverflow.com/questions/4873 ... webservice
Once you've chosen an option/source there are quite a few ways to get at that information, like Nick points out. There are many examples you can find out there for using web services and web content in your programming language of choice (including VBA). You can turn that data into a format that is Excel or TM1 friendly (like CSV) and even schedule it to happen automatically as often as needed.
One new option for getting that data that's more directly TM1-integrated would be Turbo Integrator Java Extensions (new to TM1 10.2.2). Java Extensions involve developing classes (basically a class with a function of the same name as the class that has to follow a rigid structure in order for TI to use) that can be called from TI (via ExecuteJavaS and ExecuteJavaN functions). You could make your web service calls in one of those classes (Java includes the building blocks to do this) and then either have your class return a specific exchange rate to TM1 or write all rates that you need to a file that you can load in a later step. Up-to-the minute info on Java Extensions is not in the official IBM documentation right now, but can instead be found here:
https://www.ibm.com/developerworks/comm ... 3c4a9c8010
Hope that helps.
Regards,
Mike
Mike Cowie
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
QueBIT Consulting, LLC
Are you lost without Print Reports in Planning Analytics for Excel (PAfE)? Get it back today, for free, with Print Reports for IBM Planning Analytics for Excel!
-
- Posts: 23
- Joined: Wed Feb 01, 2012 5:46 pm
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 2007
Re: Automation of Currency data from the open source into TM
May be I need to put my hands on programming as well look like..
Any way thanks for your solutions.
Cheers
Any way thanks for your solutions.
Cheers
-
- Community Contributor
- Posts: 127
- Joined: Wed Oct 14, 2009 7:46 am
- OLAP Product: TM1
- Version: 9.4
- Excel Version: 11
- Location: London