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?
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" *)
http://reference.wolfram.com/mathematica/ref/FinancialData.html
http://reference.wolfram.com/mathematica/ref/Export.html
Don’t forget to check out the many available tutorials and guides that are linked through these reference pages.
This will give you the adjusted closing price. Depending on your application, you may want to use “RawClose” instead of just “Close”.
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?
To become proficient with working with stock data, you may want to review programming with Mathematica. In this case, especially with lists: List Manipulation—Wolfram Documentation
You can achieve this with a Map expression. For example:
Map[FinancialData[#, {"1/1/2012", "12/31/2012"}] &, {"GE", "MMM", "ABT", "ANF"}]
Thank you very much for your help!
It is my first time with this software, so I don’t know so much!
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:
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”}]
I would like to plot the history of the SP500 P/E ratio.
L.W.Sterritt
If you quickly look online, you’ll see that this data isn’t available throught Wolfram|Alpha at this time:
http://www.wolframalpha.com/input/?i=SP500+P%2FE+ratio
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.
Sean
The P/E for the SP500 is frequently quoted in financial news. Im 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.
L.W.Sterritt