You can use this template to track the U.S. Stock market performance.
Microsoft has introduced a new feature in Excel called Data types. One of the data types is ‘Stocks’. That’s what we will be using for these templates. This feature is available in Microsoft 365 Subscription Plans for PC and Web and Free Excel for the web
This template can be used to view the India. stock market performance.
- Automatically shows the major indices – NIFTY 50 and SENSEX
- 1690 stocks are tracked for performance – 19 are currently not available
- New stocks can be added easily
- View the top gainers (stocks which increased in price, ranked by Change %)
- View the top decliners (stocks which decreased in price, ranked by Change %)
- View the most actives (stocks which traded most in volume, ranked by Volume)
- See more details on the top gainers, top decliners and most actives
The foundation of this template is the Stocks Data type and StockHistory function.
Two of the most used indices in the Indian stock market are NIFTY 50 and SENSEX.
Dashboard shows the indices’ current price, change in price and change %.
You can also see that of the 1690 stocks that make up the indices, how many gained in price and how many declined in price.
In the above screenshot, 964 gained and 637 declined. 19 stocks are not mapped.
On the top right you can see the maximum of the last trade time of all the stocks.
The default time that Microsoft provides is UTC, we convert that to Indian Standard Time via the offset of 5.5 hours.
Visit https://savvytime.com/converter/utc to view offset
The dashboard shows the Top gainers of the day.
In the Gainers sheet, you can see more details about the top gainers. You can also see top 20 stocks that gained.
The dashboard also shows the top decliners of the day.
You can see more details of these stocks and also top 20 stocks that declined in price, in the DECLINERS sheet.
Finally, the dashboard also shows the top 10 stocks that are active (by trading volume) on the day.
To see top 20 stocks that were active and also see more details such as 52 week price range, please see the ACTIVES sheet.
Here is the link to the article from Microsoft which shows which exchanges are available and the delay in data refresh for each.
How to refresh?
To refresh the price and other information, just use the regular Excel refresh in the Data ribbon.
How to add more stocks?
Please unhide the hidden DATA sheet and add stock symbols to that table. They will automatically be added for tracking in the dashboard.
Please share your experience. I expect this to be different with different exchanges and countries. Is it working for you? Is it working differently? I would love to hear from you.