Millions of users in enterprises today start their day with Microsoft Teams, live their day in Teams and wrap their day with Teams! Teams is where people come together to plan, collaborate, share, discuss, decide and take action on things that matter to their businesses and productivity. Teams as the collaboration hub also plays a central role in enterprises today among strategy and planning group members who keep a close eye on all facets of the business including competition.
Recalling what Peter Thiel says “All failed companies are the same: They failed to escape competition.” - in today’s post let’s explore how Microsoft Teams and Power Automate (erstwhile Microsoft Flow) when put together allows strategy teams in an organization keep a close eye on competition’s stock prices – a leading metric of how Wall Street perceives their progress.
First Things First
Imagine the strategy team in Contoso (our favorite imaginary company) is collaborating on Microsoft Teams using their private workspace as follows. Among other things, Competitive Info is a channel where they keep a close watch on their named competitors, track compete news, discuss impact, market perception etc.
Strategy team often checks ups and downs in their competitors’ stock prices. Someone from the team always takes turns in querying stock prices from public sources such as MSN Money or Yahoo! Finance and preparing a report for the rest of the team to consume. One of the strategy team members (that’s me!) wants to automate this repetitive task and imagine a world where periodic tracking of compete stock prices happen, essentially getting notifications about stocks once every 12 hours.
I intend to use Power Automate flow inside Microsoft Teams for the same.
Identifying the basic ingredients
Contoso team identifies the following as pieces of this automation puzzle:
Building the Power Automate flow
The Power Automate flow looks like this:
Each step in the creation of the flow used for this is explained below:
Using the ‘Apply to Each’ control action, for each row in the table represented by ‘Current item’, the following steps are performed:
Parse JSON: Here the row item picked up as the dynamic parameter ‘value’ is parsed as a JSON output so that we can read stock symbols from the Excel file.
You may be wondering how to fill the Schema for the JSON output in this step. Let me share the trick for the same. Power Automate supports generating the schema from a sample output using ‘Generate from sample’ action. But how do you know the JSON output for an Excel file’s rows?
I got that by simply running the flow until step 3 i.e. ’List rows present in a table’ through the ‘Test’ action and seeing the output of the flow run. The Flow Runs Page will show the last run instance of the flow as follows:
Clicking on the last run flow will allow you to step into and see outputs as follows:
Under Outputs, the Body section contains how rows read from Excel are represented in JSON format.
From this output, you can pick up one of the ‘value’ items that can be passed to the ‘Generate from sample’ dialog in the ‘Parse JSON’ step.
This will spit out the schema for parsing JSON and extract relevant values for use in the next step.
HTTP GET call: Next I add the HTTP action to invoke a REST API for getting the latest stock price of the symbol.
The URI field is the most important here. Yahoo! Finance’s stock price URL is of the form “https://query1.finance.yahoo.com/v7/finance/chart/<INSERT STOCK SYMBOL HERE>?&interval=1m”. Hence, using the ‘Add dynamic content’ feature, I can input the parsed ‘Stocks’ string from the previous step to construct the right HTTP GET URL for each stock symbol.
Parse JSON: Now, the response from the HTTP GET step needs to be parsed to extract stock price metrics for the given stock symbol. Body field from the previous step acts as the ‘Content’ input here. Similar to the previous step, the Schema is generated using the ‘Generate from sample’ option. You can use the JSON response you obtain when executing the Yahoo! Finance URL in your browser for any stock symbol. For eg: when you visit this URL: https://query1.finance.yahoo.com/v7/finance/chart/MSFT?&interval=1m in your browser, you’ll get the JSON response to this GET call. Use this entire output to generate the schema.
Append to string variable: Now, it’s time to construct what get’s posted for end users in the Teams channel as a conversation. From the previous step, I now have all pieces of information – it’s just a matter to compose the message. Using the ‘Append to string variable’ action, I keep appending to the string variable called ‘Message’ we’d defined earlier.
The output of the HTTP GET call when parsed gives us the stock symbol, currency, regularMarketPrice etc. all of which are dynamic content available to be used in the ‘Value’ field to construct the string for each competitor’s stock symbol.
Here, I use the ‘Post a message’ action in Power Automate to simply post the Message string variable to Teams in the chosen team/channel.
Keeping with the best practices of starting conversations in Teams, the ‘Subject’ field is set as well. To report when the compete stock prices have been captured, I also included the time snapshot when this message gets posted using a dynamic expression compiled using:
convertTimeZone(utcnow(),'UTC','India Standard Time','dd/MM/yyyy HH:mm:ss')
Testing the Power Automate flow
To test the flow, I can trigger it manually and check the Competitive Info channel to find the below message beautifully posted along with the latest stock prices of all competitors of Contoso!
Say hello to real-time, productive and meaningful collaboration!
The entire corporate strategy team can now always stay in the know about how Wall Street is responding to their competitors. Automated messages such as the above periodically posted in Microsoft Teams where the entire team collaborates allow the team to quickly react to stock price movements and analyse the market around them.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.