Getting real-time data from Web to Excel

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.

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

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

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

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.

Then at the Developer tab, select Visual Basic:

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:

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 :)