Almost every intranet project I work on there is a request to display the company’s stock information on their new intranet. My typical approach would be to leverage a backend service in Azure to call the stock API, cache the data, and provide a web API for a custom SPFx web part to display the information.

I wanted to challenge this model to see if it could be done leveraging out-of-the-box (OOTB) services and features in Microsoft 365.

The Solution

At it’s core the solution is pretty straight-forward and easy to follow. I am leveraging SharePoint to store and present the stock information and Power Automate to update the stock information on a re-occurring basis. Custom JSON View formatting allows me to display the information in a more user friendly manner and a Power Automate per user plan allows me to use the HTTP action to call the REST api.

All in the end result of the solution ended up looking like:

Screenshot of Microsoft Stock TickerScreenshot of Microsoft Stock Ticker

Now to dive into each piece of the solution, starting with SharePoint.

Building the SharePoint List

The SharePoint list to store the stock information is pretty straight-forward as it is comprised of 4 columns: Title, Current Price, Change, Latest Update. You are going to start by creating a SharePoint list from scratch and then the 4 columns listed above (with the exception of Title) as single line of text.

SharePoint list columns on the Stock Information ListSharePoint list columns on the Stock Information List

After creating the list I added a single item and filled in the column information with mock data so I could start working on the JSON View Formatting. This item will then continually be updated by the Power Automate flow to reflect the latest information.

Stock Information List ItemStock Information List Item

Crafting the JSON View Formatting

For those who are not familiar with JSON View or Column Formatting it is a way you can modify the look and feel of items in a list using JSON. You can find out more information on the subject here: View Formatting and Column Formatting

There is also a great GitHub Repo with all kinds of samples that can help you get started: https://github.com/pnp/sp-dev-list-formatting.

Before I start writing the JSON I like to have a design in mind that I am trying to achieve so I have a baseline to build towards and then I refine and tweak as I go along. For the Stock Ticker I used the following mockup.

Stock Ticker Design MockupStock Ticker Design Mockup

From the mockup I start to plan the HTML structure I will need to and map columns to elements in the design.

Stock Ticker HTML MockupStock Ticker HTML Mockup

Now onto the fun part, crafting the JSON for the custom view formatting. I pasted the full JSON below but wanted to call out a few aspects about it first.

  1. I am leveraging Office UI Fabric Core CSS classes within in my design to align with the OOTB SharePoint Online styling and allow it to update with the theme of the site.
  2. I am using the Change column to determine if the current price is trending positive or negative from the previous day close and rendering the appropriate arrow and color to match. Excel-style expressions within the View Formatting make this super easy.
  3. It doesn’t need to be perfect out the gate, start small and work your way towards the end state. I never get it perfect on the first try and am always iterating over the design and styles as I test it.
json
{
"$schema": "https://developer.microsoft.com/json-schemas/sp/view-formatting.schema.json",
"hideSelection": true,
"hideColumnHeader": true,
"rowFormatter": {
"elmType": "a",
"attributes": {
"class": "ms-borderColor-themePrimary ms-borderColor-themeDarker--hover",
"href": "https://www.google.com/search?safe=off&tbm=fin&sxsrf=ALeKk03Aa55apr6WXTb0exVA8wi2WLL0pg:1601502784957&q=NASDAQ:+MSFT",
"title": "Click here for more stock information.",
"target": "_blank"
},
"style": {
"border-style": "solid",
"border-width": "1px",
"border-radius": "2px",
"color": "black",
"display": "flex",
"justify-content": "space-between",
"max-width": "380px",
"padding": "16px",
"text-decoration": "none"
},
"children": [
{
"elmType": "div",
"attributes": {
"class": "ms-fontSize-mPlus ms-fontWeight-bold"
},
"children": [
{
"elmType": "div",
"style": {
"overflow": "hidden",
"text-overflow": "ellipsis",
"white-space": "nowrap"
},
"txtContent": "[$Title]"
},
{
"elmType": "div",
"attributes": {
"class": "ms-fontSize-sPlus ms-fontWeight-regular"
},
"txtContent": "[$LastUpdated]"
}
]
},
{
"elmType": "div",
"style": {
"flex": "none"
},
"children": [
{
"elmType": "span",
"style": {
"color": "=if(Number([$Change]) > 0,'green','red')",
"display": "=if(Number([$Change]) == 0,'none','inline-block')",
"font-size": "24px",
"padding": "0 4px"
},
"attributes": {
"iconName": "=if(Number([$Change]) > 0,'ArrowUpRight8','ArrowDownRight8')",
"title": "='Change since previous day close: '+[$Change]+'.'"
}
},
{
"elmType": "span",
"style": {
"margin-left": "5px",
"min-width": "60px",
"font-size": "32px"
},
"txtContent": "[$CurrentPrice]"
}
]
}
]
}
}

There you have it, the start of the solution. Part 2 will focus on the Power Automate flow that is used to call the stock API and update the information on a re-occurring basis.

AaaS - Anthony as a Service