Getting real-time data from Web to Excel

October 29, 2018

An undergraduate student asked me how to get real-time data from a website into an excel. The data is used for his homework assignment, but many financial data are not free of charge. Therefore I gave him a simple solution, which I am going to show you how as well:

First, open Microsoft excel 365 and navigate to the Data tab. There is an option to get data from other sources and we select Web as an example.

1  vce  2j0rBSOtMxvecvyeBA

Next, we will input the URL which has the data you want to get from:

1  sN9  A9lEq  g5y5CQK1M4XA

The navigator would then gives you the option with tables for you to choose from. We select Table 8 here as an example:

1  sfBSgUqcRC6rVuRgboO9rw

Once this is done, the data is import to your excel already. However, right now it requires a manual refresh. In order to auto-refresh the data, we could right-click on the query to change the properties with a refresh every 1 minute.

1  kKZI  UeeuHIGEqkWt12ktg

This is doing pretty good already and performing an auto-refresh. What if you are more aggressive and want nearly real-time data every second? Then you would need to write some code. Navigate to File -> Options -> Customize Ribbon, under the Main tabs, you can check and add the Developer tab.

1    KcHG5kRiwlL0KWSu0WrRg

Then at the Developer tab, select Visual Basic:

1  uHeE85uQYtpU  5yPbUMPJg

And select Insert -> Module, then copy and paste the code snippet below:

Sub refresh_data() Sheets(“Sheet2”).Select Range(“B2”).Select Selection.Copy

Sheets(“Sheet1”).Select Dim xCell As Range For Each xCell In ActiveSheet.Columns(1).Cells If Len(xCell) = 0 Then xCell.Select Exit For End If Next ActiveSheet.Paste

Sheets(“Sheet2”).Select ActiveWorkbook.RefreshAll Application.OnTime DateAdd(“s”, 1, Now), “Refresh_data” End Sub ​

Like this:

1  RoFmqUh  F2YsWBGnIXN7mA

Click run and we are done! This code snippet can be translated into simple English: select sheet 2, copy the current value, go to sheet 1, select the last row of the column then paste the value, finally move to sheet 2 and trigger refresh all, finally trigger itself every second. We can now achieve our goal to get nearly real-time data to excel.

Leave a comment below if you have any further questions :)


Written by Victor Leung who is a keen traveller to see every country in the world, passionate about cutting edge technologies. Follow me on Twitter