STOCK_PRICE
Overview
Retrieves the latest daily closing price for a given stock symbol using the Alpha Vantage API. This function is useful for obtaining up-to-date stock prices for use in financial models, dashboards, or Excel-based analysis. The function queries the Alpha Vantage API, which provides free stock and financial data. For more details, see the Alpha Vantage documentation and support page .
This example function is provided as-is without any representation of accuracy.
Usage
Use this function in Excel as follows:
=STOCK_PRICE(symbol, api_key)symbol(string, required): The stock ticker symbol (e.g., “AAPL” for Apple Inc.).api_key(string, required): Your Alpha Vantage API key.
The function returns the latest daily closing price for the specified stock symbol as a float. If the symbol is invalid or data is unavailable, an error message string is returned.
Examples
Example 1: Valid Stock Symbol
Excel formula:
=STOCK_PRICE("IBM", "demo")Expected output:
| Price |
|---|
| 290.49 |
Example 2: Invalid Stock Symbol
Excel formula:
=STOCK_PRICE("INVALID", "demo")Expected output:
| Error |
|---|
| Error: Data unavailable. |
Python Code
import requests
def stock_price(symbol, api_key):
"""
Retrieves the latest daily closing price for a given stock symbol using the Alpha Vantage API.
Args:
symbol: The stock ticker symbol (e.g., "AAPL").
api_key: Your Alpha Vantage API key.
Returns:
The latest daily closing price as a float, or an error message string if unavailable.
"""
url = f"https://www.alphavantage.co/query"
params = {
"function": "TIME_SERIES_DAILY_ADJUSTED",
"symbol": symbol,
"apikey": api_key
}
response = requests.get(url, params=params, timeout=10)
if response.status_code != 200:
return f"Error: API request failed with status {response.status_code}."
data = response.json()
if "Error Message" in data:
return "Error: Invalid symbol."
if "Time Series (Daily)" not in data:
return "Error: Data unavailable."
try:
latest_date = sorted(data["Time Series (Daily)"].keys())[-1]
close_price = float(data["Time Series (Daily)"][latest_date]["4. close"])
return close_price
except Exception:
return "Error: Could not parse price data."