Excel Formula Reference: Yahoo Finance

  • All Yahoo Finance Excel formulas start with EPF.Yahoo so they can be easily referenced.
  • Formulas can either be entered manually or via the shortcut buttons on the Configuration Pane.

Excel Price Feed provides formulas for most of the data available on the Yahoo Finance website. These Excel formulas are simple to use in your existing spreadsheets, for example here are the formulas for current price and beta:

Yahoo Finance Excel Formulas

Yahoo Finance provides data from many different exchanges, some are real-time (such as the NYSE and NASDAQ) whilst others are delayed. For full details of coverage refer to Exchanges and data providers on Yahoo Finance on the Yahoo Finance website.

Excel Price Feed provides current and historic market data for stocks, indices, stock options, commodities, cryptocurrencies, mutual funds and ETFs. In addition, for single stocks, there is annual reported income, cash flow and balance sheet data. There are also some formulas specifically for stock options, mutual funds and Exchange Traded Funds (ETFs).

Click on a category below to view the formulas:


Current Market Data

All of these formulas require just a stock ticker, for example: =EPF.Yahoo.Price("AAPL")

Formula Description
EPF.Yahoo.52WeekChange 52 Week Change in price (%).
EPF.Yahoo.52WeekLow 52 week low.
EPF.Yahoo.52WeekHigh 52 week high.
EPF.Yahoo.AverageDailyVolume10Day Average daily volume over past 10 trading days.
EPF.Yahoo.AverageDailyVolume3Month Average daily volume over past 3 months.
EPF.Yahoo.AveragePrice200Day Average price over the past 200 trading days.
EPF.Yahoo.AveragePrice50Day Average price over the past 50 trading days.
EPF.Yahoo.Ask Current ask price.
EPF.Yahoo.AskSize Current ask size.
EPF.Yahoo.Beta Beta.
EPF.Yahoo.Bid Current bid price.
EPF.Yahoo.BidSize Current bid size.
EPF.Yahoo.BookValue Book Value Per Share (most recent quarter).
EPF.Yahoo.DayChange Change in price today.
EPF.Yahoo.DayChangePercent Percentage change in price today.
EPF.Yahoo.DayHigh Day high.
EPF.Yahoo.DayLow Day low.
EPF.Yahoo.DayVolume Volume of shares traded today.
EPF.Yahoo.DebtToEquity Total Debt/Equity.
EPF.Yahoo.EBITDA EBITDA
(Earnings before interest, tax, depreciation and amortization).
EPF.Yahoo.EPS EPS TTM (Trailing Twelve Months).
EPF.Yahoo.EnterpriseValue Enterprise Value.
EPF.Yahoo.EnterpriseToEbitda Enterprise Value/EBITDA.
EPF.Yahoo.EnterpriseToRevenue Enterprise Value/Revenue.
EPF.Yahoo.ExDividendDate Ex Dividend Date.
EPF.Yahoo.FloatingShares Number of floating shares.
EPF.Yahoo.ForwardAnnualDividendRate Forward annual dividend rate.
EPF.Yahoo.ForwardAnnualDividendYield Forward annual dividend yield.
EPF.Yahoo.HeldInsidersPercent % of shares held by insiders.
EPF.Yahoo.HeldInstitutionsPercent % of shares held by institutions.
EPF.Yahoo.MarketCap Current market capitalisation.
EPF.Yahoo.MarketOpen Market open price.
EPF.Yahoo.NetIncomeToCommon Net Income To Common.
EPF.Yahoo.OperatingMargin Operating Margin TTM (Trailing Twelve Months).
EPF.Yahoo.PayoutRatio Payout Ratio.
EPF.Yahoo.PEGRatio Price/Earnings-to-Growth (PEG) Ratio.
EPF.Yahoo.PERatio PE Ratio TTM (Trailing Twelve Months).
EPF.Yahoo.PERatioForward PE Ratio Forward.
EPF.Yahoo.PrevClose Previous market close price.
EPF.Yahoo.Price Current mid price.
EPF.Yahoo.PriceToBookRatio Price to Book Ratio.
EPF.Yahoo.PriceToSales Price/Sales TTM (Trailing Twelve Months).
EPF.Yahoo.ProfitMargin Profit Margin.
EPF.Yahoo.ReturnOnAssets Return on Assets TTM (Trailing Twelve Months)
EPF.Yahoo.ReturnOnEquity Return On Equity.
EPF.Yahoo.RevenuePerShare Revenue Per Share TTM (Trailing Twelve Months).
EPF.Yahoo.RevenueGrowth Quarterly Revenue Growth (Year on Year).
EPF.Yahoo.SharesOutstanding Number of shares outstanding.
EPF.Yahoo.ShortPercentFloat Short shares as a % of float.
EPF.Yahoo.SharesShort Number of short shares.
EPF.Yahoo.ShortRatio Short shares ratio.
EPF.Yahoo.TrailingAnnualDividendRate Trailing annual dividend rate.
EPF.Yahoo.TrailingAnnualDividendYield Trailing annual dividend yield.


