Import current currency rate into Excel

Reading time 2 minutes

Aktualisiert – Januar 31, 2024

Excel, known to Windows and Mac users alike, has long offered the option of copying web content into your own tables.
However, data on websites that are only accessible after login cannot be accessed.

The procedure in Windows and Mac OS is slightly different. Excel 2016 for Mac OS is described here.

When installing Excel, in the user folder (e.g. xyz) /Users/xyz/Library/Group Containers/UBF8T346G9.Office/User Content.localized/Queries two sample files ending with .iqy each stored with a search query.

The files can be edited using the text editor Text edit to be edited.

Case study

Suppose you want to query the dollar rate on the finanzen.net website, which is available on this website, among others website can be found.

After copying the web address and opening the sample file SampleWebQuery01.iqy you delete their content and replace it with

WEB
1
https://www.boerse.de/devisen/Euro-Dollar/EU0009652759

The file can be found at e.g dollarkurs.iqy get saved.

Integration into your own file

Open your own Excel file and create a new worksheet within it. Position the cursor in cell A1 of the new worksheet and click on the entry at the top of the program header bar (not in the worksheet window bar). Data, there further down Retrieve external data, followed by Execute web query click. In the Finder window that opens, the file you just created dollarkurs.iqy select by double-clicking.

This is followed by data retrieval from the URL contained therein. After a few seconds the content appears in the table.

What is noticeable is that the price is displayed as, for example, “1.0857 USD”, i.e. as a text string, not as a real number. It is also not enough to define this cell as a “number” using cell formatting. Here the string must first be reduced to the numeric characters and then converted into a real number.

The string is reduced by entering the formula

=LEFT(B9;8)

It causes eight characters to be taken from LEFT and the rest to be deleted.

The conversion to a real number causes a multiplication by 1,0, which means the entire formula is:

=LEFT(B9;8)*1.0

Now go to your own table, select the field in which the dollar rate should be displayed and click on that at the top of the start menu Sum sign click. After switching to the new worksheet, select the desired cell with the - now pure - dollar rate and press the Enter/Return key complete. The dollar rate is now in the desired field. Don't forget to save!

Content update

The next time you open the file, a window will appear in which you can update the content so that the current course is displayed after opening the file.