Imagine having the power to bring in your data tables with just a blink of an eye. And the icing on the cake? You can effortlessly fetch real-time stock or option data from the web, process it to your heart’s content, all without the need for repetitive manual processing—it’s automatic!
I was once in your shoes, curious about the stock market with zero knowledge on how to extract web data into my spreadsheets. The learning curve seemed steep, but my curiosity and the allure of real-time data made the journey exciting.
At first, I relied on Power Query, but it occasionally struggled with larger datasets, making me seek a faster and more efficient solution. That’s when I stumbled upon a method that transformed my Excel experience. Today, I’m thrilled to share this method with you, so you can replicate my journey and create your own data-driven Excel dashboard.
Don’t worry if you are Struggling to afford the latest version of Microsoft Excel? Well, I’ve found a solution! You can now get a genuine copy at an incredibly low cost From here , Believe they are simple/ authentic and almost free & you don’t have to scroll through countless pages.Collapse
Lets Come back to the topic –The dashboard you see below started as a small dream but grew into a powerful tool that allows me to sense the market sentiment in real time. The best part? You can also make this for free—all you need is Microsoft Excel on your computer. Take a peek at what’s possible, and you’ll wonder why you haven’t tried it sooner. Dive in, and let’s unlock the magic of real-time data together!
NiftySensex Dashboard I had created 🙂
Before we delve into the code, it’s essential to have a basic understanding of VBA (Visual Basic for Applications) and how to apply code within Excel. If you’re new to this, don’t worry—I’ll provide a brief tutorial shortly. This is my first blog, so your appreciation and feedback mean a lot. Let’s explore the exciting world of Excel macros together!”
For entering macro steps in Excel, here’s a concise version:
How to Enter a Macro in Excel
- Open Excel: Launch Microsoft Excel on your computer.
- Enable Developer Tab: If you don’t see the “Developer” tab in the Excel ribbon, enable it in Excel Options.
- Enter Macros: Enter it in Code window.
- Access Macro: To run your macro, go to the “Developer” tab, click “Macros,” select your macro, and click “Run.”
That’s it! You’ve entered and executed a macro in Excel.
“In the example below, I’ve created a Sheet1 and entered the URL for the India Stock list in cell A1. Now, it continuously fetches tables from the specified website. Copy and paste this code into your Excel. You can also manually enter the URL in the code, but it’s more convenient to have it in a cell. This way, you can easily update the URL as needed because requirements tend to change over time.”
—-Code Starts from Here ——
Option Explicit
Dim htm As Object
Dim Tr As Object
Dim Td As Object
Dim Tab1 As Object
Dim url As String
Dim Colstart As Long
Dim HTML As Variant
Dim i As Long
Dim j As Long
Dim n As Long
Dim RunTime
Private Sub FETCH()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
url = VBA.Trim(Sheets(“Sheet1”).Cells(1, 1)) ‘String
Set HTML = CreateObject(“htmlfile”) ‘Create HTMLFile Object
With CreateObject(“msxml2.xmlhttp”) ‘Get the WebPage Content
.Open “GET”, url, False
.send
HTML.body.innerHTML = .responseText
End With
Colstart = 2
j = 2
i = Colstart
n = 0
‘Loop Through website tables
For Each Tab1 In HTML.getElementsByTagName(“table”)
With HTML.getElementsByTagName(“table”)(n)
For Each Tr In .Rows
For Each Td In Tr.Cells
Sheet8.Cells(j, i) = Td.innerText
i = i + 1
Next Td
i = Colstart
j = j + 1
Next Tr
End With
n = n + 1
i = Colstart
j = j + 1
Next Tab1
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub
—-Code Ends Here ——
-So, this was the simplest way to fetch live data lightning fast from the web. But, hold on, the project isn’t over yet. There are many more beautiful things waiting for you to discover, and together we’ll continue learning so that you can create these beautiful dashboards for your own analysis.
Additionally, I’ve always wished that I could do the same for NYSE stocks. Well, I’ve already started working on that, and I’m excited to share my progress with many users who want to master advanced Excel. But before that don’t forget to have your copy of Microsoft Excel today , if its expensive then again i would like to recommend to download From here ,and continue exploring the vast world of Microsoft Excel and the powerful products they’ve created. This way, people like us can unleash our creativity and accomplish amazing things.
Taking a break here , so don’t forget to show your appreciation because this is my first blog:) , and I’ll continue blogging as long as even one person finds it valuable. Furthermore, I’ll be sharing insights on how we can create Excel tools or even more advanced projects. It all hinges on your creativity and your willingness to learn and explore.