Retrieving Real-Time Data from the Web to Excel

An undergraduate student recently asked me how to import real-time data from a website into Excel for a homework assignment. Many sources for financial data charge fees, so I offered him a simple, free solution. Now, I’ll share that solution with you as well.

First, open Microsoft Excel 365 and go to the Data tab. Look for the option to Get Data from Other Sources and select Web.

Next, input the URL containing the data you want to retrieve.

The Navigator will then display various tables to choose from. For this example, we’ll select Table 8.

Once this is done, the data will be imported into your Excel spreadsheet. However, it will need manual refreshing. To automate this, right-click on the query and change the Properties to Refresh Every 1 Minute.

That works well for minute-by-minute updates. But what if you want nearly real-time updates every second? In that case, you’ll need to write some code.

Navigate to File -> Options -> Customize Ribbon. Under Main Tabs, enable the Developer Tab.

Then, in the Developer tab, select Visual Basic.

Choose Insert -> Module and 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.Value) = 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

Click Run, and you’re all set! This code can be translated into simple steps: select Sheet 2, copy the current value, go to Sheet 1, find the last row of the first column and paste the value there, then return to Sheet 2 and refresh all data. Finally, the code will trigger itself every second, allowing you to achieve nearly real-time data updates in Excel.

If you have any further questions, feel free to leave a comment below. 🙂