Is there a way to get a stock quote into my EXCEL 2024 spreadsheet? I do not have 365.

Question

I do not have Microsoft 365. I have EXCEL 2024, so the function STOCKHISTORY doesn't work for me. I want to get a stock quote into my spreadsheet. Ideally, It would keep updating.

Answer 1

Thanks for sharing the details, David! Try a VBA code. With VBA, you can fetch live stock quotes directly into Excel 2024, even without Microsoft 365. This is actually one of the most flexible ways because you can control which stocks, how often they update, and how the data is displayed.

Here’s a clean approach:

VBA Method Using a Web API

Use Alpha Vantage (free API) as an example.

Go to Alpha Vantage, get a free API key (instant).

In Excel, open the VBA editor, press Alt + F11 in Excel. Click insert then module and paste this code:

Function GetStockPrice(symbol As String) As Double

Dim http As Object

Dim JSON As String

Dim price As Double

Dim apiKey As String

apiKey = "YOUR_API_KEY" ' <-- Replace with your Alpha Vantage API key

Set http = CreateObject("MSXML2.XMLHTTP")

http.Open "GET", "https://www.alphavantage.co/query?function=GLOBAL_QUOTE&symbol=" & symbol & "&apikey=" & apiKey, False

http.Send

JSON = http.responseText

' Parse the price from JSON

Dim startPos As Long, endPos As Long

startPos = InStr(JSON, """05. price"": """) + Len("""05. price"": """)

endPos = InStr(startPos, JSON, """")

If startPos > 0 And endPos > 0 Then

price = CDbl(Mid(JSON, startPos, endPos - startPos))

GetStockPrice = price

Else

GetStockPrice = CVErr(xlErrNA)

End If

End Function

Now you can use this as a function in Excel, in any cell type =GetStockPrice("MSFT")

Hit enter, the latest stock price appears.

To refresh every few minutes, add this code into the same module:

Sub RefreshStock()

ThisWorkbook.Sheets("Sheet1").Calculate

Application.OnTime Now + TimeValue("00:05:00"), "RefreshStock" ' every 5 minutes

End Sub

Run RefreshStock from the Macro button once to start automatic updates.

Note that you can replace "MSFT" with any valid stock ticker.

You can pull multiple tickers by writing formulas in multiple cells.

See if this helps. If you need further assistance just let me know.

Best regards,

Kimberly

Answer 2

In Excel 2024, if you do not have Microsoft 365, you won't have access to the STOCKHISTORY function or the advanced data types for stocks that are available in Excel for the web and Microsoft 365. However, you can still manually enter stock quotes or use other methods to get stock data into your spreadsheet.

One option is to use a web service or an API to fetch stock quotes. You can create a custom function that retrieves stock data from an external source using Excel's capabilities to make web requests. This would require some programming knowledge, as you would need to write a script that interacts with the API to get the stock quotes and update them in your spreadsheet.

Alternatively, you can periodically copy and paste stock quotes from a financial website into your Excel spreadsheet if you do not need real-time updates.

For automatic updates, consider using Excel's custom functions feature, which allows you to create functions that can fetch data from the web, but keep in mind that this feature may have limitations based on your version of Excel.

If you are looking for a more straightforward solution, you might want to consider upgrading to Microsoft 365, which offers more integrated features for fetching and updating stock data directly in Excel.