Company Data

All these formulas require just a stock ticker, for example: =EPF.Yahoo.Industry("XOM")

Formula Description
EPF.Yahoo.City City (the city where the HQ or registered office is located).
EPF.Yahoo.Country Country (the country where the HQ or registered office is located).
EPF.Yahoo.Industry Industry name.
EPF.Yahoo.Name Company name.
EPF.Yahoo.QuoteType Quote type, for example "EQUITY" or "MUTUALFUND".
EPF.Yahoo.Sector Sector name.


Analyst Price Targets

All these formulas require just a stock ticker, for example: =EPF.Yahoo.TargetMeanPrice("MSFT")

Formula Description
EPF.Yahoo.TargetHighPrice Analyst Target 1Y High Price.
EPF.Yahoo.TargetLowPrice Analyst Target 1Y Low Price.
EPF.Yahoo.TargetMeanPrice Analyst Target 1Y Mean Price.
EPF.Yahoo.TargetMedianPrice Analyst Target 1Y Median Price.


Exchange-Traded Fund (ETF) and Mutual Fund Data

All these formulas (except for FundAnnualTotalReturn which also requires a year) require just a fund ticker, for example: =EPF.Yahoo.FundExpenseRatio("SPY")

Formula Description
EPF.Yahoo.Fund1YReturn 1 Year Daily Total Return.
EPF.Yahoo.Fund3YReturn 3 Year Daily Total Return.
EPF.Yahoo.Fund3YAverageReturn 3 Year Average Return.
EPF.Yahoo.Fund5YAverageReturn 5 Year Average Return.
EPF.Yahoo.FundAnnualTotalReturn Annual Total Return for the specified year.
EPF.Yahoo.FundCategory Fund category and focus.
For example "Blend Large":
A large fund with a blend of investments.
EPF.Yahoo.FundExpenseRatio Expense Ratio.
EPF.Yahoo.FundHoldingsTurnover Holdings Turnover.
EPF.Yahoo.FundInception Fund Inception Date.
EPF.Yahoo.FundNAV Fund Net Asset Value (NAV).
EPF.Yahoo.FundTotalAssets Total Assets.
EPF.Yahoo.FundTotalNetAssets Total Net Assets.
EPF.Yahoo.FundTrailingTotalReturn1M Trailing Total Return 1 Month.
EPF.Yahoo.FundTrailingTotalReturn3M Trailing Total Return 3 Month.
EPF.Yahoo.FundTrailingTotalReturn1Y Trailing Total Return 1 Year.
EPF.Yahoo.FundTrailingTotalReturn3Y Trailing Total Return 3 Year.
EPF.Yahoo.FundTrailingTotalReturn5Y Trailing Total Return 5 Year.
EPF.Yahoo.FundTrailingTotalReturn10Y Trailing Total Return 10 Year.
EPF.Yahoo.FundTrailingTotalReturnYTD Trailing Total Return YTD.
EPF.Yahoo.FundYTDReturn YTD Daily Total Return.
EPF.Yahoo.Holdings.Bond Fund Holdings in Bonds (%).
EPF.Yahoo.Holdings.Cash Fund Holdings in Cash (%).
EPF.Yahoo.Holdings.Convertible Fund Holdings in Convertibles (%).
EPF.Yahoo.Holdings.Other Fund Holdings Other (%).
EPF.Yahoo.Holdings.Preferred Fund Holdings in Preferreds (%).
EPF.Yahoo.Holdings.Stock Fund Holdings in Stock (%).
EPF.Yahoo.MutualFundYield Mutual Fund Yield.
EPF.Yahoo.SectorWeightings.BasicMaterials Sector Weightings Basic Materials (%).
EPF.Yahoo.SectorWeightings.CommunicationServices Sector Weightings Communication Services (%).
EPF.Yahoo.SectorWeightings.ConsumerCyclical Sector Weightings Consumer Cyclical (%).
EPF.Yahoo.SectorWeightings.ConsumerDefensive Sector Weightings Consumer Defensive (%).
EPF.Yahoo.SectorWeightings.Energy Sector Weightings Energy (%).
EPF.Yahoo.SectorWeightings.FinancialServices Sector Weightings Financial Services (%).
EPF.Yahoo.SectorWeightings.Healthcare Sector Weightings Healthcare (%).
EPF.Yahoo.SectorWeightings.Industrials Sector Weightings Industrials (%).
EPF.Yahoo.SectorWeightings.RealEstate Sector Weightings Real Estate (%).
EPF.Yahoo.SectorWeightings.Technology Sector Weightings Technology (%).
EPF.Yahoo.SectorWeightings.Utilities Sector Weightings Utilities (%).
EPF.Yahoo.Yield ETF Yield.


