Message Boards Message Boards

10 Replies
2 Total Likes
View groups...
Share this post:

Obtaining and exporting financial data for a specific time range

Posted 11 years ago
I would like to take the daily close prices of all S&P500 stocks from 1/1/2012 to 12/31/2012 and then to export these to an excel file. Which function should I use?
10 Replies
Posted 10 years ago
The P/E for the SP500 is frequently quoted in financial “news”. I’m not sure how it is weighted; I was hoping that there may be an agreed upon methodology, but it may be that it is agenda driven. I would like to try your suggestions, and perhaps learn how the quoted numbers are calculated.
POSTED BY: Lanny Sterritt
If you quickly look online, you'll see that this data isn't available throught Wolfram|Alpha at this time:

Additionally I can't find the information through the Yahoo Finance API using the FinancialData function:
FinancialData["^GSPC", "PERatio"]

When you say you want the PE ratio of the SP500, does that mean you want a weighted average of the PE ratios of the component companies based on their marketshare? or total estimated worth? I don't know what the proper weighting would be, but I can think of a couple of ways to weight this.
POSTED BY: Sean Clarke
Posted 10 years ago
I  would like to plot the history of the SP500 P/E ratio.
POSTED BY: Lanny Sterritt
Posted 11 years ago
Thank you, but I don't want the prices for the Index S&P500.
Instead, I want the prices for all of the 500 component stocks of this index.
I believe that the correct function is:

