Friday, March 4, 2016

Free delayed quotes and a plethora of other financial data

If you are looking to import quotes into an excel spreadsheet, I haven't found anything else as convenient or extensive.  It can take you a while to get up to speed on the data available but it is worth it.   The range of data available, for free, is mind boggling.  



Here's just a sample of the data available from the Yahoo feed:

Basic QuoteFundamentalsTechnicalsEstimatesReal-Time (ECN)
CodeDescriptionCodeDescriptionCodeDescriptionCodeDescriptionCodeDescription
sSymbolj1Market Capitalizationa2Average Daily Volumet81yr Target Pricek1Last Trade (ECN with Time)
nNamef6Float Sharest7Ticker Trende9EPS Est. Next Quarterb3Bid (ECN)
pPrevious Closer5PEG Ratioj52-week Lowe7EPS Est. Current Yrb2Ask (ECN)
oOpenp5Price/Salesj5Change From 52-week Lowe8EPS Est. Next Yearc6Change (ECN)
hHighp6Price/Bookj6Pct Chg From 52-week Lowr6Price/EPS Est. Current Yrw4Day's Value Change (ECN)
gLowb4Book Valuek52-week Highr7Price/EPS Est. Next Yrc8After Hours Change (ECN)
xExchangerP/E Ratiok4Change From 52-week Highj3Market Cap (ECN)
l1Last Trade (Price Only)eEarnings/Sharek5Pct Chg From 52-week Highr2P/E (ECN)
lLast Trade (With Time)j4EBITDAm350-day Moving Avgk2Change & Percent (ECN)
d1Date of Last Trades7Short Ratiom7Change From 50-day Moving Avgv7Holdings Value (ECN)
t1Time of Last TradedDividend/Sharem8Pct Chg From 50-day Moving Avgg5Holdings Gain & Percent (ECN)
k3Last Trade SizeyDividend Yieldm4200-day Moving Avgg6Holdings Gain (ECN)
c1Changer1Dividend Pay Datem5Change From 200-day Moving Avgm2Day's Range (ECN)
p2Percent ChangeqEx-Dividend Datem6Pct Chg From 200-day Moving Avgi5Order Book (ECN)
cChange & Percent
vVolumeOption Quote Additions
mDay's RangeCodeDescription
j52-week Lowo1Open interest?
k52-week Highp3Type of option
w52-week Rangee3Expiration date
bBids3Strike price
b6Bid SizenName of option
aAsk
a5Ask Size
note, the last trade quote is l1 (lowercase 'L' then the number '1')  (that drove me nuts for a while)
here's how it looks in my spreadsheet  =RCHGetYahooQuotes(+Z2,"l1")

This is only one small segment of the data,  The full excel list of all the data points available can be found here:  http://ogres-crypt.com/SMF/Elements/ and it is a truly phenominal amount of data.  Yes, some of it is repetitive but if you are looking for data on a security, start here.  


There's also an active message board if you have any questions on implementation:


Of course you can get one cell to refer to another, so one can populate quite a bit of data in a spreadsheet by just entering the symbol.  Best part is it's all free.  

I use the plug-in almost daily and would honestly be lost without it.  I suggest you give it a try as well.


1 comment: