Message Boards Message Boards

GROUPS:

Obtaining and exporting financial data for a specific time range

Posted 10 years ago
10755 Views
|
10 Replies
|
2 Total Likes
|
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?
POSTED BY: George P
10 Replies
Posted 10 years ago
Sean
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.
L.W.Sterritt
POSTED BY: Lanny 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.
POSTED BY: Sean Clarke
Posted 10 years ago
I  would like to plot the history of the SP500 P/E ratio.
L.W.Sterritt
POSTED BY: Lanny Sterritt
Posted 10 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"}]
POSTED BY: George P
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 10 years ago
Thank you very much for your help!
It is my first time with this software, so I don't know so much!
POSTED BY: George P
To become proficient with working with stock data, you may want to review programming with Mathematica. In this case, especially with lists: http://reference.wolfram.com/mathematica/guide/ListManipulation.html

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 10 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?
POSTED BY: George P
This will give you the adjusted closing price. Depending on your application, you may want to use "RawClose" instead of just "Close".

http://support.wolfram.com/kb/4299
POSTED BY: Sean Clarke
Posted 10 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" *)

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.
POSTED BY: William Rummler
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract