Supplementing existing DataSets with new stocks from Fidelity screener
Author: superticker
Creation Date: 12/20/2016 2:42 AM
profile picture

superticker

#1
At the end of each trading day, I data mine the Fidelity stock screener for new stocks to add to my existing Wealth-Lab datasets. The process works like this:

1) Grab three different screening results from independent Fidelity classic screener definitions in *.CSV format.

2) Eliminate duplicate screener results from the three CSV files (using Excel).
3) Eliminate duplicate stocks that are already present in the WL datasets (using Excel).
4) Place the remaining, unique, new stocks into a "screening" WL dataset for further analysis to determine which WL strategy they might fit in, if any, using WL screening.

Steps 2 and 3 are now done with Excel with much interactive manipulation. I'm looking to streamline this process so what now takes me 70 minutes can be done in 3 minutes. Has anyone already done this? If so, are you using Excel Macros or did you roll your own C# application? I'm looking for starting material to possibly roll my own. Seems like the .NET framework has already done 92% of the work, but configuring its collections and I/O interfaces are a hassle.
profile picture

Eugene

#2
While I can't comment on 1) as I have no idea how one obtains the results (must be from website or email subscription?), the C# code for steps 2 to 4 could fit about a couple of screens (with a console application in mind). You could even program it in WealthScript but unfortunately, to see the changes to DataSets (like modify/add) you'd have to restart WLP. For a Wealth-Lab addin it's possible to make those changes on-the-fly (that's how the Data Tool works) but things get more complicated here. So for simplicity's sake, your app should be run with WLP closed.

2) Parse the CSV files and get unique results e.g. using LINQ Distinct

3) Using LINQ to XML, parse the existing DataSets and either remove duplicates or use a HashSet that only stores unique items

4) "Serialize" the new DataSet into a new XML file by appending the string from step 3 to a DataSet template
profile picture

superticker

#3
Yes, the *.CSV files generated in 1) come from the classic stock screener on the Fidelity website; it's a powerful screener. Their website prompts you for which program to use for opening the CSV files. I use the classic screener because it allows me to control the weights on each selection criteria the screener employs to compute a ranking score. I only wish the CSV files included those ranking scores.

I think you're right. A console application is the easiest to construct. The Wealth-Lab addin option sounds interesting, but let me get the console application running first.

I was thinking there would be a .NET deserializer that would parse the CSV files into a C#/.NET collection. Some types of sort-on-the-fly collections "automatically" eliminate duplicates. LINQ Distinct might be a good way too if I placed the symbols in a string array instead of a sorting collection that automatically eliminates duplicates. (It's nice .NET does all the tough work for you.)

