Hi,

I am trying to automate the stock screening I do on Fidelity's web site using Wealth Lab Pro. In Fidelity's stock screener, they have data from S&P Global Market Intelligence (Financial Health, Valuation, Quality, and Growth Stability). The results are numbers between 1 - 100 (for each of the 4 criteria above).

Is there any way to 'pull' this data from Fidelity, so I can incorporate this as part of my screening routine? I appreciate your help. Thanks,

Hi,

Anything is possible, as Robert (Cone) likes to say, the question is what it takes. Unlike certain sections of their website which can be used without logging in, pulling the stock screening results from Fidelity requires valid credentials. Unfortunately, I do not have a username/password.

QUOTE:
In Fidelity's stock screener, they have data from S&P Global Market Intelligence ... Is there any way to 'pull' this data from Fidelity,...

When you're logged in, there's a Download selection on the Fidelity stock screener so you can create a screener_results.xls binary file that Excel can open. You can certainly analyze it there with Excel or R. R might be easier than Excel if you already have that installed on your system. You can also open it with ExcelDataReader which will place it in a System.Data.DataTable datatype your personal C# program can access.

Where are you going with this? Do you want to create a custom "local database" with this extra fundamental data for processing on Wealth Lab? If you're a programmer and are pretty good with disk I/O, SQLlite, or Linq, I don't see why you couldn't. Sound like a personal programming project to me. Might take several weekends. Could be fun.

Understand, WL's own fundamental structures (for the Fidelity provider) know nothing about this data, so you need to create your own "struct's" to contain this novel data. If you're a C++/C# programmer, you know what I'm talking about.

There are .NET libraries making the task of parsing Excel spreadsheets (both binary and XML formats) rather easy and without effort. Some WL data providers and in-house tools in our own backend are powered by them. So OP doesn't need Excel nor R - it all (including the disk I/O and DB) can be performed by a WealthScript Strategy (or a WL addon) on-the-fly. At least that's how I'd accomplish this. But like I said, the real challenge is the automation of Fidelity login when credentials aren't available. ;)

I like the idea of creating a special WL add-on, but only if WL is able to share its credentials with that add-on to get server access.

Frankly, I don't have interest in this project (as with anything that requires Fidelity website credentials) but your Federated Login requirement would make it much more complicated (if possible).

I do have a Fidelity account / login, and am basically always connected to Fidelity in WL Pro.

Currently, what I am doing is:
a) Do my search in Fidelity & create a watchlist;
b) Create a dataset using that watchlist
c) Run my analysis on that dataset

I am looking to see if there is a way to automate this, so I can eliminate steps a & b above. I have all other data except for the 'S&P Global Market Intelligence' parameters. If I can find a way to get these from Fidelity into WL Pro, then I can automate my search. Thanks,

QUOTE:
if there is a way to automate this, so I can eliminate steps a & b above.

Of course your can automate this if your a programmer (Are you?) and have a couple weekends. Two suggestions have already been made:

1) Post# 3 suggests using ExcelDataReader to place the screener data in a System.Data.DataTable datatype, then move it out into your own custom C# struct where you can search that with Linq queries or something.

CODE:
Please log in to see this code.

So you're creating a List of Records (i.e., a database), which you are now going to search with Linq queries or List methods. In practice, you may want to use a Dictionary<string,Record> datatype over List<Record> where "string" is for the stock symbol (searchKey) you would be searching against. But that's another discussion.

I do Method 1 with a stand alone program my browser (which is logged into Fidelity) calls automatically. But you could just have your browser save the search_results.xls file and have WL read it instead with the constructor of your WL screener.

2) Post# 4 suggests employing Microsoft's JET engine to do the same thing assuming you have MS Office installed. If you're good with SQL queries (Are you?), that might be a good option. Basically, you employ OLE DB queries to have the JET engine read the spreadsheet data into its database where you can then make SQL queries against it. Look at the solution https://stackoverflow.com/questions/12996234/optimal-way-to-read-an-excel-file-xls-xlsx/12996544 that's employing the OleDbConnection approach for an example.

Method 1 is probably faster, but requires more low-level coding since you need to define a custom datatype (i.e. schema) with a C# struct. With Method 2, you skip that because you just put the entire spreadsheet into the JET database (MS Office) and employ SQL to get at it.

If you're not a programmer, or you don't have a couple weekends, then neither method is good. But if you are a programmer, then you can do this. You first need to decide which method, either 1 or 2, you want to use. The stackoverflow link (above) kind of covers both approaches, so pick one after studying those choices.

Except during development, I wouldn't expose your strategy code to all this database implementation stuff. Rather, I would encapsulate all this implementation stuff into its own fundamentals class and save it into a DLL library with Visual Studio. My strategies have about 350 lines of code, but they call my own DLLs, which hold the bulk of my WL utility coding (which doesn't change).

I can answer questions about ExcelDataReader, but I don't have experience with OLE, the JET engine, or SQL myself. Questions about either are probably better addressed on StackOverflow, not this forum. Happy coding.

