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.

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, commodities, cryptocurrencies and ETFs. In addition, for single stocks, there is annual reported income and balance sheet data. There are also some formulas specifically for Exchange Traded Funds (ETFs):


Current Market Data

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

Formula Description
EPF.Yahoo.Price Current mid price.
EPF.Yahoo.DayChange Change in price today.
EPF.Yahoo.DayChangePercent Percentage change in price today.
EPF.Yahoo.DayVolume Volume of shares traded today.
EPF.Yahoo.MarketCap Current market capitalisation.
EPF.Yahoo.Bid Current bid price.
EPF.Yahoo.Ask Current ask price.
EPF.Yahoo.BidSize Current bid size.
EPF.Yahoo.AskSize Current ask size.
EPF.Yahoo.TrailingAnnualDividendYield Trailing annual dividend yield
EPF.Yahoo.TrailingAnnualDividendRate Trailing annual dividend rate
EPF.Yahoo.EnterpriseValue Enterprise Value
EPF.Yahoo.Beta Beta
EPF.Yahoo.SharesOutstanding Number of shares outstanding
EPF.Yahoo.FloatingShares Number of floating shares
EPF.Yahoo.SharesShort Number of short shares
EPF.Yahoo.ShortRatio Short shares ratio
EPF.Yahoo.ShortPercentFloat Short shares as a % of float
EPF.Yahoo.HeldInsidersPercent % of shares held by insiders
EPF.Yahoo.HeldInstitutionsPercent % of shares held by institutions
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.52WeekHigh 52 week high.
EPF.Yahoo.52WeekLow 52 week low.
EPF.Yahoo.PERatio PE Ratio TTM (Trailing Twelve Months)
EPF.Yahoo.PERatioForward PE Ratio Forward
EPF.Yahoo.EPS EPS TTM (Trailing Twelve Months)
EPF.Yahoo.DayHigh Day high.
EPF.Yahoo.DayLow Day low.
EPF.Yahoo.MarketOpen Market open price.
EPF.Yahoo.PrevClose Previous market close price.


Company Data

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

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


Exchange-Traded Fund (ETF) 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.FundExpenseRatio Expense Ratio.
EPF.Yahoo.FundCategory Fund category and focus.
For example "Blend Large":
A large fund with a blend of investments.
EPF.Yahoo.FundHoldingsTurnover Holdings Turnover.
EPF.Yahoo.FundTotalNetAssets Total Net Assets.
EPF.Yahoo.FundTotalAssets Total Assets.
EPF.Yahoo.Yield Fund Yield.
EPF.Yahoo.FundNAV Fund Net Asset Value (NAV).
EPF.Yahoo.FundInception Fund Inception Date.
EPF.Yahoo.FundYTDReturn YTD Daily Total Return.
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.FundTrailingTotalReturnYTD Trailing Total Return YTD.
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.


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


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

Many 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