Get company financial data in Excel

GET COMPANY FINANCIAL DATA IN EXCEL

Over 800 types of financial information on hundreds of thousands of stocks, options, indices, ETFs and mutual funds on 86 international exchanges.

Import Free Financial Data into Excel with JeX

How do you get company financial data into Excel? Understandably, many people prefer to monitor stock market/portfolio activity using Excel because its ad-free and extremely flexible. But to do so you need access to financial data. 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, that data is vast in scope and quotes are real-time for NASDAQ, NYSE and many 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. An alternative is  JeX Financial, a ready-built system you simply edit to your own needs. On the surface, Jex Financial is a modestly priced workbook of 23 worksheets which are easy to understand, replicate and customize. No VBA knowledge is required, and its not an add-in. 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 feeds that are useful to you.

Upshot is, 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...

Excel Ultimate Stocks, Options, Funds JSON Data Import

Simple access to data

Creating your own Excel financial data spreadsheets is very straightforward. A single user defined function (the JeX UDF), is used for all the data extraction in this workbook. This is simply a formula you can type into Excel. Financial data is extracted and placed in the cell or cells you put the formula in. 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. A few calls require just 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 we supply the extra simple UDFs "DateToUnix()" and "UnixToDate()" to handle the translation from Excel date format to Unix date format. 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.

 

Requires Excel 2007 or better and Windows. Mac not supported.

List of worksheets in Jex Financial