There are some XML file serializers .NET has for read/writing I need to look at. I was thinking of using them to go from collection to XML file, but perhaps LINQ might also be an option there as well. I'm assuming you're suggesting I use a DataSet "template" from an existing XML dataset. (If there's some other template I should be using, please point me to where to find it.)

If any one else has anymore ideas or starting material, let me know.
profile picture

Eugene

#4
QUOTE:
I was thinking there would be a .NET deserializer that would parse the CSV files into a C#/.NET collection.

There are numerous ways to do it: parse line by line, use TextFieldParser , read with LINQ etc.

QUOTE:
I'm assuming you're suggesting I use a DataSet "template" from an existing XML dataset.

Exactly. You can of course complicate things by serializing it but since you're after renaming the DataSet and modifying its symbols, why not just settle with a simple string Replace?


P.S. On the other hand, if you were to take the Wealth-Lab addin route (like the Data Tool) then you could use native objects like DataSourceManager and not worry about dealing with the XML, files and parsing. Do everything (loop through existing DataSets, modify them (remove duplicates), delete DataSets) without having to go low-level.
profile picture

Cone

#5
Can you create a Fidelity.com WatchList with the Screener results?

If so, you can import that Watchlist into WL Pro
Just 1. Create a New DataSet > 2. Fidelity > 3. Import the Symbols from a Fidelity.com Watchlist

Voila.
profile picture

superticker

#6
QUOTE:
Can you create a Fidelity.com WatchList with the Screener results?
Yes, this works well. I could then call on this addin to eliminate duplicates from that imported watchlist. That's an interesting approach.

Right now the web browser asks me which application to open the CSV file with. I'm assuming it does an OLE or COM operation to the accepting application to complete this handshake. I'm not a Windows programmer (I'm from the real-time embedded system world.), so I'm totally new to Windows handshaking with an OPEN request. But importing a watchlist would skirt this handshaking problem between browser and Wealth-Lab.

There is one problem. If I have 500 screening results on the browser, I don't want to have to place 500 check marks next to all of them to place them into a watchlist. I hope you're not suggesting I write a Grease Monkey extension to have Firefox do that for me automatically. Is there a tricky way using the Fidelity interface to get the web screener to select all the check boxes for export to a watchlist? Perhaps you can get Fidelity to add this feature?

QUOTE:
... if you were to take the Wealth-Lab addin route (like the Data Tool) then you could use native objects like DataSourceManager and not worry about dealing with the XML, files and parsing.
Yes, that's a big plus. But then I would have to debug the .NET operations (remove duplicates) under Wealth-Lab, although since .NET has most of that implemented, it may not be that big of a deal.

The addin needs a GUI. I was kind of hoping to skip that (for now) with the console application.
profile picture

Cone

#7
QUOTE:
Perhaps you can get Fidelity to add this feature?
Not me, but Fidelity customers may be able to! (Strange that a "check all" feature doesn't exist.)

Edit: I haven't used it yet, but FF and Chrome both have add-ins for "Check all".

500 results? That will be a problem with the Watchlist transfter since Fidelity.com Watchlists are limited to 50 symbols. :(
profile picture

superticker

#8
QUOTE:
FF and Chrome both have add-ins for "Check all".
I didn't know that. Interesting.

QUOTE:
500 results? ... Fidelity.com Watchlists are limited to 50 symbols.
Hmmm. So the Fidelity watchlist approach is out. It's back to deserializing/parsing the CSV screener output file. Thanks for pointing that out.

As a signal processing guy, I trade against the "decorrelated line" where the stock time series (DataSeries) is decorrelated with the S&P500. The problem is the screener can't do a decorrelation or a cross correlation, which are fundamental operations in signal processing. So the plan is to pull a large number of stocks out of the Fidelity screener (500), and then turn my WL decorrelate indicator loose on them for further screening.

So this programming project is about moving large numbers of stocks out of the Fidelity screener and into Wealth-Lab for second-tier screening of unknown stocks. I'm just trying to establish the best way to do that.
profile picture

Eugene

#9
QUOTE:
Right now the web browser asks me which application to open the CSV file with.

Choose "Save as" instead to save the file on the disk for further processing.

P.S. Ideally you'd want to request the watchlist from your app but that'd complicate things since you'd have to handle authorization on Fidelity's website through a login form with SSN/PIN and the cookie.
profile picture

superticker

#10
QUOTE:
... browser asks me which application to open the CSV file with.
QUOTE:
Choose "Save as" instead to save the file on the disk ...
Well, Save works too. Firefox just puts it in the Downloads folder when you Save. (BTW, I used the TextFieldParser class to read the CSV file last night, so that's done; that was easy. The rest looks pretty easy too if the XML deserializers work right.)

What's hard is trying to figure out the handshake between Firefox and my console app so it honors the OPEN request sent by Firefox. I'm not sure where to begin there.

QUOTE:
Ideally you'd want to request the watchlist from your app ...
But if a Fidelity watchlist is limited by 50 stocks, that's a problem for me. The one feature a watchlist has is that you can pick and choose which stocks to bring over. But I really want Wealth-Lab's second-tier screening to do that for me using the decorrelated line against an RSI evaluation (or something).

What I would be interested in doing is converting the console app to a Wealth-Lab addin that can still import the CSV file from a Firefox handshake. But let's get it the console app working first since that's simplest to debug.
profile picture

Eugene

#11
QUOTE:
What I would be interested in doing is converting the console app to a Wealth-Lab addin that can still import the CSV file from a Firefox handshake.

That might be possible if you associate your console app with CSV file type instead of Excel. Next, check out this thread:

How do I open a file with my application?
profile picture

superticker

#12
Thanks. Looks like passing the CSV filename to the console app is also easy. I'll definitely try it.

Perhaps you already know, but typically browsers (like Firefox) let you assign specific file associations (helper applications) for that browser. That way the file association is local to the browser and not global to the OS. It's a nice browser feature.

The next question is, if the CSV reading operation is part of a Wealth-Lab addin, how does one get Wealth-Lab to pass that request on to a particular addin? Is there some place that tells Wealth-Lab if the file type is CSV, that pathname should be passed to the fidScreenerImport addin? How's is that directed?

By the way, the Fidelity web-based screener always calls the file "screener_results.csv".
profile picture

Eugene

#13
QUOTE:
Perhaps you already know, but typically browsers (like Firefox) let you assign specific file associations (helper applications) for that browser.

You're absolutely right, FF has that "Applications" section in its Options. It's funny that in my soon 21 years of browsing I almost never had the need to utilize this feature. :)

QUOTE:
The next question is, if the CSV reading operation is part of a Wealth-Lab addin, how does one get Wealth-Lab to pass that request on to a particular addin?

Sorry, no experience with this. What I was suggesting about file associations concerned your console app.
profile picture

superticker

#14
I got my console application for processing Fidelity screener results, screener2wl, running nicely. It's a great time saver since it automatically removes duplicates from screener results WL datasets already know about. But there's a problem....

I can't get Firefox to pass command-line arguments to my helper app on the Fidelity screener download. Any ideas? Does Chrome or Internet Explorer have this same problem? (Perhaps there's a security concern allowing any browser to invoke the command shell from a web action. I never considered that.) But a console app that can't receive command-line arguments isn't much good. :(

Yes, I can use shortcuts to pass arguments to my screener2wl app, but Firefox won't invoke helper shortcuts or batch files directly; only executables. If I wanted to use a shortcut or batch file, I would have to save the Fidelity screener download to disk first, then invoke the shortcut to process it. If I'm going to do that, then I just as well convert the console app to a WL add-in, which can read the saved screener CSV file.

There's another weird problem. The screener2wl app creates the WL XML symbol datasets just fine, but you have to restart WL before it will see them. Is there a way to get the WL Data Manager to refresh its dataset view without restarting WL?
profile picture

Eugene

#15
QUOTE:
There's another weird problem. The screener2wl app creates the WL XML symbol datasets just fine, but you have to restart WL before it will see them. Is there a way to get the WL Data Manager to refresh its dataset view without restarting WL?

A weird problem for one is by design for others. ;) Yes, there is a way. That's how the Data Tool is able to do all sorts of operations on DataSets so that the changes are reflected instantly. Unfortunately, it uses an undocumented interface for which no usage example is provided.
profile picture

superticker

#16
QUOTE:
Is there a way to get the WL Data Manager to refresh its dataset view without restarting WL?
QUOTE:
That's how the Data Tool is able to do all sorts of operations on DataSets so that the changes are reflected instantly. Unfortunately, it uses an undocumented interface for which no usage example is provided.
Is the code for the Data Tool available? Or can you document its API? Or do you want to develop a screener2wl add-in from my code?

I can sign a nondisclosure form if that's an issue. If so, I can generate a service ticket.

One other thing. I haven't done a GUI in Windows before, and an add-in will require a GUI. So the add-in part (GUI) will need to be done later. But if I can get the console app to use part of the Data Manager API so I don't have to restart WL, that would be a good intermediate step.

---
I'm gathering you don't know anything about web browsers and getting them to pass command-line arguments to helper apps (like screener2wl) on a download. I wonder who would know about such things?
profile picture

Eugene

#17
QUOTE:
Is the code for the Data Tool available? Or can you document its API?

Sorry but the Data Tool is our (MS123) intellectual property, closed source. We're not authorized to document a Fidelity API like the Data Manager. Like I figured it out myself, you could also hack your way through this API. I will only give you one hint: inherit your GUI class from IItemTracker<DataSource>. The rest is up to you.


QUOTE:
I'm gathering you don't know anything about web browsers and getting them to pass command-line arguments to helper apps (like screener2wl) on a download. I wonder who would know about such things?

I admit that I never used a web browser to pass command-line arguments, preferring less awkward techniques to imitate the web browser with the various controls and hadling accompanying tasks ranging from cookie management to evaluating Javascript math expressions.

From post #6 it sounds like you must use the current approach for web scraping the Fidelity website. If that works for you then fine but have you looked at quite powerful engines like HtmlAgilityPack, ScrapySharp, WebDriver etc.?
profile picture

superticker

#18
QUOTE:
Sorry but the Data Tool is our (MS123) intellectual property, closed source.
As I said, I would be happy to sign a nondisclosure agreement. We do that all the time in the engineering consulting business; standard practice. And someone at MS123 could should develop this add-in instead. Or we can work on it as a shared effort. I can certainly share what I've written so far. I'm surprised someone hasn't already done a Fidelity screener interface for WL by now.

But for me to solely work on an add-in with a Windows GUI is down the road some. I got other projects. However, I can work on the low hanging fruit right away if it's not too much hassle. I don't have too much experience with the VS debugger, so hacking the API isn't a priority.

QUOTE:
for web scraping the Fidelity website.... have you looked at quite powerful engines like HtmlAgilityPack, ScrapySharp, WebDriver etc.?
Personally, I haven't done much web scraping. So where are we going with this? Are we talking about writing an app to scrap the Fidelity screener? Is the purpose of this approach to work around Firefox's limitation of not passing command-line arguments to helper apps?

Will these scraping packages (you mentioned) handle the Fidelity authentication cookies for me? Can they use Firefox's established authentication cookies for getting access? I'm trying to figure out how deep the rabbit hole goes here to determine if I'm interested ...
profile picture

Eugene

#19
These scraping packages will help handle like the auth cookies and other tasks like filling forms and parsing the output. It takes some time to learn the lingo but the end result is more straightforward code which isn't dependent on intermediaries like the browser and console app.

However, after some thinking I'd like to suggest a different, easier solution for you to consider. There's a great Firefox plugin called iMacros that can handle form filling, web scraping and CSV export. Not only it can record macros, it could be programmed with its own scripting language too (it comes with a lot of samples). In your console app, use something like FileSystemWatcher to monitor a directory for changes and new CSV files. Semi-automation in the nick of time.
profile picture

superticker

#20
QUOTE:
These scraping packages will help handle like the auth cookies and other tasks like filling forms and parsing the output.
The .NET framework string and XMLdocument functions already provide very good parsing. But handling the https encryption and authentication cookies concerns me. If the scraping packages can do that, then I'm interesting in looking into them deeper.

Yes, it would be nice to cut out the intermediaries like the browser; however, the browser is providing https encryption and cookies handling today, which is needed. So the scraping packages have to provide at least this too before I would give up the browser.

I also thought about writing a Firefox extension for doing some of the scraping (or using a Grease Monkey script). I'll definitely take a look at the iMacros extension you mentioned--sounds interesting. It may be the easiest next step. Thanks for all the leads!

-----

The current console app w/o parameter passing works well for me if I hard code all my WL dataset names into it. I have one set of WL datasets for stocks and another for ETFs. The latter is processed with a Windows shortcut parameter to let the screener2wl app know to use the ETF datasets instead of the stocks.

I could share my code, but since my datasets are hard coded into it now, there's little point. Of course, if it gets a GUI, things could be different, but that's a low priority. If someone at MS123 wants to collaborate with me to turn screener2wl into a WL add-in, let me know.

Your WL trading is only as good as your dataset symbols, so I check the screener at the end of each day to add symbols to WL for second tier screening. The screener2wl app will just make that much quicker and easier--big win.