Group Abstract Group Abstract

Message Boards Message Boards

0
|
7.9K Views
|
5 Replies
|
1 Total Like
View groups...
Share
Share this post:

How does one deal with missing data in FinancialData?

Raymond Low
Posted 4 years ago

So close, so close, but missing data shuts down Mathematica's functions. My data, the first sector -- AdvertisingAgencies -- for example, 133 stocks, when filtered for nyse & nasdaq a mere 33. Problems with missing data so filtered again for 2021 at 193 days, and eight more bit the dust. The list:

secNN01y = {"NASDAQ:ADV", "NASDAQ:BOMN", "NASDAQ:CMPR", "NASDAQ:CNET", \
"NASDAQ:CRTO", "NASDAQ:FLNT", "NASDAQ:ICLK", "NASDAQ:ISIG", \
"NASDAQ:MCHX", "NASDAQ:MGNI", "NASDAQ:NCMI", "NASDAQ:QNST", \
"NASDAQ:SRAX", "NASDAQ:WIMI", "NASDAQ:XNET", "NYSE:CCO", "NYSE:DLX", \
"NYSE:DMS", "NYSE:EEX", "NYSE:INUV", "NYSE:IPG", "NYSE:OMC", \
"NYSE:QUOT", "NYSE:TSQ", "NYSE:WPP"}

I wanted to look at seasonality -- I first tried "Return", but 20 cent stocks raised a dollar or two had 164% returns which skewed the data, "Price" was used. I wanted to check seasonality over a ten year period, 2021 worked but was only ten months, so 2020 was tried for 12 months. 

p20 = FinancialData[secNN01y, "Price", {{2020}, {2020}, "Month"}];

enter image description here

Missing data in the first stock of the list, but if I kept deleting stocks for missing data soon there would not be any data to analyze. I tried using Table to reorganize the data by month, worked for 2021, but not for 2020 with missing data

p20T = Table[p20[[j, 2, 1, 1, i]], {i, 12}, {j, 25}];

I tried Pick with DateObjects on a limited set of data enter image description here

I wanted the mean of the stocks for each month, but the multiple levels in the data defeated Mean. With Rohit's helps the issue was partly resolved enter image description here

I tried to expand the data to the full year -- and missing data became an issue again enter image description here

The first stock in the list only has data for the last three months of 2020. When k calls for the 4th element in the list -- everything crashes

How does one work with FinancialData with all of its flaws???? I can not keep deleing stocks or else there will be so few, the data will be meaningless. If I can not take away, documentation says to replace the missing data with the mean of what is left -- but that is simply beyond my coding ability. Is there a better way???
POSTED BY: Raymond Low
5 Replies
Sort By:
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Hi Duncan,

Have to be careful with this approach because it is not guaranteed that the commonest length series all have the same time intervals.
POSTED BY: Rohit Namjoshi
Duncan Aitken
Duncan Aitken
Posted 4 years ago

One technique I have used is to find the most common lengths. For example,

Map[#["PathLength"] &, p20]
commonestPositions = Position[%, Commonest[%][[1]]]

Then choose those time series that have the most common lengths

p20bis = Extract[p20, commonestPositions]

This will give a list of time series that share the most common length.

Likewise,

secNN01ybis = Extract[secNN01y, commonestPositions]

will give a list of the corresponding symbols.
POSTED BY: Duncan Aitken
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Raymond,

I don't completely understand what you are trying to do, however, you are running into issues because you are trying to select data by indexing months and that will fail if there is no data for a particular month/stock. A way to avoid that is to group the data by month so missing months are not part of the group if data is missing. An example using the code from my previous post.

monthlyMean2020 =
monthlyPriceDataByYear //
(* Don't care about symbol, so drop it *)
Values //
(* Select 2020 values *)
Map[KeySelect[# == DateObject[{2020}, "Year"] &]] // Values // Flatten[#, 2] & //
(* Group by month *)
GroupBy[First -> Last] //
(* Mean by month *)
Map[Mean] // KeySort

Some months in the mean will have three values, one for each stock and some will have two (where ADV is missing), but that does not matter, the mean is computed from whatever data is available.

(* Mean of all data *)
mean2020 = monthlyMean2020 // Mean

Percent change from mean for each month

monthlyMean2020Percent = 100.*(monthlyMean2020 - mean2020)/mean2020

monthlyMean2020Percent // 
 BarChart[#, ChartLabels -> DateValue[Keys@monthlyMean2020, "MonthNameShort"]] &

enter image description here

You can try it on your full list of stocks and see if the result looks reasonable. If it does, you can remove the year selection and generate the chart for all years combined. Or compare the last 2 years with all previous years or ...
POSTED BY: Rohit Namjoshi
Raymond Low
Raymond Low
Posted 4 years ago
POSTED BY: Raymond Low
Rohit Namjoshi
Rohit Namjoshi
Posted 4 years ago

Raymond,

There are some stocks for which FinancialData is incomplete. It looks like ADV is one of them. An option would be to download historical data from another source, Nasdaq or Yahoo and import it.

Can you explain what you mean by "I wanted to look at seasonality". If you want to compare the monthly price for various stocks across years then it would be better to organize the data to make it easier to do that. One option using a subset of your list.

stocks = {"NASDAQ:ADV", "NASDAQ:BOMN", "NASDAQ:CMPR"};
lookbackYears = 3;

monthlyPrice = 
 FinancialData[stocks, 
   "Price", {{2021 - lookbackYears}, {2021}, "Month"}] // AssociationThread[stocks, #] &

toMonth[date_] := DateObject[date, "Month"];
monthlyPriceData = monthlyPrice // Map[#["DatePath"] &] // MapAt[toMonth, #, {All, All, 1}] &

monthlyPriceDataByYear = monthlyPriceData // Map[GroupBy[DateObject[First[#], "Year"] &]]

Then you can visualize or analyze in different ways

monthlyPriceDataByYear // KeyValueMap[DateListPlot[#2, PlotLabel -> #1] &] // Column

enter image description here

monthlyPriceDataByYear // Map[KeySelect[# == DateObject[{2020}, "Year"] &]] // 
   DeleteCases[<||>] // Map[Last] // DateListPlot[#, PlotLabel -> "2020"] &

You have a lot more stocks on your list so you probably need different visualizations and analyses depending on your goal.

enter image description here
POSTED BY: Rohit Namjoshi
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Tag limit exceeded
Note: Only the first five people you tag will receive an email notification; the other tagged names will appear as links to their profiles.
Reply Preview
Attachments
Remove
or Discard