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 🙂

victorleungtw

Years of experience delivering enterprise projects with global stakeholders in banking, airline and gaming industries involving a broad range of technologies. Provide consulting services at both executive and operational levels, including strategy and data analysis. Lead web and mobile app development team with hands-on coding implementation. Manage projects as Certified Scrum Master with Agile techniques. Qualification with master degree in Computer Science and pursuing MBA degree in Finance.

Leave a Reply

Close Menu