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()
Dim xCell As Range
For Each xCell In ActiveSheet.Columns(1).Cells
If Len(xCell) = 0 Then
Exit For
End If
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 🙂

By Victor Leung

Experience in software development, consulting services and technical product management. Understanding of business and technology with an MBA in Finance and a Master degree in Computer Science. AWS Certified Solution Architect with experience in building products from scratch and serving as a charismatic leader.

Leave a comment

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: