Import Financial JSON Data to Excel

Use JSON extraction to pull financial data into Excel

Excel workbook delivers hundreds of pieces of financial information

Tapping into online JSON data

Turns out, there is a lot of financial information which you can pull into Excel, once you have a good JSON extractor (like the one available on this site). I have put together an Excel workbook with 17 worksheets of financial information, over 800 types of financial information on stocks, options, funds and ETFs. Watch the video below to see it in action.

Excel Ultimate Stocks, Options, Funds JSON Data Import

Simple access to data

Creating your own spreadsheets is very straightforward. A single user defined function (the JeX UDF), is used for all the data extraction in this workbook. A simple example would be:-

=JeX("Last Price", "AMZN")

This will return the last regular market price of Amazon, and it is real-time data. That same methodology covers most of the function calls. If the call returns an array, simply select the cells you wish the data to appear in, type the formula then use Cntrl-Shift-Enter to enter it an Excel array formula. Orientation can be horizontal or vertical. Or you can add an index as the last parameter. Some calls require a little extra information. For example, if you want a list of call contract symbols for MSFT for the 12/7/2018 expiration, you would enter

=JeX("Call Contract Symbol", "MSFT", DateToUnix("12/7/2018"))

as an array formula. The expiration date (and all dates) need to be UNIX timestamps, so there are simple UDFs "DateToUnix()" and "UnixToDate()" to handle the translation. Historical prices require both a time range and time interval. That's about as complex as it gets; these are the only UDFs in the entire workbook. Options prices are a little delayed.

 

Below is the complete lists for Keywords accepted by the JeX function.

Summary Details

Previous Close
Open
Day Low
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

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

Income Statement Annual (4 years)

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

Historical Data (minute, hour, day, 5 day, month granularity)

HDate
HOpen
HHigh
HLow
HClose
HVolume
HAdjClose

REVERSED:
RHDate
RHOpen
RHHigh
RHLow
RHClose
RHVolume
RHAdjClose

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

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

Earnings Trends

EARNINGS INFORMATION
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

Institutional Ownership

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

Share Purchase/Sale 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 Info

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

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

Statistics

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

Cash Flow History (4 years)

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

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

Price (Stock, fund, currency, index, ETF)

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

Fund Ownership

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

Major Holders

Insiders % Held
Institutions % Held
Institutions Float % Held
Institutions Count

SEC Filing Information

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

Options by expiration date

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

Fund Holdings

Grade Date
Firm
To Grade
From Grade
Action
UPGRADE DOWNGRADE HISTORY
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

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

Events

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

Balance Sheet History (4 year)

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

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

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

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

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

Recommendation Trends

Rec Period
Rec Strong Buy
Rec Buy
Rec Hold
Rec Sell
Rec Strong Sell

Upgrade/Downgrade History

Grade Date
Firm
To Grade
From Grade
Action

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

Option Detailed 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