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
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 :)
Experience in software development, application architecture, and deploying cloud solutions for enterprise customers. Strong hands-on skills with a Master's degree in Computer Science and business acumen with a master of business administration (MBA) in Finance. Certified in Amazon Web Services (AWS), Google Cloud Platform (GCP), Microsoft Azure, Kubernetes (CKA, CKAD, CKS, KCNA) and Scrum (PSM, PSPO) with experience in building banking products from scratch.