Factor and data series importing

We are introducing two new tools for all P123 users. They should be available later today.

Under the Research menu you’ll see a new category: Series & Factors.

The old “Custom Series” tool is now under this menu, relabeled “Aggregate (Custom) Series.”

The new tools are Data Series and Stock Factors.

A data series is a list of dates and values; a stock factor is a list of dates, tickers, and values. You can import them as CSV files. Here’s how you can use them.

The data series function just like custom series. Once you save a data series, you can chart it and then use the values with GetSeries. GetSeries is used in the series parameter for a number of commands, including Close, SMA, EMA, etc.

So, for example, let’s say you upload the FRED data for the US leading index. And let’s say you only want to invest when the index is 0.5% or higher. So you copy the USSLIND data from FRED, open the data series tool, upload the CSV file, give it a name (say “USSLIND”), and then in your buy rule, or a screener rule, you can specify Close(0,GetSeries(“USSLIND”)) > 0.5.

The stock factor tool allows you to import outside data about tickers (both stock and ETF). You can use this, for example, to import an ETF’s historical holdings, the executive compensation of companies, or alternative data sources for adjusted EBITDA if you can get it. You can upload the historical Zacks ranks of companies (if you can get that) to see how well they’d backtest. If you can get satellite images of parking lots, you can upload the number of shoppers visiting public retail companies.

The tool works just like a custom formula, except that it has two dollar signs in front of the name instead of one. So you can use stock factors in screens, ranking systems, and simulations—everywhere you can use a custom formula. If a ticker changes, you don’t have to reload the file: all the data is stored based on the ticker ID.

Have fun with these new tools, and let us know what you think.

I have historic holdings of ETF USMV for longer than 5 years, taken every 3 months. How do I get this into a Stock Factor database, and how can this be used to run a simulation?

USMV holds over 200 positions. I have about 24 “inlists” of them, but not historic prices.

Is date order important or does that get sorted out in the backend? The following use Monday dates, will they be presented to the simulator as Monday morning data (i.e. prior to market open)?

2019-09-09,IBM,NA
2019-09-09,AAPL,NA
2019-09-09,CSCO,NA
2019-09-16,CSCO,NA
2019-09-16,OXY,NA

EDIT: it looks like the systems sort submitted data. The above was d/l back as;
2019-09-16,CSCO,N/A
2019-09-16,OXY,N/A
2019-09-09,AAPL,N/A
2019-09-09,CSCO,N/A
2019-09-09,IBM,N/A

Once I have a stock-factor list, how do I construct a Universe? My imagination is failing me.

FANTASTIC - I’ve been waiting for this feature!!!

Thank you!

Chris

Stock factors data down-loads with “N/A” which, when up-loaded again, causes an error report.

Create a CSV file with the date, the ticker, and the value “1”

Upload that as a stock factor. Call it $$USMV.

You can now use $$USMV in a screener. $$USMV = 1 will get you only stocks that were held in USMV on a particular date.

Sorry about that. You have to take the slash out of the N/A before you upload.

If you see any other bugs like this, please point them out.

Are all the up-loads additive? For example;

load 1;
2019-09-09,IBM,1
2019-09-09,AAPL,1
2019-09-09,CSCO,1
2019-09-16,CSCO,1
2019-09-16,OXY,1

oops, made a mistake on OXY.
load 2:
2019-09-09,IBM,1
2019-09-09,AAPL,1
2019-09-09,CSCO,1
2019-09-16,CSCO,1

OXY is still a stock factor.

So the method for deleting a bad entry is to delete the series and up-load a clean one? Is that right?

Activating a stock looks like;
2019-09-09,IBM,1

Deactivating a stock looks like;
2019-09-16,IBM,0

Is that right?

I tried deactivating a stock with today’s date and it’s still listed in a Screener run. So the change is not recognized. Is that expected? Does that mean we need to update our ETF lists with Sunday’s date?

There’s another method, and that’s to put 2019-09-16,OXY,NA or 2019-09-16,OXY,0 Either of those will overwrite the previous entry for OXY. But leaving it out won’t.

Yes indeed.

Today’s date won’t be recognized until after 11 pm today, when the data reloads.

Thank you Yuval and P123 Staff!

I will be able to use this as is. Not sure about this: see next post (but I assume I am doing something wrong or there is a fix).

The data upload limit will constrain what I can do, I think. See image for data from 2013 till early 2019 and not all stocks in my P123 universe at that.

Can these uploads be concatenated within P123. Other workarounds? Perhaps I can upload (and test) a year at a time if nothing else (have not tested this for a data limit)? Maybe I can removed the worse half (or 3/4) of the data and get under the limit? I will test these ideas.

How many rows is this limit (about).

Maybe you can upload multiple times and they are concatenated? Probably the case. Will try this. If this works I appreciate the opportunity to post as it helped me find my own answer (probably). Will not try this, however, until I figure out the problem in the next post.

Much appreciated.

-Jim


Okay, I have reduced the upload size. Now what am I doing wrong?

There are a lot of tickers and it is getting time-consuming removing them all.

This is just the tickers starting with “A” as I sorted the spreadsheet in order to find (and remove) many tickers already (and resubmitted it). It is getting to be a lot of missing data (after removal) and probably is not useful with that many removals.

Again, I assume I am missing something. Any help or fixes appreciated.

Thanks!

-Jim


Yuval, my “inlists” of historic holdings are spaced 3 months apart. Does the CVS file have to repeat the same holdings list for each day in the 3-month segment?

If so then for daily data, 22 x 3-month periods, and 200 holdings:
Days in a 3-month period = 63
Number of 3-month periods = 22
Number of holdings = 200

Total Number of lines in the input CVS file for 5.5 years of historic data = 63 x 22 x 200 = 277,200. Does this not exceed the upload limitation which Chris found?

Georg, It’s been my experience that the stock factors remain active until deactivated with a zero value. So you shouldn’t have to repeat the quarterly data on a daily basis.

Walter

Walter, that means one has to deactivate the stocks that have been dropped at the end of a 3-month period, and only would require the new holdings to be listed, because the old ones which are retained continue to be active. Is this correct?
Thanks.

I believe so. That should work.

Since my updates will occur weekly, my planned workflow is load active stock selections on Sunday. On the following Saturday, use the same list to deactivate all selections and then on Sunday reload the new - possibly updated - stock list. That way I don’t have to actively manage the deselections.

Yes, that’s a good idea. Deactivate the old list, and activate the new list every 3 months.

Jrinne, where did you get those tickers from? They are not valid today . Perhaps they are point in time tickers? I think the import only looks at the current ticker, which means you need to find the latest ticker (arexq for example) or find the ticker for dead ones (that uses the ^ notation).

We’re thinking of several ways to support old tickers, like

  • Allow you to choose “use point in time tickers”
  • Support CUSIPs
  • Support our own internal numeric id which never changes

Thanks