Factor and data series importing

[Jrinne]

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

Marco,

Thank you!

I would appreciate support for old tickers if this is a reasonable priority.

It is a great feature that I would like to use to its fullest!!!

In the meantime, I am glad to know the cause for this—so I can stop looking (naively) for date-format issues with Mac/Excel;-)

Do you know of a web site or reference for old → new ticker names? I could conceivably work through the Excel spreadsheet alphabetically (changing the tickers) now that I know the cause.

Very much appreciated!!!

-Jim

[jmh]

Do you know of a web site or reference for old → new ticker names? I could conceivably work through the Excel spreadsheet alphabetically (changing the tickers) now that I know the cause.

I had to do that for a bunch of inlist() in the past.

I recommend using the P123 advanced stock search. In particular, use the company (old or new) name rather than the ticker is very helpful.
My experience is that it will get you 90-95% of what you need.
For the remaining tickers, I had to use google and try to read through the history of possibly matching companies

Good luck

Jerome

[Jrinne]

Thank you Jerome! -Jim

[yuvaltaylor]

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, …

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.

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.

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?

No, there’s no need to repeat the data for each day. The data “sticks” until there’s a new date for that ticker.

[Jrinne]

Thanks Yuval and P123 staff,

Everyone put a lot of work into this and great explanations Yuval. Let me understand if I can do what I want, please.

Suppose I would , somehow, select 25 stocks each week that I expect to be (on average) the best performers using whatever method. Maybe they come from a newsletter.

I load those stocks with a simple RankPos from the newsletter that ranks the stocks best to worse eg,

2013-08-05, AAPL, 1

2013-08-05, IBM, 2 ….

…2013-08-05, FB, 25 Using “lower values” in the rank

Next week (2013-08-12) I load 25 new stocks but AAPL, IBM and FB are not in the list of the 25 stocks expected to be best performers this week.

If I have a buy rule: RankPos <= 25, sell RankPos > 25 will AAPL, IBM and FB “stick?” Will the sim (or port) continue to hold AAPL, FB and IBM as well as the 25 new stocks that I loaded for that week?

Would I have to make NA (or 100) the stocks that are no longer present (as the top 25) the next week? Seems like I might have to upload the entire universe (with current factors) every week for this to work flawlessly.

Seems like I might need a satellite image for every stock in the universe to continue your example, Yuval. Or put NA for every stock that does not have a satellite image for that week in the universe—remembering which stocks had factors (satellite images) last week. Perhaps, workable as part of a weekly routine for a port. A lot of work for a spreadsheet to use in a sim. Not simple data wrangling for Python either (for me).

Not a problem if this is not a perfect solution for everyone. Just trying to make it work for me (if it can work).

Maybe consider a “no stick” option of some sort if I have this right (e.g., 1 defaults to NA next rebalance).

Thank you in advance.

-Jim

[geov]

Thanks Yuval and P123 staff, this is a great addition to P123.

It was a pain in the neck to construct the CSV file from the historic data of USMV which I have downloaded every 3 months for a number of years now. Problem is that only currently recognized ticker symbols are accepted. This required a lot of editing to get rid of the errors. But in the end it worked well.

Below you can see the performance from 10/1/2012 to 12/31/2019 with all historic positions equal weight, which is virtually identical to benchmark USMV, showing an annualized return of 13.8%.

The second attachment is a screen with the (Greenblatt) highest ranked 25 stocks, rebalanced weekly. Annualized return is 19.2%. That is a huge improvement over the performance of USMV, and is similar to the performance of the live 12-position model which we publish weekly at iMarketSignals.

Also this proves conclusively what I proposed more than 5 years ago. Just “piggy-back” on a good ETF and select the highest ranked stocks from it. It is difficult to under-perform the ETF with this strategy.

---

---

[WalterW]

That’s great work, Georg! I may have to try https://masterdatareports.com soon.

[Chipper6]

Georg, was that the only test you tried? What were the results for the other tests?

[geov]

I have only got the holdings from 10/1/2012 of USMV in the CSV input file for Stock Factor $$USMV.

First I ran the screen.
Second I ran a sim to verify the screen.

You can never underperform USMV, if there are at least 3 positions you hold in the sim.

Only one buy rule: $$USMV=1
Only one sell rule: rank<95 & NoDays>12
Universe is All Fundamentals
Ranking= Greenblatt
Slippage = variable
Reconst. and Rebal.= weekly
Max Position Drift= 10

Below are screenshots for 5 and 50 positions. Note the low turnover.

What else should I try?

---

---

[InmanRoshi]

I’m trying to wrap my head around data series. So for example, I could export the portfolio performance of a live P123 portfolio with just the daily dates and amount of the portfolio, and then import it into a time series and construct buy/sell timing rules based on 50 and 200 SMA trends of the portfolio? I do a lot of this on my own using excel, but it would be nice to be able to run P123 basktests with it.

[WalterW]

I’m trying to wrap my head around data series. So for example, I could export the portfolio performance of a live P123 portfolio with just the daily dates and amount of the portfolio, and then import it into a time series and construct buy/sell timing rules based on 50 and 200 SMA trends of the portfolio?

