Get company financial data in Excel

Understandably, many people prefer to monitor stock market/portfolio activity using Excel because its extremely flexible. But how do you import company financial data into Excel? Cutting and pasting is simply not viable for serious work where many quantities are involved. You need access to financial data streams. Before subscribing to a financial data provider such as Barchart, QuoteMedia (via MarketXLS), QuickFS, Quandl, Alpha Vantage, IEX (via Stock Connector) or Intrinio, ask yourself if free data, such as that from Yahoo Finance would work for you. After all, Yahoo data is global in scope and quotes are real-time for NASDAQ, NYSE, Canada, India and many other international exchanges.

To get company financial data into Excel from Yahoo Finance, one way is to use Randy Harmelink's SMF add-in which itself is free and well worth checking out. Another is XLQ from QMatix. An alternative is JeX Financial, a ready-built system you simply edit to your own needs. On the surface, Jex Financial is a workbook of 23 example worksheets which you can easily replicate and customize. But look deeper and you will see a really simple way to call down the exact data you need for watch-lists, portfolios, analysis sheets, charts, in fact any financial worksheet you wish to create. No VBA knowledge is required, and its not an add-in so there is no installation. Under the wraps is JeX, our proprietary JSON extraction engine which can tap into any on-line JSON data feed using worksheet-based (versus VBA) schema definitions. You have full access to the engine should you wish to exploit other JSON feeds that are useful to you.

No monthly subscriptions

You get instant access to over 800 types of financial information on 80 exchanges and over 200,000 symbols--stocks, options, funds, ETFs, currencies, cryptocurrencies, commodities and indexes. Additionally, we include a https://newsapi.org newsreader to keep you apprised of developing news and headlines for any symbol, country or news source. So, you can do most of your financial research, cost effectively, from one Excel document.

Watch the video to understand more...

Leonard G.

P.S. The program is great and I love it.

Steve C.

Works great!

Ed H. Texas

First off, thanks for building the spreadsheet, it’s amazing...

The Jex function extracts the field of interest from the JSON stream. All the field names listed can be used in the JeX call. For example a cell would contain

=Jex("Bid", "MSFT")

to return the bid price of Microsoft. A few fields require more than two arguments, for example options require the option symbol and the expiration date. Simply refer to the example in the spreadsheet (all fields have examples) or the Key sheet to see how the JSON extraction query is structured.

Blank Fields

Some fields will return blanks depending on the type of query or the time of day. For example, stocks will not return a MorningStar rating. Mutual funds will not return a bid size. Stocks will not return a pre-market price in the post-market, etc.

Arrays

Some fields return arrays and will return the first element by default. To see more elements you can select the cells (row or column) and enter the formula with CNTRL-SHIFT-ENTER . Or you can select a particular element using the last parameter, e.g.

=Jex("Dividends Paid", "IBM", 3)

will give the third element of the array, IBMs dividends 3 years ago from the cash flow statement.

Click to view the list of fields

SUMMARY DETAILS
Summary Previous Close
Summary Open
Summary Day Low
Summary Day High
Regular Market Previous Close
Regular Market Open
Regular Market Day Low
Regular Market Day High
Dividend Rate
Dividend Yield
ex Dividend Date
Payout Ratio
Five Year Avg Dividend Yield
Beta
Trailing P E
Forward P E
Volume
Regular Market Volume
Average Volume 10 Days
Average Daily Volume 10 Day
Bid
Ask
Bid Size
Ask Size
Market Cap
Yield
YTD Return
Total Assets
Strike Price
Open Interest
52 Week Low
52 Week High
Price To Sales Trailing 12 Months
Fifty Day Average
Two Hundred Day Average
Trailing Annual Dividend Rate
Trailing Annual Dividend Yield
NAV Price
Currency
From Currency
Last Market
Volume 24 Hours
Volume All Currencies
Circulating Supply
Algorithm
Max Supply
Start Date
Tradeable

ASSET PROFILE
Address1
City
State
Zip
Phone
Website
Industry
Industry Symbol
Sector
Long Business Summary
Full Time Employees
Audit Risk
Compensation Risk
Share Holder Rights Risk
Governance Epoch Date
Overall Risk
CEO
CEO Age
CEO Title
CEO Year Born
CEO Fiscal Year
CEO Total Pay
CEO Exercised Value
CEO Unexercised Value
Officer Name
Officer Age
Officer Title
Officer Year Born
Officer Total Pay
Officer Exercised Value
Officer Unexercised Value