QUOTE:
2) Post# 4 suggests employing Microsoft's JET engine to do the same thing assuming you have MS Office installed.

No, my post does not suggest delving into this stuff. We posted at the same time and I also had the lightweight and easy ExcelDataReader on my mind. We're on the same page here.

QUOTE:
I do Method 1 with a stand-lone program my browser (which is logged into Fidelity) calls automatically.

I've written hundreds of parsers which mimic the browser but in cases like this where credentials aren't available to the developer, Method 1 sounds fine. No idea about asagar00's skills but here are a couple more ways to accomplish this with medium to low effort:

1. Medium effort: use Selenium WebDriver or AutoIT to control the browser i.e. automate clicking the Download XLS button

http://learn-automation.com/install-selenium-webdriver-with-c/
https://www.quora.com/How-do-I-simulate-a-mouse-click-using-Autoit-without-coordinates?share=1

2. Easy: use a browser addin (e.g. iMacros for Firefox) to replay repetitive tasks like filling out forms, downloading files and extracting data:

https://addons.mozilla.org/en-US/firefox/addon/imacros-for-firefox/

QUOTE:
in cases like this where credentials aren't available to the developer, Method 1 sounds fine.

I totally agree. Use the browser's credentials to Download the screener_results.xls file. Keep it simple.

1) At that point you can have the browser automatically call a stand alone program to preprocess it for WL, which is what I do. (I just manually click the Download tag rather than using AutoIT. Doing it with AutoIT is too fancy for me.)

2) or you can have the browser simply save the screener_results.xls file and have a WL post-processing class pick it up in the strategy's constructor.

The problem with this second method is if the results file is corrupted, it can crash the strategy (or WL) if good exception handling isn't employed. I don't worry about exception handling with the first method. I just let the stand alone program crash. But with the first method, the stand alone program needs to write an intermediate, terse, sanitize version of the original file (I employ an XML format) the strategy can safely input.

As I said, it's going to take more than one weekend to get this code working. The devil is in the details.

---
asagar00, what's your programming experience? Have you used the Visual Studio IDE? Can you write a command line stand alone program with it? Are you good with creating an encapsulating C# post-processing class to do all this I/O stuff? Are you willing to spend a couple weekends coding and debugging this? Which approach looks interesting to you?

I think you can use a Dictionary datatype to access the data without Linq queries once you have it loaded into the Dictionary database from ExcelDataReader's System.Data.DataTable. Something like this for a query "might" work:

CODE:
Please log in to see this code.

Take a look at the Dictionary datatype on .NET and tell us if this looks like something you're comfortable with. Study some of their examples. https://docs.microsoft.com/en-us/dotnet/api/system.collections.generic.dictionary-2?view=netcore-3.1

Here's a ScreenerData class you can compile in Visual Studio. Create a Local.Components DLL library (VS project), then add the ScreenerData.cs file (reproduced in a code section below) as a class to that project.

To use it, declare an instance of ScreenerData at the top of MyStrategy. Be sure you have already downloaded screener_results.xls from your browser's Fidelity screener page because this class will be looking for that file when the strategy starts up. (It will die if the file isn't there.)

CODE:
Please log in to see this code.

Now to use it, I "think" you can address the database from within your strategy as shown below, but my syntax are probably slightly wrong. You'll need to research that. Perhaps someone can correct my code below, but it's something like this: (Check the .NET Dictionary<TKey,TValue> docs)

CODE:
Please log in to see this code.

In practice, you'll want to add a GetRecord & Get member function to this class so the above line becomes:

CODE:
Please log in to see this code.

Now the actual code below is not completely done. You--or someone--need(s) to replace ...

CODE:
Please log in to see this code.

with the right assignments from the sheet1.Rows[i][indxOfSymbol]. array. That part is left up to you; I know nothing about that. You might want to use the VS Debugger to take a look inside that sheet1 structure to see where everything is when you're doing that replacement. Here's your ScreenerData.cs class starting material.

CODE:
Please log in to see this code.

You'll need to nuGet (from your Local.Components Visual Studio project) both the ExcelDataReader.3.4.2 package and the ExcelDataReader.DataSet.3.4.2 package. You'll need to setup project references for both packages, but you only need one using statement for ExcelDataReader. Both of these ExcelDataReader packages need to be copied into your Wealth-Lab install folder so WL can find them when using this ScreenerData class. Of course, your own Local.Components.dll file you created with Visual Studio needs to be in there too.

In practice, you'll want to add some member functions (i.e. Methods & Properties) to this class so your strategy can use it with less coding.

@ Eugene
If you want to rewrite this code so it follows your suggestions in Post# 4, I would be interested in seeing that approach.

In practice, the schema Record definition should be placed in the strategy itself and passed as a ScreenerData constructor parameter. That way all WL users can employ it with their own schema definition. Then it could be included in Community.Components for everyone. But the above draft is good enough for development work.

Happy coding.