Stock Option Data

In addition to the formulas listed below, several of the current market data formulas can be used with stok option tickers for example EPF.Yahoo.DayHigh and EPF.Yahoo.MarketOpen

All these formulas require just an options contract name, for example: =EPF.Yahoo.ExpireDate("AAPL200131C00320000")

Formula Description
EPF.Yahoo.Ask Current ask price.
EPF.Yahoo.Bid Current bid price.
EPF.Yahoo.ExpireDate The expiry date of the option.
EPF.Yahoo.Strike The strike price of the option.
EPF.Yahoo.UnderlyingTicker The underlying ticker of the option contract.


Historic Market Data

All these formulas require both a stock ticker and a historic date, for example: =EPF.Yahoo.Historic.Close("TSLA", "1 May 2019")

Formula Description
EPF.Yahoo.Historic.AdjustedClose Adjusted Close price on specified date.
EPF.Yahoo.Historic.Close Close price on specified date.
EPF.Yahoo.Historic.High High price on specified date.
EPF.Yahoo.Historic.Low Low price on specified date.
EPF.Yahoo.Historic.Open Open price on specified date.
EPF.Yahoo.Historic.Volume Volume on specified date.


Annual Reported Income, Cash Flow and Balance Sheet Data

All these formulas require both a stock ticker and reporting year, for example: =EPF.Yahoo.AnnualRevenue("GOOG",2018)

Yahoo Finance generally provides the past 4 years reported data.

Formula Description
EPF.Yahoo.AnnualAccountsPayable Annual accounts payable.
EPF.Yahoo.AnnualAccountsReceivable Annual Accounts Receivable.
EPF.Yahoo.AnnualAcquisitions Annual acquisitions / purchases of businesses.
EPF.Yahoo.AnnualAssetsTotalCash Annual assets: total cash.
EPF.Yahoo.AnnualBasicEPS Annual basic EPS.
EPF.Yahoo.AnnualBasicSharesOutstanding Annual basic shares outstanding.
EPF.Yahoo.AnnualBeginningCashPosition Annual beginning cash position.
EPF.Yahoo.AnnualCapitalExpenditures Annual Capital Expenditures.
EPF.Yahoo.AnnualChangeInCash Annual change in cash.
EPF.Yahoo.AnnualChangeInWorkingCapital Annual change in working capital.
EPF.Yahoo.AnnualCommonStockIssued Annual common stock issued.
EPF.Yahoo.AnnualCommonStockRepurchased Annual common stock repurchased.
EPF.Yahoo.AnnualCostOfRevenue Annual cost of revenue.
EPF.Yahoo.AnnualCurrentAssets Annual current assets.
EPF.Yahoo.AnnualCurrentLiabilities Annual current liabilities.
EPF.Yahoo.AnnualDeferredIncomeTax Annual deferred income tax.
EPF.Yahoo.AnnualDebtRepayment Annual debt repayment.
EPF.Yahoo.AnnualDepreciationAndAmortization Annual Depreciation & Amortization.
EPF.Yahoo.AnnualDilutedEPS Annual diluted EPS.
EPF.Yahoo.AnnualDilutedSharesOutstanding Annual diluted shares outstanding.
EPF.Yahoo.AnnualDividendsPaid Annual total dividends paid.
EPF.Yahoo.AnnualEarnings Annual earnings.
EPF.Yahoo.AnnualEBITDA Annual EBITDA.
EPF.Yahoo.AnnualEndCashPosition Annual end cash position.
EPF.Yahoo.AnnualFreeCashFlow Annual free cash flow / other working capital.
EPF.Yahoo.AnnualInterestExpense Annual interest expense.
EPF.Yahoo.AnnualInventory Annual Inventory.
EPF.Yahoo.AnnualLongTermDebt Annual Long Term Debt.
EPF.Yahoo.AnnualOperatingCashFlow Annual operating cash flow.
EPF.Yahoo.AnnualOperatingIncome Annual operating income.
EPF.Yahoo.AnnualOtherCashflowsFromInvestingActivities Annual Other Cashflows from investing activities.
EPF.Yahoo.AnnualOtherFinancingActivities Annual other financing activities.
EPF.Yahoo.AnnualOtherNonCashItems Annual other non cash items.
EPF.Yahoo.AnnualPretaxIncome Annual pre-tax income.
EPF.Yahoo.AnnualPurchasesOfInvestments Annual sales / maturities of investments.
EPF.Yahoo.AnnualResearchAndDevelopmentExpense Annual research and development expense (R&D).
EPF.Yahoo.AnnualRevenue Annual revenue.
EPF.Yahoo.AnnualSellingGeneralAndAdministrationExpense Annual selling, general and administration expense.
EPF.Yahoo.AnnualStockBasedCompensation Annual stock based compensation.
EPF.Yahoo.AnnualStockholdersEquity Annual stockholders equity.
EPF.Yahoo.AnnualTaxProvision Annual tax provision.
EPF.Yahoo.AnnualTotalAssets Annual total assets.
EPF.Yahoo.AnnualTotalCashflowsFromFinancingActivities Annual Total Cashflows from financing activities.
EPF.Yahoo.AnnualTotalCashflowsFromInvestingActivities Annual Total Cashflows from investing activities.
EPF.Yahoo.AnnualTotalCashflowsFromOperatingActivities Annual Total Cashflows from operating activities.
EPF.Yahoo.AnnualTotalOtherIncomeExpenseNet Annual total other income expense net.
EPF.Yahoo.AnnualTotalLiabilities Annual total liabilities.


