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. 🙂