Back in October I wrote the first part of this 2 part series that talks about and outlines how you can create an OOTB Stock Information solution in SharePoint Online that focused on the overall solution design and the JSON needed to format the view. This post will talk about how you can leverage Power Automate, with a premium license, to update the list with the current stock information.
What you will need before you begin
Before we dive into Power Automate there are a few things you will need:
- A SharePoint List setup and configured to place the stock information
- A premium license for Power Automate (for the HTTP action)
- A stock API endpoint for use to gather the information from
Power Automate Flow
We are first going to start off by creating an Scheduled cloud flow called Get Stock Information and set the flow to run every week M-F. Don’t worry about getting the recurrence logic just right here as we will change it once in the flow.
The next step in building the flow will be to edit the **Recurrence trigger because we only need this flow to run when the Stock Market is open. Additionally, we will only be updating the stock information every 15 minutes to limit the amount of calls that are made to our stock API.
Your Recurrence trigger should resemble the screenshot above now. Our next step will be to add an HTTP action to call the our stock API’s REST endpoint. I am using IEX Cloud’s stock API to fetch the current stock information but based on the stock API you chose the configuration of this action may vary.
I chose to use IEX Cloud’s stock API because of how easy it is to sign up and set up for development purposes. It also has the added benefit of being extremely easy to call the REST endpoint and configure the HTTP action. Here you will notice that we are calling the quote endpoint for the stock MSFT. Then we are simply passing our token as a query parameter that will be added to the end of the URL.
The next action we are going to put into our Flow is the Parse JSON action. This will allow us to parse the output from the HTTP action and easily reference it later on in our Flow. The content will be the Body output from the HTTP action and the Schema can be derived from the stock API’s documentation.
The final action we will add to our Flow is the Update item action for SharePoint. We will configure the solution to point to the site collection where we have built and configured our Stock Information list. Since we are using the Update item action we will also need to include the Id of the item we would like to update, in our case 1. Additionally, we are limiting the columns by our Stock Information view but this is an optional configuration.
You will start to see our Parse JSON action in use as we update the columns on our item. The following will breakdown what information from the stock API is mapped to which columns and you will notice that we are using some expressions to format the data that is coming back as well.
|Title||companyName||We are pulling in the company name from the stock API and setting it to the Title. This allows our design to update if the company’s name changes.|
|Current Price||formatNumber(body(‘Parse_JSON’)?[‘latestPrice’],’#.00′)||We are using the formatNumber function to ensure that the latest price is always converted to 2 decimal places.|
|Change||change||We are simply pulling the change in stock price from the Parse JSON action.|
|Last Updated||Updated: convertFromUtc(addToTime(‘1970-01-01T00:00:00’,div(body(‘Parse_JSON’)?[‘latestUpdate’],1000),’Second’,’yyyy-MM-ddTHH:mm:ssZ’),’Eastern Standard Time’,’g’)||The stock API gives the latestUpdate time back is an epoch timestamp given in milliseconds so we need to figure out the current date time and then convert it from UTC to our current time zone which is Eastern Standard Time.|
We are now ready to save our Flow and run it to ensure that everything works properly and as expected. If all goes well you should expect to see green check marks across all actions and your stock information should show the current information.
Before you go and implement this as a production ready solution you may want to look into adding some error handling capabilities and notifications into the Flow to ensure that the proper support staff are notified if the Flow begins to fail so they can investigate it. You will also want to ensure that the stock API you chose can handle the amount of calls if you decide to increase the frequency with which the Flow runs.