Next Earnings Announcement Date

All of these formulas require just a stock ticker, for example: =EPF.Yahoo.NextEarningsDate("BMW.DE")

Formula Description
EPF.Yahoo.NextEarningsDate The next date that earnings will be announced
Note: if the exact earnings date has not been announced yet then this value will be empty.
EPF.Yahoo.NextEarningsDateEstimate The estimated date period that earnings will be announced
Note: if the exact earnings date has been announced then this value will be empty.
EPF.Yahoo.NextEarningsDateEstimateStart The estimated start date of the period that earnings will be announced
Note: if the exact earnings date has not been announced yet then this value will be empty.
EPF.Yahoo.NextEarningsDateEstimateEnd The estimated end date of the period that earnings will be announced
Note: if the exact earnings date has not been announced yet then this value will be empty.

Examples

Formula Description
=EPF.Yahoo.Price("AAPL") Populate the Excel cell with the current stock price of Apple.
=EPF.Yahoo.Volume("MSFT") Populate the Excel cell with the current daily volume of Microsoft.
=EPF.Yahoo.52WeekHigh("VFIAX") Populate the Excel cell with the 52 week high price of the Vanguard 500 Index Mutual Fund.
=EPF.Yahoo.AnnualRevenue("GOOG",2018) Populate the Excel cell with the reported Google annual revenue for 2018.
=EPF.Yahoo.Historic.Close("TSLA", "1 May 2019") Populate the Excel cell with the Tesla stock close price on 1 May 2019.
=EPF.Yahoo.Historic.Volume("GE", "5 April 2019") Populate the Excel cell with the General Electric (GE) stock volume on 5 April 2019.
=EPF.Yahoo.NextEarningsDate("BMW.DE") Populate the Excel cell with the next earnings announcement date of BMW stock.


VBA Functions

Several of the Yahoo Finance data which is available via Excel Formulas can also be accessed in VBA via a set of VBA functions. They all begin with EPF.Yahoo.VBA and can be used for market analysis, automated trading etc.

All Excel Price Feed VBA functions can be used from VBA by using the Application.Run function.

The list below is all the functions that can be called from VBA, see above for descriptions.

EPF.Yahoo.VBA.Price
EPF.Yahoo.VBA.DayChange
EPF.Yahoo.VBA.DayChangePercent
EPF.Yahoo.VBA.DayVolume
EPF.Yahoo.VBA.MarketCap
EPF.Yahoo.VBA.52WeekHigh
EPF.Yahoo.VBA.52WeekLow
EPF.Yahoo.VBA.PERatio
EPF.Yahoo.VBA.EPS
EPF.Yahoo.VBA.Currency
EPF.Yahoo.VBA.DayHigh
EPF.Yahoo.VBA.DayLow
EPF.Yahoo.VBA.MarketOpen
EPF.Yahoo.VBA.PrevClose
EPF.Yahoo.VBA.Name
EPF.Yahoo.VBA.QuoteType

In addition there is a VBA function for returning historical daily data:

EPF.Yahoo.HistoricDateRange


VBA Yahoo Finance Examples

1. Return the live price of Microsoft stock:

Dim price As Variant
price = Application.Run("EPF.Yahoo.VBA.Price", "MSFT")


2. Return the day high price of Apple stock:

Dim bidPrice As Variant
bidPrice = Application.Run("EPF.Yahoo.VBA.DayHigh", "AAPL")


3. Sample Code for Apple stock quote and 52 week high in Excel VBA:

Yahoo Finance VBA Apple (AAPL) stock quote