You need to have calculation in Excel always with current value of foreign currency or stocks? You do not need to constantly check the website and manually write data in Excel. There is a simple solution for you – connect online table directly to Excel spreadsheet. How to do this, you will learn precisely in this manual.

There are a few ways to connect to the Internet table, which can be easily updated through Excel. All ways use the so-called web query tool.

I will show you two ways to get such a Web query. The first way will be easier because this instrument is integrated directly inside the browser Internet Explorer.

1) Web query through Internet Explorer

Open the browser Microsoft Internet Explorer. You just need to find a Web page with a table, for our example we choose page of ČNB (link here www.cnb.cz) with an online list of foreign exchange rates. Explorer has a built-in option that can export selected table directly to Excel. Perhaps you can think of why this option has only the Explorer :-).

The picture shows a table with currencies. Just click it in Explorer with right-click, the menu apperas and at the bottom select Export to Microsoft Excel. A new Excel file is created and the table is copied into it. However, it is downloaded with the option of updating directly from the site. The table in Excel is then possible to click inside and on a Data tab > click Refresh (or Refresh All). After that, the status bar displays information about running a web query and the table itself from the Internet resume with the new values. For this update you must be connected to the Internet.

2) Web query through Excel

The second way to connect to the Internet Web table, is to use Excel directly without depending on your favorite browser. Click on the Data tab> Get External Data > From Web. A special browser window appears (looks old) in which the top line you can enter the address, as in the first described procedure. This time we choose the address of ČSOB bank (link is here www.csob.cz). The loaded page also contains a table with foreign currencies. Importing of online tables through this process only works for tables that have a yellow arrow in the upper left corner in the browser window. Unfortunately, it does not work at all pages, it depends on how the page is created and coded. On that page but it works and you can try it without any problems. See the yellow arrow in the upper left corner of the table and after you tick the arrow it turns into a green “checkmark”. Then you simply complete the import by Import button in the lower right corner of the window.

One more small windows opens in which we choose a target cell where the table shell be inserted. Alternatively, press in this window Properties, as shown below, and set the minute interval in which the table is updated automatically. Again, it will also work through the Data tab > Update described in the first procedure.

If this guide has helped you, become a fan on Facebook and recommend this site to your friends, it can be useful for them too.

Similar posts about this topic

Leave a Reply

Your email address will not be published. Required fields are marked *