I haven’t tried that yet. But I have imported portfolio transactions and used that to generate a universe for a custom series. With that done, it’s easy to analyze the portfolio for things like median PE, median volume, avgrec, etc.

Walter

[marco]

George why do you have turnover=100% 5 times ? Seems wrong

[geov]

Marco,
As per Walter’s suggestion, I make all the holdings active from a particular date and then deactivate all of them before I activate the next set of holdings. That way I don’t have to actively manage the deselections. That can cause sometimes a 100% turnover.
I am tracking USMV holdings quarterly. So turnover spikes can occur at the end of every 3-month period. That would happen if the first trading day of the week coincides with the the date when the change happens, I think, but not sure.

For example the holdings as of 6/30/2018 are held until 9/29/2018. Therefore, input is:
2018/06/30 AAPL 1 (active)
2018/06/30 ABT 1
etc.
2018/09/29 AAPL 0 (deactivate)
2018/09/29 ABT 0
etc.

Then for the new list which becomes active from 9/30/2018
2018/09/30 AAPL 1 (active)
2018/09/30 ABT 1
etc

[geov]

Marco, I just checked. It has to do with the date specification.

There was a turnover spike on 1/3/2018 when deactivating on 12/30/2018 and activating new list on 12/31/2018.
1/2/2018 104.7359695 104.9631882 0 189
1/3/2018 104.9413071 105.1820297 97 5
1/4/2018 106.3816071 105.5998077 0 5
1/5/2018 107.0279007 106.1369629 0 5
1/8/2018 106.9877014 106.3160172 0 5
1/9/2018 106.8179245 106.3756943 0 188
1/10/2018 106.2072906 105.8584366 0 188

The spike disappears when deactivating on 12/29/2018 and activating new list on 12/30/2018.
1/2/2018 104.7359695 104.9631882 0 189
1/3/2018 105.0679855 105.1820297 12 188
1/4/2018 105.2545166 105.5998077 0 188
1/5/2018 105.931282 106.1369629 0 188
1/8/2018 106.1905594 106.3160172 0 188
1/9/2018 106.1460495 106.3756943 0 188
1/10/2018 105.5392532 105.8584366 0 188

[geov]

Here is the revised All Holdings equal weight USMV screen with turnover spikes removed.
100% TURNOVER IS NOT PERMISSIBLE. It does affect the performance. So one has to watch out for this.

Turnover occurs at end of 3-month periods and also when some of the holdings go extinct.

---

[WalterW]

Georg, How did you handle the turnover? Did you specify the deactivations manually? Something else?

How does the 25 stock sim look now?

[geov]

As I said before: “it’s a pain in the neck”.
I did not specify the deactivation manually. I specified the deactivation on the second last day of March, June, Sept, and Dec.
And then I specified the activation on the last day of March, June, Sept, and Dec.

That resulted in 5 x 100% turnover situations, which Marco mentioned.

When this occurred, I specified the deactivation on the third last day, and then I specified the activation on the second last day. That removed the turnover spikes.

Screen of Top 25 is below - not much different from before.

---

[WalterW]

Georg, Do you set ‘Force Positions into Universe’ true? Just curious.

[geov]

Georg, Do you set ‘Force Positions into Universe’ true? Just curious.

The data series for $$USMV are the historic stock holdings of ETF USMV. A stock is held from a particular starting date to an end date as specified in the series. If a stock disappears for some reason or other then the end date is the disappearance date of the stock, which would coincide with its removal from the All Fundamentals universe.

Forcing a stock into the universe would not have any effect on its end date in the data series, because in a simulation with historic holdings stocks are not selected from the universe according some rules. Only relevant are the “in” and “out” dates in the data series.

[InmanRoshi]

So for example, I could export the portfolio performance of a live P123 portfolio with just the daily dates and amount of the portfolio, and then import it into a time series and construct buy/sell timing rules based on 50 and 200 SMA trends of the portfolio?

FWIW, this works. It’s a nice little stop loss measure to protect yourself if you’re afraid of you ranking suddenly going out of favor and gets back into the market when your ranking system is back in favor. I haven’t had much time to play around with it too much. Like most timing strategies it whipsaw you and effect your total alpha, but it’s a nice “sleep at night” tactic.

For a vanilla example, create a simple simulation with maximum positions with a buy rule of Rank > 90 & sell when Rank < 90 on the longest time period your membership allows. Covert it to a live portfolio. Export the return results to CSV keeping only the Date and Total Equity columns. Upload it into a data series names “Rank 90”.

Create a custom function Rank90: eval(SMA(50,0, GetSeries(“Rank 90”)) > SMA(200,0, GetSeries(“Rank 90”)),0,1). Put $Rank90 = 0 in your buy role to make sure your Rankings Top 10th Percentile 50 SMA is above it’s 200 day average. Sell $Rank90=1 when it’s below.

This functionality is a big step towards making more market neutral strategies. You can imagine how you can nest Eval statements against trend of several different ranking systems like this to swap in and out of portfolios using those rankings with the Portfolio() function.