Map[FinancialData[#, {"1/1/2012", "12/31/2012"}] &, {"MMM", "ABT", "ABBV", "ANF", "ACE", "ACN", "ACT", "ADBE", "ADT", "AMD", "AES", "AET", "AFL", "A", "GAS", "APD", "ARG", "AKAM", "AA", "ALXN", "ATI", "AGN", "ALL", "ALTR", "MO", "AMZN", "AEE", "AEP", "AXP", "AIG", "AMT", "AMP", "ABC", "AMGN", "APH", "APC", "ADI", "AON", "APA", "AIV", "AAPL", "AMAT", "ADM", "AIZ", "T", "ADSK", "ADP", "AN", "AZO", "AVB", "AVY", "AVP", "BHI", "BLL", "BAC", "BK", "BCR", "BAX", "BBT", "BEAM", "BDX", "BBBY", "BMS", "BRK.B", "BBY", "BIIB", "BLK", "HRB", "BA", "BWA", "BXP", "BSX", "BMY", "BRCM", "BF.B", "CHRW", "CA", "CVC", "COG", "CAM", "CPB", "COF", "CAH", "CFN", "KMX", "CCL", "CAT", "CBG", "CBS", "CELG", "CNP", "CTL", "CERN", "CF", "SCHW", "CHK", "CVX", "CMG", "CB", "CI", "CINF", "CTAS", "CSCO", "C", "CTXS", "CLF", "CLX", "CME", "CMS", "COH", "KO", "CCE", "CTSH", "CL", "CMCSA", "CMA", "CSC", "CAG", "COP", "CNX", "ED", "STZ", "GLW", "COST", "COV", "CCI", "CSX", "CMI", "CVS", "DHI", "DHR", "DRI", "DVA", "DE", "DELL", "DLPH", "DAL", "DNR", "XRAY", "DVN", "DO", "DTV", "DFS", "DISCA", "DG", "DLTR", "D", "DOV", "DOW", "DPS", "DTE", "DD", "DUK", "DNB", "ETFC", "EMN", "ETN", "EBAY", "ECL", "EIX", "EW", "EA", "EMC", "EMR", "ESV", "ETR", "EOG", "EQT", "EFX", "EQR", "EL", "EXC", "EXPE", "EXPD", "ESRX", "XOM", "FFIV", "FDO", "FAST", "FDX", "FIS", "FITB", "FSLR", "FE", "FISV", "FLIR", "FLS", "FLR", "FMC", "FTI", "F", "FRX", "FOSL", "BEN", "FCX", "FTR", "GME", "GCI", "GPS", "GRMN", "GD", "GE", "GIS", "GM", "GPC", "GNW", "GILD", "GS", "GT", "GOOG", "GWW", "HAL", "HOG", "HAR", "HRS", "HIG", "HAS", "HCP", "HCN", "HP", "HES", "HPQ", "HD", "HON", "HRL", "HSP", "HST", "HCBK", "HUM", "HBAN", "ITW", "IR", "TEG", "INTC", "ICE", "IBM", "IGT", "IP", "IPG", "IFF", "INTU", "ISRG", "IVZ", "IRM", "JBL", "JEC", "JDSU", "JNJ", "JCI", "JOY", "JPM", "JNPR", "KSU", "K", "KEY", "KMB", "KIM", "KMI", "KLAC", "KSS", "KRFT", "KR", "LTD", "LLL", "LH", "LRCX", "LM", "LEG", "LEN", "LUK", "LIFE", "LLY", "LNC", "LLTC", "LMT", "L", "LO", "LOW", "LSI", "LYB", "MTB", "MAC", "M", "MRO", "MPC", "MAR", "MMC", "MAS", "MA", "MAT", "MKC", "MCD", "MHFI", "MCK", "MJN", "MWV", "MDT", "MRK", "MET", "MCHP", "MU", "MSFT", "MOLX", "TAP", "MDLZ", "MON", "MNST", "MCO", "MS", "MOS", "MSI", "MUR", "MYL", "NBR", "NDAQ", "NOV", "NTAP", "NFLX", "NWL", "NFX", "NEM", "NWSA", "NEE", "NLSN", "NKE", "NI", "NE", "NBL", "JWN", "NSC", "NTRS", "NOC", "NU", "NRG", "NUE", "NVDA", "NYX", "ORLY", "OXY", "OMC", "OKE", "ORCL", "OI", "PCG", "PCAR", "PLL", "PH", "PDCO", "PAYX", "BTU", "JCP", "PNR", "PBCT", "POM", "PEP", "PKI", "PRGO", "PETM", "PFE", "PM", "PSX", "PNW", "PXD", "PBI", "PCL", "PNC", "RL", "PPG", "PPL", "PX", "PCP", "PCLN", "PFG", "PG", "PGR", "PLD", "PRU", "PEG", "PSA", "PHM", "PVH", "QEP", "PWR", "QCOM", "DGX", "RRC", "RTN", "RHT", "REGN", "RF", "RSG", "RAI", "RHI", "ROK", "COL", "ROP", "ROST", "RDC", "R", "SWY", "SAI", "CRM", "SNDK", "SCG", "SLB", "SNI", "STX", "SEE", "SRE", "SHW", "SIAL", "SPG", "SLM", "SJM", "SNA", "SO", "LUV", "SWN", "SE", "STJ", "SWK", "SPLS", "SBUX", "HOT", "STT", "SRCL", "SYK", "STI", "SYMC", "SYY", "TROW", "TGT", "TEL", "TE", "THC", "TDC", "TER", "TSO", "TXN", "TXT", "HSY", "TRV", "TMO", "TIF", "TWX", "TWC", "TJX", "TMK", "TSS", "TRIP", "FOXA", "TSN", "TYC", "USB", "UNP", "UNH", "UPS", "X", "UTX", "UNM", "URBN", "VFC", "VLO", "VAR", "VTR", "VRSN", "VZ", "VIAB", "V", "VNO", "VMC", "WMT", "WAG", "DIS", "WPO", "WM", "WAT", "WLP", "WFC", "WDC", "WU", "WY", "WHR", "WFM", "WMB", "WIN", "WEC", "WPX", "WYN", "WYNN", "XEL", "XRX", "XLNX", "XL", "XYL", "YHOO", "YUM", "ZMH", "ZION", "ZTS"}]
You can also just use natural language and Wolfram|Alpha integration. After you press equal sign twice at the beginning of a new line - just type in plane English:

To get the data:
WolframAlpha["S&P500 stocks from 1/1/2012 to 12/31/2012", {{"DateRangeSpecified:Close:FinancialData", 1}, "TimeSeriesData"}]

To understand how this code is obtained follow this tutorial:

POSTED BY: Vitaliy Kaurov
Posted 11 years ago
Thank you very much for your help!
It is my first time with this software, so I don't know so much!
To become proficient with working with stock data, you may want to review programming with Mathematica. In this case, especially with lists:

You can achieve this with a Map expression. For example:
Map[FinancialData[#, {"1/1/2012", "12/31/2012"}] &, {"GE", "MMM", "ABT", "ANF"}]
POSTED BY: Sean Clarke
Posted 11 years ago
Thank you for your response!

I can use the FinancialData function for one stock, but I don't know how to use it for 500 stocks.
For example if I have "GE", "MMM", "ABT", "ANF", etc (a total of 500 tickers) and I want the historical close for these 500 tickers from 2012/1/1 to 2012/12/31, how should the FinancialData function be?
For one stock it is: FinancialData["GE", {"2012, 1, 1", "2012, 12, 31"}]
For 500 stocks? FinancialData[{"GE", "MMM", "ABT", "ANF" etc.}, {"2012, 1, 1", "2012, 12, 31"}] ?
Or should I calculate this function for every ticker of the 500 stocks?
This will give you the adjusted closing price. Depending on your application, you may want to use "RawClose" instead of just "Close".
POSTED BY: Sean Clarke
Posted 11 years ago
Luckily, this is quite straightforward using two of Mathematica's more broadly applicable functions, FinancialData and Export:

 data = FinancialData["^GSPC", {"1/1/2012", "12/31/2012"}];
 data[[;; 3]]
 (* Out:
 {{{2012, 1, 3}, 1277.06}, {{2012, 1, 4},
   1277.3}, {{2012, 1, 5}, 1281.06}}
Export["data.xlsx", data]

(* Out: "data.xlsx" *)

Don't forget to check out the many available tutorials and guides that are linked through these reference pages.
POSTED BY: William Rummler
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
or Discard

Group Abstract Group Abstract