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."