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:
List of compete stocks to track: The list of stock symbols to track is maintained in a simple Excel file which can be edited by anyone in the team making it effortless to add/remove competitors from the stock tracking mix. The table where stock symbols are entered is given a name as well.
Channel where the stock price alerts will be posted: This can be the Competitive Info channel in the Corporate Strategy HQ team.
Web service / Data source for querying stock prices: As the most critical piece of the automation, the team will need a source to query latest stock price information for a given symbol. There are a number of public web APIs available which provide this information as a simple JSON response via a simple http GET call. For the purpose of this automation, I pick up the Yahoo! Finance API. See a sample response here: https://query1.finance.yahoo.com/v7/finance/chart/MSFT?&interval=1m
Each step in the creation of the flow used for this is explained below:
Create Flow: I created this using the “+Create” menu option, starting a “Scheduled flow” from scratch.
Recurrence: Since the flow is a recurring one, the first step of the solution is Recurrence (a timer event) which Power Automate will automatically add resulting from the Create new step above.
Initialize variable: Since I want to post a single message in the Teams channel containing prices of more than one stock symbols of Contoso’s competitors, I will create a variable and keep building the content of the channel message dynamically.
Fetch stock symbols from the table: I then read the compete stock symbols by listing rows from the Excel file Stock.xlsx as the next step.
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.
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.
Post the message into Teams channel: The last step is to post the message containing all compete stock prices into Microsoft Teams channel. You should be careful that this step should be outside the ‘Apply to each’ loop, otherwise, the flow will end up posting multiple times with incremental content as the Message string gets constructed for symbols one by one.
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.