Exporting Stock Financial Data into Excel

Question

I would like to pull 3 attributes (Open, Close, Volume) for a variety of stock symbols. I would like to pull the stock data within a specific range and export it to an excel spreadsheet. I would also like to append the next stock ticker and its attributes to the bottom of the former.

So far I have the following code:

filename = "Data.xls";
data = FinancialData[
   "GE", {"Open", "Close"}, {{2000, 1, 1}, {2021, 1, 1}}];
V = data // Normal;
Export[filename, V];

Several problems are easy to see: Data for Open and Close are saving in separate tabs (I do not know why), adding additional ticker symbols forces all ticker data for a symbol into the same row in the same tab (again I do not know why), I am getting a string e.g.

Quantity[50.29999923706055, "USDollars"]

when all I want is the numerical piece. Finally, I am not getting the headers for each attribute as I am looking for.

I would really appreciate some assistance here. I have looked at other solutions as Creating a Stock Dataset but it doesn't quite give me what I am looking for and my attempts to change it to meet my needs have failed.

Answer 1

Very easy. The reason you're not getting what you want is partly how you're downloading the data from Wolfram's servers (as a TimeSeries rather than as a list you can then combine how you want) and partly because you're not then transforming it correctly before exporting.

This does the job:

(*Download the data in legacy format rather than new TimeSeries format*) 

filename = "Data.xls";
raw = QuantityMagnitude@FinancialData[
    "GE", {"Open", "Close"}, {{2000, 1, 1}, {2021, 1, 1}}, 
    Method -> "Legacy"];
    
    (*Convert the raw dates to ISO Date format*)

dateList = DateString[#, "ISODate"] & /@ raw[[1, All, 1]];
    
    (*Combine the datelist with the open and close series in an association and then create a column-oriented dataset object for easy check and export*)

V = Transpose@
      Dataset@<|"Date" -> dl, "Open" -> raw[[1, All, 2]], 
        "Close" -> raw[[2, All, 2]]|>
    
    (*Export*)

Export[filename, V]

Hope that helps!

Answer 2

I work with financial data in Mathematica and Excel quite frequently and have found the following framework helpful for my applications.

---

Helper functions:

joinColumnToData

joinColumnToData is useful for adding a column of dates or tickers to a table of prices.

joinColumnToData[column_?VectorQ, data_?VectorQ] := Transpose@{column, data};
joinColumnToData[column_?VectorQ, data_?MatrixQ] := ArrayFlatten@{{List /@ column, data}};

---

convertFinancialDataToFlatDatePath

convertFinancialDataToFlatDatePath makes working with FinancialData easier.

For example FinancialData returns TimeSeries objects and I often extract the "Dates" and "Values". In practice I struggle with:

1. "Values" returned are in Quantity, which is slow
2. Quantity is almost always in USD, so Quantity provides no additional value, we are only concerned with the magnitude
3. "Dates" returns a DateObject with full granularity (seconds, timezone, etc.) which is difficult to work with. Eg Select[financialDataset, #Date == DateObject["2020-11-30"] &] would fail as we did not provide the full granularity
4. DateObject does not export nicely so we would prefer this to be a string
5. DateObject can be slow so we would prefer to work in AbsoluteTime

convertFinancialDataToFlatDatePath solves these problems by returning a flat array with "Values" converted to a magnitude and giving you the option to apply a formula to convert "Dates" to the DateString or DateObject of your choice.

convertFinancialDataToFlatDatePath::"usage"="Converts a TimeSeries object from FinancialData to a flat table for easier calculations and exporting. 
Option \"DateConversionFunction\" to convert the default DateObject supplied by TimeSeries. Defaults to Function[{date},DateString[date,\"ISODate\"]]
Some other useful options are:
\"DateConversionFunction\" ->Function[{date},DateString[date,\"ISODateTime\"] (* for intra-day *),
\"DateConversionFunction\" ->Function[{date},DateObject[date,\"Day\",TimeZone->None](* for working in Mathematica *),
\"DateConversionFunction\" ->AbsoluteTime (* for speed *),
\"DateConversionFunction\" ->Identity (* to use the defaults *)";

Options[convertFinancialDataToFlatDatePath]=
    {
      "DateConversionFunction"->Function[{date},DateString[date,"ISODate"]]
    };

convertFinancialDataToFlatDatePath[timeSeries_TemporalData,OptionsPattern[]]:=
    Module[
      {datesRaw,valuesRaw,dates,values},
      datesRaw=timeSeries["Dates"];
      valuesRaw=timeSeries["Values"];

      dates=OptionValue["DateConversionFunction"]/@datesRaw;
      values=QuantityMagnitude@valuesRaw;

      joinColumnToData[dates,values]
    ];

Putting it together:

---

Define the investment universe, allowing for a variety of stock symbols.

You mentioned you only need Open, Close, and Volume but I prefer to download as much as I can and then filter later on. Downloading is often the bottleneck and I don't like having to redownload once I realize I need High and Low data too.

tickers = {"GE", "AAPL"};
dataStart = DateString["2020-11-30", "ISODate"];
dataEnd = DateString["2021-01-01", "ISODate"];

tickerProperties = "OHLCV";
tickerPropertiesHeader = {"Open", "High", "Low", "Close","Volume"};(*change if using something other than "OHLCV"*)
headers = {"Ticker", "Date"}~Join~tickerPropertiesHeader;

---

Get data from Mathematica and put it in a nice format to work with.

(*get list of TimeSeries from Mathematica*)
financialDataRaw=FinancialData[tickers,tickerProperties,{dataStart,dataEnd}];

(*format it*)
financialDataFlat=convertFinancialDataToFlatDatePath/@financialDataRaw;

(*add Tickers to the data*)
tickerArray=MapThread[ConstantArray[#1,#2["PathLength"]]&,{tickers,financialDataRaw}];
financialDataWithTickers=MapThread[joinColumnToData,{tickerArray,financialDataFlat}];

financialData=Map[AssociationThread[headers,#]&,financialDataWithTickers,{2}]//Flatten;

financialData is a list of associations, which is fast and easy to work with in Mathematica. It is my preferred format.

For example to get the attributes you want : Select[financialData, #Ticker == "GE" &][[All, {"Date", "Open", "Close", "Volume"}]]

---

Export the data.

As you might download different datasets, it can be helpful to name the file according to the tickers and dates of the data in the file. This makes it easier to identify the file contents.

I have used ".xlsx" here as it allows for more rows etc. over the old ".xls" format. You can also use a text file format. I prefer ".tsv" over ".csv" as you can copy and paste to Excel directly from a ".tsv" file.

fileBaseName=StringRiffle[tickers," "]<>"_"<>dataStart<>"-"<>dataEnd;
fileExtension=".xlsx"; 
fileName=fileBaseName<>fileExtension;

financialDataset=Dataset[financialData];
Export[fileName,financialDataset]