FINANCIAL DATA
FD Current Price
Target High Price
Target Low Price
Target Mean Price
Target Median Price
Recommendation Mean
Number of Analyst Opinions
Total Cash
Total Cash Per Share
EBITDA
Total Debt
Quick Ratio
Current Ratio
Total Revenue
Debt To Equity
Revenue Per Share
Return On Assets
Return On Equity
Gross Profits
Free Cashflow
Operating Cashflow
Earnings Growth
Revenue Growth
Gross Margins
EBITDA Margins
Operating Margins
Profit Margins
Recommendation Key

DEFAULT KEY STATISTICS
Price Hint
Enterprise Value
Forward PE
Profit Margins 2
Float Shares
Shares Outstanding
Shares Short
Shares Short Prior Month
Shares Short Previous Month Date
Date Short Interest
Shares Percent Shares Out
Held Percent Insiders
Held Percent Institutions
Short Ratio
Short Percent Of Float
Beta 2
Morning Star Overall Rating
Morning Star Risk Rating
Category
Book Value
Price To Book
Annual Report Expense Ratio
Ytd Return
Beta 3 Year
Total Assets 2
Yield 2
Fund Family
Fund Inception Date
Legal Type
Three Year Average Return
Five Year Average Return
Price To Sales Trailing 1 2 Months
Last Fiscal Year End
Next Fiscal Year End
Most Recent Quarter
Earnings Quarterly Growth
Revenue Quarterly Growth
Net Income To Common
Trailing Eps
Forward Eps
Peg Ratio
Last Split Factor
Last Split Date
Enterprise To Revenue
Enterprise To Ebitda
52 Week Change
S&P 52 Week Change
Last Dividend Value
Last Cap Gain
Annual Holdings Turnover

CALENDAR EVENTS
Earnings Date 1
Earnings Date 2
Earnings Average
Earnings Low
Earnings High
Revenue Average
Revenue Low
Revenue High
Ex Dividend Date
Dividend Date

INCOME STATEMENT HISTORY
Income Statement End Date
E Total Revenue
Cost Of Revenue
Gross Profit
Research Development
Selling General Administrative
Non Recurring
Other Operating Expenses
Total Operating Expenses
Operating Income
Total Other Income Expense Net
Ebit
Interest Expense
Income Before Tax
Income Tax Expense
Minority Interest
Net Income From Continuing Ops
Discontinued Operations
Extraordinary Items
Effect Of Accounting Charges
Other Items
Net Income
Net Income Applicable To Common Shares

CASH FLOW HISTORY
Cash Flow End Date
Net Income 2
Depreciation
Change To Netincome
Change To Account Receivables
Change To Liabilities
Change To Inventory
Change To Operating Activities
Total Cash From Operating Activities
Capital Expenditures
Investments
Other Cashflows From Investing Activities
Total Cashflows From Investing Activities
Dividends Paid
Net Borrowings
Other Cashflows From Financing Activities
Total Cash From Financing Activities
Effect Of Exchange Rate
Change In Cash
Repurchase Of Stock
Issuance Of Stock

BALANCE SHEET HISTORY
Balance Sheet End Date
Cash
Short Term Investments
Net Receivables
Inventory
Other Current Assets
Total Current Assets
Long Term Investments
Property Plant Equipment
Good Will
Intangible Assets
Other Assets
Deferred Long Term Asset Charges
Balance Sheet Total Assets
Accounts Payable
Short Long Term Debt
Other Current Liab
Long Term Debt
Other Liab
Balance Sheet Minority Interest
Total Current Liabilities
Total Liab
Common Stock
Retained Earnings
Treasury Stock
Capital Surplus
Other Stockholder Equity
Total Stockholder Equity
Net Tangible Assets

HISTORICAL STOCK PRICES
HDate
HOpen
HHigh
HLow
HClose
HVolume
HAdjClose
RHDate
RHOpen
RHHigh
RHLow
RHClose
RHVolume
RHAdjClose

HISTORICAL STOCK PRICE METADATA
Currency2
Symbol
Exchange Name
Instrument Type
First Trade Date
Gmtoffset
Timezone
Exchange Timezone Name
Chart Previous Close
hPrevious Close
Valid Ranges
Pre
Regular
Post
Trading Periods

FUND PROFILE
Family
styleBoxUrl
Category Name
Legal Type 2
Manager Name
Manager Bio
Manager Start Date
Annual Report Expense Ratio2
Front End Sales Load
Deferred Sales Load
Twelve B One
Net Expense Ratio
Gross Expense Ratio
Annual Holdings Turnover2
Total Net Assets
Projection Values 5y
Projection Values 3y
Projection Values 10y
Annual Report Expense Ratio Cat
Front End Sales Load Cat
Deferred Sales Load Cat
Twelve B One Cat
Annual Holdings Turnover Cat
Total Net Assets Cat
Projection Values 5y Cat
Projection Values 3y Cat
Projection Values 10y Cat
Initial Investment
Initial IRA Investment
Initial AIP Investment
Subsequent Investment
Subsequent IRA Investment
Subsequent AIP Investment
Brokerages

FUND PERFORMANCE
As Of Date
Morningstar Return Rating
YTD Return Percent
Five Yr Avg Return Pct
Num Years Up
Num Years Down
Best One Yr Total Return
Worst One Yr Total Return
Best Three Yr Total Return
Worst Three Yr Total Return
One Year Total Return
Three Year Total Return
As Of Date Cat
Morning Star Return Rating Cat
Ytd Return Pct Cat
Five Yr Avg Return Pct Cat
Num Years Up Cat
Num Years Down Cat
Best One Yr Total Return Cat
Worst One Yr Total Return Cat
Best Three Yr Total Return Cat
Worst Three Yr Total Return Cat
Load Adj Ret 1 Yr
Load Adj Ret 3 Yr
Load Adj Ret 5 Yr
Load Adj Ret 10 Yr
Trailing Returns Ytd
Trailing Returns One Month
Trailing Returns Three Month
Trailing Returns One Year
Trailing Returns Three Year
Trailing Returns Five Year
Trailing Returns Ten Year
Trailing Returns Last Bull Mkt
Trailing Returns Last Bear Mkt
Trailing Returns Nav Ytd
Trailing Returns Nav One Month
Trailing Returns Nav Three Month
Trailing Returns Nav One Year
Trailing Returns Nav Three Year
Trailing Returns Nav Five Year
Trailing Returns Nav Ten Year
Trailing Returns Cat Ytd
Trailing Returns Cat One Month
Trailing Returns Cat Three Month
Trailing Returns Cat One Year
Trailing Returns Cat Three Year
Trailing Returns Cat Five Year
Trailing Returns Cat Ten Year
Trailing Returns Cat Last Bull Mkt
Trailing Returns Cat Last Bear Mkt
Total Return Year
Total Return Annual Value
Total Return Year Cat
Total Return Annual Value Cat
Returns Q1
Returns Q2
Returns Q3
Returns Q4
Quarterly Returns Year
Rank In Category YTD
Rank In Category 1 Month
Rank In Category 3 Month
Rank In Category 1 Year
Rank In Category 3 Year
Rank In Category 5 Year
Risk Statistics Years
Alpha
Beta2
Mean Ann Ret
R Squared
Std Dev
Sharpe Ratio
Treynor Ratio
Cat Risk Statistics Years
Cat Alpha
Cat Beta2
Cat Mean Ann Ret
Cat R Squared
Cat Std Dev
Cat Sharpe Ratio
Cat Treynor Ratio

PRICE
Pre Mkt Change %
Pre Mkt Change
Pre Mkt Time
Pre Mkt Price
Post Mkt Change %
Post Mkt Change
Post Mkt Price
Post Mkt Time
Regular Mkt Change %
Change %
Regular Mkt Change
Day Change
Regular Mkt Time
Price Hint2
Last Price
Reg Mkt Price
Reg Mkt Day High
Day High
Reg Mkt Day Low
Day Low
Day Volume
Reg Mkt Volume
Average Daily Volume 10 Day2
Average Daily Volume 3 Month
Regular Mkt Previous Close2
Regular Mkt Source
Regular Mkt Open2
Strike Price2
Open Interest2
Exchange2
Exchange Name2
Exchange Data Delayed By
Mkt State
Quote Type
Symbol2
Underlying Symbol
Short Name
Long Name
Name
Currency3
Quote Source Name
Currency Symbol
From Currency2
Last Mkt2
Volume 24 Hr
Volume All Currencies2
Circulating Supply2
Mkt Cap

