How to Get Live Stock Prices in Excel: Real-Time Quotes, Historical Data, and VBA Automation

🧠 Introduction

Get live stock quotes and historical data in your spreadsheets. Excel (especially Excel 365) has many tools for this. You can find live market quotes, delayed trade prices, end‑of‑day closing, or custom historical timelines. This guide will show you how, from built‑in tools to custom VBA macros.

✅ 1. Excel’s Native Stocks Data Type

Excel 365 users can turn a ticker into a linked stock record:

Enter a ticker (e.g. MSFT) in a cell.
Select the cell(s), then go to Data → Stocks to convert it into the Stocks data type—indicated by a small building icon.
Click the Add Column button or type .Price, .Previous close, Change (%), etc. to extract fields.

📊 Example:

A	B	C
MSFT	Stocks-type	.Price formula

🧮 Formula Example:

=B5.Price            ' shows realtime price
=B5.[Previous close]
=B5.[Change (%)]

⚠️ Note: Requires Excel 365 and internet connectivity; data is delayed and not for trading use.

📈 2. Using `STOCKHISTORY()` for Historical and Latest Close

For those needing the latest close price or historical trends, STOCKHISTORY is ideal:

🔹 Latest Close Example:

=STOCKHISTORY(B5, TODAY(), , 2, 0, 1)

– Returns the most recent closing price for the ticker in cell B5.

🔹 Specific Date Example:

=STOCKHISTORY(B5, $F$5, , 0, 0, 1)

– Fetches close price on the date stored in cell F5.

💡 Pro Tip: In some time zones, use TODAY()-1 to avoid #VALUE! errors due to market hours.

🌐 3. Power Query + Yahoo Finance for Live Quotes

Excel tables can auto-refresh with Power Query to pull live data from external websites:

🔧 Steps:

Go to Data → Get Data → From Web
Paste a Yahoo Finance URL (e.g., https://finance.yahoo.com/quote/MSFT)
Parse the specific price from the HTML table
Set auto-refresh on open or on demand

🧩 Tip: Reddit users confirm this method works well. It auto-refreshes—even for non‑365 users.

💻 4. VBA Macro to Grab Live Value via WebService

For classic Excel versions, use the WEBSERVICE method with VBA:

📜 VBA Code:

Sub Declaring_Variables()
    Dim TickerName As String
    TickerName = InputBox("Ticker Symbol")
    
    Dim CurrentPrice As String
    CurrentPrice = Application.WebService("http://finance.yahoo.com/d/quotes.csv?s=" & TickerName & "&f=l1")
    
    MsgBox "Text Format: " & CurrentPrice & vbCrLf & "Number Format: " & Application.NumberValue(CurrentPrice)
End Sub

This macro prompts for a ticker, fetches the CSV from Yahoo, and shows both text and number formats.

🔁 5. Advanced VBA for Automated Refreshing

Want auto-refresh every 30 seconds and scheduled formula resets? Use this enhanced VBA approach:

📜 VBA Scheduler Code:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("00:00:01"), "ResetCells"
    Application.OnTime TimeValue("15:00:01"), "AddFormulas"
    Application.OnTime Now + TimeValue("00:00:30"), "RefreshData"
End Sub

Public Sub ResetCells()
    ' resets stock‑related cells to zero at midnight
End Sub

Public Sub AddFormulas()
    ' re‑inserts .Price/.Change() formulas around 3 PM
End Sub

Public Sub RefreshData()
    ThisWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:30"), "RefreshData"
End Sub

🗣️ User Insight: “I reset at midnight, re‑formula at 3 PM, and refresh every 30 seconds to avoid skewing index funds data.”

📊 Comparison & Best Practices

Method	Requires	Live/Delayed	Historical	Automation
Stocks Data Type	Excel 365	Delayed	❌	✅
STOCKHISTORY()	Excel 365	❌	✅	✅
Power Query + Yahoo Finance	Any Excel	Near-live	Limited	✅
VBA WebService	Any Excel	Delayed	❌	✅
VBA with Timed Refresh	Any Excel	Custom	❌	✅✅

🔍 SEO Tip: Use Synonyms & Keyword-Rich Phrases

Make your content SEO-optimized by using a variety of terms:

share quotes, equity price, market ticker value
end-of-day closing figure, historical stock trend
auto-refresh cell, VBA script, macro-driven quote feed

Including synonyms in headers, code, and explanations improves discoverability.

✅ Conclusion & Recommendations

Use built-in Stocks & STOCKHISTORY if you have Excel 365—it’s simple and powerful.
Use Power Query + Yahoo for real-time data without subscription needs.
Use VBA when you require automation or need to support older Excel versions.

By combining formulas, Power Query, and VBA, you can build anything from basic stock tickers to advanced, self-refreshing dashboards—ideal for analysts, traders, or students.

💾 Full VBA Code (Copy-Paste Ready)

🔹 Simple Yahoo WebService:

Sub GetTickerPrice()
    Dim T As String, P As String
    T = InputBox("Enter ticker:")
    P = Application.WebService("http://finance.yahoo.com/d/quotes.csv?s=" & T & "&f=l1")
    MsgBox "Price is: " & Application.NumberValue(P)
End Sub

🔹 Automated Refresh & Formula Scheduler:

Private Sub Workbook_Open()
    Application.OnTime TimeValue("00:00:01"), "ResetCells"
    Application.OnTime TimeValue("15:00:01"), "AddFormulas"
    Application.OnTime Now + TimeValue("00:00:30"), "RefreshData"
End Sub

' (Include ResetCells, AddFormulas, RefreshData as above)

If you need help customizing this to your workbook or want a downloadable template, feel free to ask!