8
$\begingroup$

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: enter image description here

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: enter image description here

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
$\endgroup$
  • $\begingroup$ just an idea: since you want to create some functionality that has to do with portfolio analysis and since a portfolio is comprised of a set of weights and a (sub)set of stocks from the database you are creating, perhaps it would be useful to introduce fields such as the stock symbol in the database and use the date as a primary key. In this way, a portfolio will be a linear combination of some rows of the initial database with a new symbol. The motivation is to treat a portfolio as another security. I understand you reject this 'unified' approach but imho I think it better suits the purpose. $\endgroup$ – user42582 Nov 5 '16 at 9:16
  • $\begingroup$ The "natural" queries will work for stockDataset if you define it using Normal@priceDataset (i.e. remove the Dataset wrapper from around the data before putting it into a container dataset). $\endgroup$ – WReach Nov 5 '16 at 14:22
  • $\begingroup$ So: stockDataset = Dataset[<|"AAP" -> <|"Name" -> "Advance Auto parts Inc.", "Prices" -> Normal@priceDataset|>|>] Then indeed stockDataset["AAP", "Prices", 1, "Close"] works: 199.024 $\endgroup$ – Jonathan Kinlay Nov 5 '16 at 16:49
  • $\begingroup$ @JonathanKinlay, I was reading your post with big interest, as Finance is also my main application area with Mathematica. Do you have any news on your attempt to build your dataset? $\endgroup$ – Rod Nov 9 '16 at 23:54
7
$\begingroup$

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"}
   ]]

Mathematica graphics

ds can be Query'ed for information.

ds["AAP", "About", "Name"]
"Advance Auto Parts Inc"
ds[All, "About", "Name"]

Mathematica graphics

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]

Mathematica graphics

You can expand a returned Dataset.

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

Mathematica graphics

Access items through the hierarchy.

dsTimeSeries2["GOOG", "DateListPlot"]

Mathematica graphics

Can also combine items through the hierarchy.

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

Mathematica graphics

This can continue as far as needed.

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

Mathematica graphics

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

dsPlots["TSLA"]

Mathematica graphics

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

    dsPlots2["TSLA"]

Mathematica graphics

Hope this helps.

$\endgroup$
  • $\begingroup$ This is a great answer and a real tour-de-force of the functionality of Dataset. $\endgroup$ – Jonathan Kinlay Sep 13 '18 at 8:52
  • $\begingroup$ That said, it's probably a little more complicated and duplicative than would be required in a typical finance application. There may well be applications where it makes sense to store everything in the form of time series, but that is a space/memory intensive approach. In most cases, I imagine, a researcher would want time series as the exception, rather than the rule and could create them "on-the-fly" as required rather than storing them for every stock. $\endgroup$ – Jonathan Kinlay Sep 13 '18 at 8:56
  • $\begingroup$ Still, Edmund's answer is a superb illustration of how a hierarchical database should be set up: 10/10! $\endgroup$ – Jonathan Kinlay Sep 13 '18 at 8:57
  • $\begingroup$ @JonathanKinlay The duplicity is for the sake of introducing the idea. In practice you would not need so many Dataset objects as you can store the additional elements in the original object. $\endgroup$ – Edmund Sep 14 '18 at 11:05
  • $\begingroup$ @JonathanKinlay Also, do not discount the utility of TimeSeries objects. There is framework supporting these objects for time series analysis ( reference.wolfram.com/language/guide/TimeSeries.html ), which is quite useful for financial analysis applications. $\endgroup$ – Edmund Sep 14 '18 at 11:12
4
$\begingroup$

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"})
];

enter image description here

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

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

enter image description here

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

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

enter image description here

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@%

enter image description here

Sample of stock prices plotted:

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

enter image description here

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

enter image description here

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}]

enter image description here

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.

$\endgroup$
  • $\begingroup$ Just noticed that I didn't validate the Timeline plot at the end. Red dots are actually highs. FYI. )) $\endgroup$ – Gregory Klopper Apr 20 '17 at 1:53
  • 1
    $\begingroup$ I think Gregory's answers is simpler and a little closer in function and form to what a financial analyst typically requires, which is usually in the form of a table rather than a hierarchical database. As he shows, using his approach you can easily create timeseries from the dataset, if you need to. Other than that, its a matter of gaining facility with the complexities of various list manipulation tools available in MMA, which clearly Gregory is expert in. Excellent work. $\endgroup$ – Jonathan Kinlay Sep 13 '18 at 9:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.