QUARTERLY INCOME STATEMENT
Q End Date
Q Total Revenue
Q Cost Of Revenue
Q Gross Profit
Q Research Development
Q Selling General Administrative
Q Non Recurring
Q Other Operating Expenses
Q Total Operating Expenses
Q Operating Income
Q Total Other Income Expense Net
Q Ebit
Q Interest Expense
Q Income Before Tax
Q Income Tax Expense
Q Minority Interest
Q Net Income From Continuing Ops
Q Discontinued Operations
Q Extraordinary Items
Q Effect Of Accounting Charges
Q Other Items
Q Net Income
Q Net Income Applicable To Common Shares

QUARTERLY BALANCE SHEET
Q BS End Date
Q Cash
Q Short Term Investments
Q Net Receivables
Q Inventory
Q Other Current Assets
Q Total Current Assets
Q Long Term Investments
Q Property Plant Equipment
Q Good Will
Q Intangible Assets
Q Other Assets
Q Deferred Long Term Asset Charges
Q Total Assets
Q Accounts Payable
Q Short Long Term Debt
Q Other Current Liab
Q Long Term Debt
Q Other Liab
Q BS Minority Interest
Q Total Current Liabilities
Q Total Liab
Q Common Stock
Q Retained Earnings
Q Treasury Stock
Q Capital Surplus
Q Other Stockholder Equity
Q Total Stockholder Equity
Q Net Tangible Assets

QUARTERLY CASH FLOW
Q CF End Date
Q CF Net Income
Q Depreciation
Q Change To Netincome
Q Change To Account Receivables
Q Change To Liabilities
Q Change To Inventory
Q Change To Operating Activities
Q Total Cash From Operating Activities
Q Capital Expenditures
Q Investments
Q Other Cashflows From Investing Activities
Q Total Cashflows From Investing Activities
Q Dividends Paid
Q Net Borrowings
Q Other Cashflows From Financing Activities
Q Total Cash From Financing Activities
Q Effect Of Exchange Rate
Q Change In Cash
Q Repurchase Of Stock
Q Issuance Of Stock

EARNINGS INFORMATION
Quarterly Earnings Dates
Quarterly EPS Actuals
Quarterly EPS Estimates
Curr Q EPS Est
Curr Q Earnings Est Quarter
Curr Q Earnings Est Year
Earnings Dates
Yearly Financials Dates
Yearly Revenues
Yearly Earnings
Quarterly Financials Dates
Quarterly Revenues
Quarterly Earnings
Financial Currency

EARNINGS TREND
Trend Period
Trend End Date
Growth Trend
Average EPS Est
Low EPS Est
High EPS Est
Year Ago EPS
No of EPS Analysts
Earnings Growth Trend
Average Revenue Est
Low Revenue Est
High Revenue Est
No of Revenue Analysts
Year Ago Revenue
Revenue Growth Trend
EPS Trend Current
EPS Trend 7 Days Ago
EPS Trend 30 Days Ago
EPS Trend 60 Days Ago
EPS Trend 90 Days Ago
EPS Revisions Up Last 7 days
EPS Revisions Down Last 7 days
EPS Revisions Up Last 30 days
EPS Revisions Down Last 30 days
EPS Revisions Up Last 90 days
EPS Revisions Down Last 90 days

FUND OWNERSHIP
Fund Ownership Report Date
Fund Ownership Organization
Fund Ownership % Held
Fund Ownership Position
Fund Ownership Value

INSIDER HOLDINGS
Insider Name
Insider Relation
Insider URL
Insider Transaction Description
Insider Latest Transaction Date
Insider Position Direct
Insider Position Direct Date

INSIDER TRANSACTIONS
Insider Transaction Shares
Insider Transaction Share Value
Insider Transaction Start Date
Filer URL
Insider Transaction Description2
Insider Transaction Name
Insider Transaction Relation
Insider Transaction Money Text
Insider Transaction Ownership

