Results 1 to 13 of 13
  1. #1
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts
    What's the address of the site?
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  3. #3
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Catharine, I don't at all care where the information comes from.

    I use http://www.news.com.au/business/mark...es%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

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    Attached Files Attached Files

  6. #6
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  7. #7
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is on the "external data" toolbar for starters.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  9. #9
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you are anywhere in the querytable, you can also use Data, Refresh (from the menu)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  12. #12
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Bruno View Post
    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
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  13. #13
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •