Updated: Feb 9, 2021
Everyone and their cat is a trader today. I thought it'd be interesting to see how a market noob like myself can benefit from receiving a daily securities rank recommendations using a service like Morningstar or Zacks and how Microsoft Azure platform tools can enable me to orchestrate the process with the minimum of code.
Let's imagine that I track the stock tickers I'm interesting in in an Excel online file stored in OneDrive:
The end goal of the project is to set up a process which will send me an email each morning, with the nicely formatted table containing securities I track and the current ranking for each:
To build this, I will create an Azure Function App which will receive a list of tickers and will use a securities ranking service of my choice to rate each security and will finally return a collection of symbols, with a rank attached to each, along with some additional info.
The Function App will be used by an Azure Logic App which will be responsible for getting my ticker list from the Excel file on OneDrive and for formatting and emailing out the response supplied by the function app.
Why not put all the steps in the single Logic App? There are at least too good reasons for that. The main one is the separation of concerns. I might have several sources for the tickers to track; or I might retrieve my portfolio directly from a brokerage using their API (maybe Azure already has a connector to it!). This logic would fall nicely within my Logic App, and Function App would be responsible strictly for the rating of tickers, regardless of where they came from. If I would choose to productize my work, the Function App would be an ideal container for my IP.
To build a Function App, I will use an Azure Function template in Visual Studio which creates all necessary scaffolding for a new .NET Core project for HTML trigger Azure Function.
The first implementation step is to model the response from the stock rating service I'll use. To do this, I've modeled the call to the service and captured the JSON response. I've then used JSON to C# converter to create a set of classes and added them to Models folder in code. This would allow me to easily deserialize the service's response.
The service returns a lot more data than I'm interested in - I will create a simplified object QuoteSimple to flatten the response. This class will also have a couple of calculated properties, to supply the total value and return percentage for each symbol.
In the screenshot below, the top split shows QuoteSimple class, and the bottom split shows some of the autogenerated classes.
The next class in Models folder is SearchParams which maps to the input data for the function - a List of objects mapped to the columns in my Excel document, and a couple of extra parameters. An additional parameter IgnoreRaitings allows me to filter out response based on stock's rating. For example, if the ranks range between 1/best and 5/worst, setting this parameter to 3 will exclude all symbols with middle/Hold ranks, and returns only symbols with Buy or Sell signals.
The main HttpTrigger function of the app deserializes the body of the request into a SearchParams object; calls GetTickets method (described below) which returns a List of QuoteSimple objects; sorts this list by the stock rank; and finally serializes and returns the response.
The GetTickers method iterates over tickers collection in SearchParams, and for each symbol calls the rating service using GetURI method; deserializes the response in Quote object (containing classes which were auto-generated from service's JSON response); converts Quote to QuoteSimple and adds new QuoteSimple to a returned List<QuoteSimple>.
The GetTickers method uses GetURI (below) to execute a Get request. It can be easily modified to execute a Post instead.
After making sure the code compiles, the easiest way to test it is in something like Postman. Function App runs on-prem as a local server:
To test it, create a POST request in Postman and run it. Request for 2 symbols below returns one response for MSFT. The function app filtered out response for GME because of the Hold rating excluded from the results.
Next step is to publish the Function App to Azure. After it is published, navigate to App Services in your Azure subscription, and open Code+Test view for your new function. Supply the same test body request used for the Postman test and make sure the function produces the same output.
With the Function App completely finished, the next step is to build the Logic App.
The full flow is displayed below:
The Logic App will run on scheduler (once per day) and get the list of tickers I'm tracking from Excel Online in the first step.
Next it will create the the request body by combining the response from List Rows in Excel action, and the values of Ignore Ratings List and Max Response Count parameters.
It will then call TickerRecommendations Azure Function from my new RateService app service and convert the Azure Function's response to a JSON array.
Finally it will use Create HTML Table action to convert the response to a simple auto-formatted HTML table and send out an email with the daily ratings.
The process was easy to implement and can be readily extended by plugging in brokerage API to get actual portfolio instead of the sample data, or by adding custom intelligence to the ratings retrieved by the Function App.