Automatic Currency Data and Time Generation

Hello guys,

I want to make a historical dataset of the latest currency rates values, in two columns.
Occurrence Time and Rate.
for example;
I've connected to "MSN Money" service via the following path:
Data > Existing Connections > "MSN MoneyCentral ..." service. as you can see in the uploaded file. It provides latest currency rates every refresh interval.
I've set the values to be refreshed every 1 min.
Now, I want two columns, one (Rate column) representing every 1min value of Euro/USD to be located below the last recorded one dynamically, and one (Date Column) to record the occurrence date/time next to rate column automatically, as soon as rate values are generated.

It means if I leave it alone for one hour, I should expect to see a 60x2 table containing below sample information which are generated automatically:

12/11/2014 12:00 1.2445
12/11/2014 12:01 1.2446
12/11/2014 12:02 1.2443
.
.
.
12/11/2014 12:59 1.2440

Any clue would be highly appreciated

hi aschkant, welcome to Excelforum, though you have made a post in Formulas section this is VB option of doing that. As there is time lag between refershing data and actual output to the sheet the records might have time intervals shown more than a minute.
Open file, Sheet3, wait and check

Just for your information there is a bundle of different Forex trading platforms around that allow export of currence quotes historical data to Excel or CSV files.

Hi Watersev,
You're brilliant, and that's it
Thank you so much.
Would you please share the source code so I can use it in similar situations again?

Sheet Currency code:

Please Login or Register  to view this content.

Hi waterserv
I am also looking for MSN on-line currency exchanger. I do not know if I use your file correctly.
When the file is opened to Sheet3,then I go to the "currency" worksheet, there is a table of currency
exchange. So I play around try to refresh, in Data menu > Refresh All, the table disappeared leaving
text format. (I enable the security content)

Is there a marco button to refresh and update the table without loosing the table?


Thanks