Creating a Stock Dataset

Question

I've been looking into how to create a stock database in Mathematica using Dataset. This may or may not be a sensible idea, depending on the quantity of data required, although the possibility of holding the data in memory in Dataset form certainly has its attractions.

I start by using FinancialData to download a year of historical price data for a single stock:

AAP = FinancialData["AAP",   "OHLCV", {DatePlus[Today, {-1, "Year"}], 
DatePlus[Today, {-1, "Day"}]}];

FinancialData isn't listable, so I'm going to have to loop over a list of tickers, eventually.

Let's do something about the dates:

dates = DateObject[AAP[[All, 1]]];
prices = AAP[[All, 2]];

Splice the data back together as follows:

pricedata = Partition[Flatten[Riffle[dates, prices]], 6];
Dimensions[pricedata]
{253, 6}

Take[pricedata, 1]
{{DateObject[{2015, 11, 4}], 198.455, 199.474, 196.489, 199.024,692600}} 

After various false starts I eventually discover how to create the initial dataset, as follows:

colheads = {"Date", "Open", "High", "Low", "Close", "Volume"};
priceDataset = Dataset[AssociationThread[colheads -> #] & /@ pricedata]

which seems to give the right result:

For example:

priceDataset[Mean, "Volume"] // N 
1.07171*10^6

My question is, where to go from here?

The typical applications of the final dataset will include both (a) time series and (b) cross-sectional computations, for example (a) computing the historical volatility for each stock and (b) finding the (weighted average) return of a portfolio comprising a subset of stocks on each date

I could add the stock ticker in another column to the above dataset and then concatenate the datasets for all the stocks together to create a very large rectangular dataset. This would be inefficient and, besides, there is other information one would like to append for each ticker symbol, such as, perhaps, the company name, sector, etc. So a hierarchical format appears more appropriate, with the stock ticker as the primary key.

I was hoping for some guidance as to how to go about creating such a hierarchical dataset and/or thoughts on what the most appropriate data structure would be, given the (fairly obvious) applications.

If I succeed in getting this done I'll share the final code here, as I expect others may have a similar interest to my own.

Addendum: What's slightly tricky about this is that I can't find much in the documentation about how to create datasets - most of the examples relate to querying an already existing dataset, or creating very simple non-hierarchical datasets.

I worked on the planets dataset example using

planets//Normal

to better understand the structure.

From which, the following suggests itself:

stockDataset = Dataset[<|"AAP" -> 
<|"Name" -> "Advance Auto parts Inc.", "Prices" -> priceDataset|>|>]

This does indeed produce a hierarchical structure:

This is sort of ok. But natural queries fail:

stockDataset["AAP", "Prices"]
Dataset[]

or:

stockDataset["AAP", "Prices", 1, "Close"]
Missing["PartInvalid", "Close"]

You have to use queries in this kind of format:

stockDataset["AAP", "Prices"][Max, "Close"]
200.023

The reason is that the prices dataset is a simple table, without a key.
Thinking about it, it would surely be better to use date as a key in constructing the pricesDataset. Then natural queries in the first format would work. More importantly, you are going to need to be able to key on dates in order to construct cross-sectional datasets.

Addendum 2: It has been pointed out that setting up the price data as a dataset as above creates a problem. A better arrangement is to define the stock dataset as follows:

stockDataset = Dataset[<|"AAP" -> <|"Name" -> 
"Advance Auto parts Inc.", "Prices" -> Normal@priceDataset|>|>]

Then queries in the "natural" form work fine, for example:

stockDataset["AAP", "Prices", Max, "Close"]
200.023

This is better than my first attempt, but I still think the price data needs to be indexed by date, so that cross-sectional analysis can be carried out more easily.

I need to amend this code before creating the price dataset:

AssociationThread[colheads -> #] & /@ pricedata

Answer 1

Dataset has been designed for hierarchical data and will fit your problem well. You should start by mapping out the hierarchy and then building a function that builds that hierarchy as an association.

tickerData[ticker_, dateSpan_] :=
 Module[
  {nameDetails, tradeHistory, analystEstimates,
   nameColumns = {"Name", "Exchange", "Sector"},
   analystColumns = {"PriceTarget", "YearEarningsEstimate", 
     "ForwardEarnings", "QuarterForwardEarnings"},
   tradeColumns = {"Date", "Open", "High", "Low", "Close", "Volume"}
   },

  nameDetails = FinancialData[ticker, #] & /@ nameColumns;
  analystEstimates = FinancialData[ticker, #] & /@ analystColumns;
  tradeHistory = FinancialData[ticker, "OHLCV", dateSpan];

  <|ticker ->
    <|
     "About" -> AssociationThread[nameColumns, nameDetails],
     "AnalystEstimates" -> AssociationThread[analystColumns, analystEstimates],
     "TradeHistory" -> 
      Map[AssociationThread[
         tradeColumns, {DateObject@#[[1]], Sequence @@ #[[2]]}] &, tradeHistory]
     |>
   |>
  ]

tickerData creates an Association hierarchy with the ticker as the Key and three keys at the next level to hold categorised information. Create a Dataset with three tickers by

ds =
 Dataset[
  Association[
   tickerData[#, {DatePlus[Today, {-1, "Year"}], DatePlus[Today, {-1, "Day"}]}] & /@
       {"AAP", "GOOG", "TSLA"}
   ]]

ds can be Query'ed for information.

ds["AAP", "About", "Name"]

"Advance Auto Parts Inc"

ds[All, "About", "Name"]

Analysis is easier with this structure because you can apply the same functions to all the data sets at once and they are return in Dataset with row keys.

dsTimeSeries =
 ds[All,
  "TradeHistory",
  TimeSeries[#, TemporalRegularity -> True] &,
  {"Date", "Close"} /* Values]

You can expand a returned Dataset.

dsTimeSeries2 = 
 dsTimeSeries[All, <|"TimeSeries" -> #, "DateListPlot" -> DateListPlot@#|> &]

Access items through the hierarchy.

dsTimeSeries2["GOOG", "DateListPlot"]

Can also combine items through the hierarchy.

dsTimeSeries2[DateListPlot,
 "TimeSeries" /*
  (With[{v0 = #["FirstValue"]}, TimeSeriesMap[100 #/v0 &, #]] &)]

This can continue as far as needed.

dsTimeSeries3 = 
 dsTimeSeries2[
  All, <|#, "Peaks" -> EventSeries[FindPeaks[#["TimeSeries"], 4]], 
    "Troughs" -> EventSeries[-FindPeaks[-#["TimeSeries"], 4]]|> &]

dsPlots = 
  dsTimeSeries3[
   Map[TimelinePlot[#, PlotStyle -> {Red, Green}] &], 
   {"Troughs", "Peaks"}];

dsPlots["TSLA"]

dsPlots2 =
  dsTimeSeries3[
   Map[
    DateListLogPlot[#,
      Joined -> {True, False, False},
      PlotStyle -> {Automatic, {PointSize[.015], Red}, {PointSize[.015], Green}}] &],
   {"TimeSeries", "Troughs", "Peaks"}];

    dsPlots2["TSLA"]

Hope this helps.

Answer 2

Here are a few things to get you started. My code is for demonstration purposes only and is neither optimized for computational speed nor memory, but it's concise and produces pretty potent results. You're making it much too complicated trying to group all prices under "Price", which will not be sustainable overtime as you start running models. Ultimately, you'll want your data in lists anyway for computational speed, and you need to get good at using various list-processing tools, such as Pick[], Position[], Extract[], etc.

colheads = {"Symbol", "Date", "Open", "High", "Low", "Close", "Volume"};
stocks = Dataset[
  AssociationThread[colheads->#]& /@ 
   Join @@ (With[{sym = #}, {sym, DateObject@#[[1]]}~Join~#[[2]] & /@
          FinancialData[#, "OHLCV", Today - Quantity[1, "Years"], 
          "DateValue"]] & /@ {"AAPL", "MSFT", "GOOG", "TSLA"})
];

You can get various indicators from FinancialIndicator[] to store in the Dataset:

FinancialIndicator["EMA", 20][FinancialData["MSFT","Price",
  Today - Quantity[1,"Years"]]] //DateListPlot

But you can also compute your own if you know the formula:

ExponentialMovingAverage[
  Normal@stocks[Select[#Symbol == "MSFT" &], "Close"],
  2/(20+1)] //ListLinePlot

Computing simple values and getting TimeSeries out for analysis is pretty trivial:

N@Mean@stocks[Select[#Symbol == "AAPL" &], "Volume"]
TimeSeries@Normal@Values@stocks[
  Select[#Symbol=="GOOG" &&
         #Date>=DatePlus[Today, {-50,"BusinessDay"},
                         HolidayCalendar->"UnitedStates", "NYSE"}]&],
  {"Date","Volume"}]
DateListPlot@%

Sample of stock prices plotted:

stocks[Select[#Symbol=="TSLA"&], {"Date","Close"}][-50;;] //DateListPlot

Only the pivot points (highs/lows) plotted:

pivotpoints = Cases[Partition[Normal@Values@
  stocks[Select[#Symbol=="TSLA" &], {"Date", "Close"}][-50 ;;],
  3, 1], c_/;!Equal @@ Sign@Differences@c[[All,2]]][[All,2]];
DateListPlot@pivotpoints

Plot the timeline of high/low events:

{highs, lows} = GatherBy[
  Partition[pivotpoints,2,1], #[[1,2]]>#[[2,2]]&][[All,All,1,1]]
TimelinePlot[{lows, highs}, PlotStyle -> {Red, Green}]

This code ought to contain more than enough building blocks to get you to dissect data any and every way you want, and compute and plot anything you want.

Enjoy! Feel free to ask for additional examples if you cannot figure something out.