INSTITUTION OWNERSHIP
Institution Owner Date
Institution Owner % Held
Institution Owner Position
Institution Owner Value
Institution Owner Organization

MAJOR HOLDERS BREAKDOWN
Insiders % Held
Institutions % Held
Institutions Float % Held
Institutions Count

NET SHARE PURCHASE ACTIVITY
Period
Buy Info Count
Buy Info Shares
Buy % Insider Shares
Sell Info Count
Sell Info Shares
Sell % Insider Shares
Net Info Count
Net Info Shares
Net % Insider Shares
Total Insider Shares

QUOTE TYPE
Exchange
Quote Type2
Symbol3
Underlying Symbol2
Short Name2
Long Name2
First Trade Date Epoch UTC
Time Zone Full Name
Time Zone Short Name
uuid
GMT Offset Milliseconds

RECOMMENDATION TREND
Rec Period
Rec Strong Buy
Rec Buy
Rec Hold
Rec Sell
Rec Strong Sell

SEC FILINGS
SEC Filing Date
SEC FIling EpochDate
SEC Filing Type
SEC Filing Title
SEC Filing URL
SEC Filing Edgar URL

UPGRADE DOWNGRADE HISTORY
Grade Date
Firm
To Grade
From Grade
Action

TOP HOLDINGS
Cash Position
Stock Position
Bond Position
Other Position
Preferred Position
Convertible Position
Holding Symbol
Holding Name
Holding %
Equity Holdings
Equity HoldingsPE
Equity Holdings Price To Book
Equity Holdings Price To Sales
Equity Holdings Price To Cashflow
Equity Holdings Market Cap
Equity Holdings Three Year Earnings Growth
Equity Holdings PE Cat
Equity Holdings Price To Book Cat
Equity Holdings Price To Sales Cat
Equity Holdings Price To Cashflow Cat
Equity Holdings Market Cap Cat
Equity Holdings Three Year Earnings Growth Cat
Bond Holdings Maturity
Bond Holdings Duration
Bond Holdings Credit Quality
Bond Holdings Maturity Cat
Bond Holdings Duration Cat
Bond Holdings Credit Quality Cat
Bond Ratings BB
Bond Ratings AA
Bond Ratings AAA
Bond Ratings A
Bond Ratings Other
Bond Ratings B
Bond Ratings BBB
Bond Ratings Below B
Bond Ratings US Government
Sector Weightings Real Estate
Sector Weightings Consumer Cyclical
Sector Weightings Basic Materials
Sector Weightings Consumer Defensive
Sector Weightings Technology
Sector Weightings Communication Services
Sector Weightings Financial Services
Sector Weightings Utilities
Sector Weightings Industrials
Sector Weightings Energy
Sector Weightings Healthcare

OPTIONS BY EXPIRATION
Options Underlying Symbol
Expiration Date
Has Mini Options
Call Contract Symbol
Call Strike
Call Currency
Call Last Price
Call Change
Call Percent Change
Call Volume
Call Open Interest
Call Bid
Call Ask
Call Contract Size
Call Expiration
Call Last Trade Date
Call Implied Volatility
Call In the Money
Put Contract Symbol
Put Strike
Put Currency
Put Last Price
Put Change
Put Percent Change
Put Volume
Put Open Interest
Put Bid
Put Ask
Put Contract Size
Put Expiration
Put Last Trade Date
Put Implied Volatility
Put In the Money
Options Expiration Dates

OPTIONS METADATA
Options Meta Language
Options Meta Region
Options Meta Quote Type
Options Meta Quote Source Name
Options Meta Currency
Options Meta Esg Populated
Options Meta Tradeable
Options Meta Regular Market Price
Options Meta Regular Market Time
Options Meta Regular Market Change
Options Meta Regular Market Open
Options Meta Regular Market Day High
Options Meta Regular Market Day Low
Options Meta Regular Market Volume
Options Meta Market State
Options Meta Trailing P E
Options Meta Shares Outstanding
Options Meta Book Value
Options Meta Fifty Day Average
Options Meta Fifty Day Average Change
Options Meta Fifty Day Average Change %
Options Meta 200 Day Average
Options Meta 200 Day Average Change
Options Meta 200 Day Average Change %
Options Meta Market Cap
Options Meta Forward P E
Options Meta Price To Book
Options Meta Source Interval
Options Meta Exchange Timezone Name
Options Meta Exchange Timezone Short Name
Options Meta Gmt Off Set Milliseconds
Options Meta Trailing Annual Dividend Yield
Options Meta Price Hint
Options Meta Post Market Change %
Options Meta Post Market Time
Options Meta Post Market Price
Options Meta Post Market Change
Options Meta Regular Market Change %
Options Meta Regular Market Day Range
Options Meta Regular Market Previous Close
Options Meta Bid
Options Meta Ask
Options Meta Bid Size
Options Meta Ask Size
Options Meta Message Board Id
Options Meta Full Exchange Name
Options Meta Long Name
Options Meta Financial Currency
Options Meta Average Daily Volume 3 Month
Options Meta Average Daily Volume 1 0 Day
Options Meta 52 Week Low Change
Options Meta 52 Week Low Change %
Options Meta 52 Week Range
Options Meta 52 Week High Change
Options Meta 52 Week High Change %
Options Meta 52 Week Low
Options Meta 52 Week High
Options Meta Dividend Date
Options Meta Earnings Timestamp
Options Meta Earnings Timestamp Start
Options Meta Earnings Timestamp End
Options Meta Trailing Annual Dividend Rate
Options Meta Market
Options Meta Exchange
Options Meta Eps Trailing Twelve Months
Options Meta Eps Forward
Options Meta Exchange Data Delayed By
Options Meta Short Name
Options Meta Symbol

OPTIONS QUOTE
Option Language
Option Region
Option Quote Type
Option Currency
Option Source Interval
Option Exchange Timezone Name
Option Timezone Short Name
Option GMT Offset Milliseconds
Option ESG Populated
Option Tradeable
Option Ask
Option Full Exchange Name
Option 52 Week Low Change
Option 52 Week Low Change %
Option 52 Week Range
Option 52 Week High Change
Option 52 Week High Change %
Option 52 Week Low
Option 52 Week High
Option Strike
Option Underlying Symbol
Option Open Interest
Option Mkt State
Option Regular Mkt Change
Option Regular Mkt Open
Option Regular Mkt Day High
Option Regular Mkt Day Low
Option Regular Mkt Volume
Option Exchange
Option Short Name
Option Price Hint
Option Regular Mkt Change %
Option Regular Mkt Day Range
Option Regular Mkt Previous Close
Option Bid
Option Expire Date
Option Exchange Data Delayed By
Option Mkt
Option Regular Mkt Price
Option Regular Mkt Time
Option Symbol

OPTION STRADDLE
Straddle Strike
Straddle Call Contract Symbol
Straddle Call Strike
Straddle Call Currency
Straddle Call Last Price
Straddle Call Change
Straddle Call % Change
Straddle Call Volume
Straddle Call Open Interest
Straddle Call Bid
Straddle Call Ask
Straddle Call Contract Size
Straddle Call Expiration
Straddle Call Last Trade Date
Straddle Call Implied Volatility
Straddle Call In The Money
Straddle Put Contract Symbol
Straddle Put Strike
Straddle Put Currency
Straddle Put Last Price
Straddle Put Change
Straddle Put % Change
Straddle Put Volume
Straddle Put Open Interest
Straddle Put Bid
Straddle Put Ask
Straddle Put Contract Size
Straddle Put Expiration
Straddle Put Last Trade Date
Straddle Put Implied Volatility
Straddle Put In The Money
Straddle Expiration Date

NEWS
Error Status
Error Code
Error Message
Total Results
Title
Source Author
URL
Published At
Source Name

NEWS HEADLINES
Error Status1
Error Code1
Error Message1
Total Results1
Headline
Link
Author
Time
Source

NEWS SOURCES
Source ID
Name of Source

JeX Financial

Import Free Financial Data into Excel with JeX

No monthly subscriptions

Excel Ultimate Stocks, Options, Funds JSON Data Import

Testamonials

List of worksheets in Jex Financial

List of JeX fields and how to use them

Blank Fields

Arrays