Published on 29 October 2018 Tweet
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:
Dim xCell As Range
For Each xCell In ActiveSheet.Columns(1).Cells
If Len(xCell) = 0 Then
Application.OnTime DateAdd(“s”, 1, Now), “Refresh_data”
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 :)