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
stockDataset
if you define it usingNormal@priceDataset
(i.e. remove theDataset
wrapper from around the data before putting it into a container dataset). $\endgroup$