How do I import web data into a cell

Topic

New Reply

I do some buying using different currencies. I have set up a spreadsheet with an exchange rate that I manually obtain from the Web.
What I would like to have is: the Net open, then open my spreadsheet and have (say) cell D2 be linked to a site that give me the US$/AU$ exchange rate – then all other cells will reconfigure themselves on D2 as it is a absolute cell reference in my sheet.
So: presently the exchange rate is 1 US $ to 0.8646 Au $ (9 Feb 2010)

Any takers? Bruno Terlingen

Reply | Quote

Replies

What’s the address of the site?

Reply | Quote

Catharine, I don’t at all care where the information comes from.

I use http://www.news.com.au/business/markets?nicmp=bus_market_currencies&nipchn=google_search&niseg=australian_dollar_exchange&nipkw=exchange%20rates%20australia
to get the raw data, where I look at the present exchange rate and copy that across manually to cell D4.
What I am looking for is a direct link to the US$/AU$ exchange rate. I have tried a number of sites and yest they all give the current exchange rate but none appear to be “linkable”.

I hope that you get my drift.

Bruno Terlingen

Reply | Quote

You can import the entire currency tabel into Excel directly using Data, Get external data, new web query.
Then use VLOOKUP formulas or INDEX and MATCH formulas to extract the currency you want from the table.

Reply | Quote

Bruno,

Go to Data, Get External Data, Choose From Web.
Navigate to the page and find a table containing the data.
Click on the yellow box next to the table you want
click import

Most currency site I’ve seen will either present the data in a table or in drop downs, not as individual table cells that you can choose so you’ll have to manipulate the data from there. I’ve attached a quick example. The data comes from http://www.x-rates.com/

Missed your link the first time through… The sheet also contains the table from your link.

Reply | Quote

Thank you. I have had a quick look but will delve deeper into the issue this afternoon. I may be able to import the above worksheet into my own and modify sheet accordingly.
Bruno Terlingen

Reply | Quote

By golly I think I am getting there, importing lots of tables, some with negative numbers. Though the small “update/refresh” box has gone AWOL, thus I can’t refresh. Can some kind soul find this refresh box please and show me how I can anchor it.

Bruno Terlingen

Reply | Quote

It is on the “external data” toolbar for starters.

Reply | Quote

Hartelijk bedankt Meneer Pieterse, I found it – thank you.

I also found that if I highlighted the imported table and right mouse clicked, I could use the option at the bottom of the drop down box called “Refresh Data”.

I thank you all for the contributions. This is yet another new ball game for me. I my even be able to “refresh” the weather (that is a novel idea), without having to go to the actual website.

Bruno Terlingen

Reply | Quote

If you are anywhere in the querytable, you can also use Data, Refresh (from the menu)

Reply | Quote

Thank you sir, I found that one also. I did import two weather tables – what a God-sent, no more having to actually logon to the weather site – just hit refresh and we get “new” weather.
Now if I could only manipulate the exchange rate, by getting others to refresh my data.

Bruno Terlingen

Reply | Quote

Thank you sir, I found that one also. I did import two weather tables – what a God-sent, no more having to actually logon to the weather site – just hit refresh and we get “new” weather.
Now if I could only manipulate the exchange rate, by getting others to refresh my data.

Bruno Terlingen

Hi Bruno

You can have the data imported when you open the workbook automatically. In the attached example I have added a macro into the on open event of the workbook

– alt + F11
– on the left double click ThisWorkbook

and you will see the code.

If you are using this for personal use you might want to set macro security to low
If you set the macro security to medium you will be prompted to allow the macro
If you set the macro to high it will not run.

You could, as an alternative, record a macro

– tools 0 macro – new
– name the macro and assign a shortcut
– selct the range that you want to update
– right click – refresh data
– click on a blank cell and then stop the macro

Assign the macro

– Select an autoshape from the drawing tool bar
– Right click and choose to assisn a macro
– click on the name of the macro you have just recorded and exit
– You can name the shape by right clicking it and choosing add text

Now when you open the workbook you can click the button at anytime and if there is any new data it will download it.

HTH

Reply | Quote

Thank you Mr Bunter, it has been about 15 years ago since I last played with macros in Excel, so I will need some brushing up. I will certainly look at the underlying workings of your downloaded sheet and see how I can modify same to suit my needs.

Regards, Bruno Terllingen

Reply | Quote