London Stock Exchange Tracker in Excel – Stock Market
You can use this template to track the London Stock Exchange market performance.
Video Demo
Requirements
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
For all the templates in this ‘Excel for Stock Market’ series, please visit indzara.com/stock-market-templates
Download
Template features
This template can be used to view the London stock market performance.
- 351 stocks that make up FTSE 100 and FTSE250 are tracked for performance
- 1 is 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.
London Stock Exchange Dashboard
Dashboard shows that of the 351 stocks that are tracked in this template, how many gained in price and how many declined in price.
In the above screenshot, 116 gained and 224 declined. 1 stock is not mapped to the Excel service.
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.
Visit https://savvytime.com/converter/utc to view offset
Top Gainers
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.
Top Decliners
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.
Top Active Stocks
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.
The foundation of this template is the Stocks Data type and StockHistory function.
Please read more on Stocks Data Type.
Please read more on StockHistory function and how we use to extract the price history of a stock or currency.
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.
Feedback
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.
4 Comments
The LSE quotes prices in pence – there are 100 pennies to the GBP. So for example at close on Friday 21/04/23 the BP closing price was 529.40 pence. The GBP price was therefore £5.2940.
In all of the cells in this sheet that show prices you need to divide the retrieved stock price by 100 to reflect the correct £ price.
In general the vast majority of UK stocks trade at under £10.
Thank you for highlighting the issue.
We have updated the live version of the template to divide the price by 100.
Best wishes.
This template is excellent
Im looking to look at the uk market
The top350
I wanna see price and the high and low etc and see which is worth looking at to buy in low
I really love the data sheet so need to find how to hide it
What would also be great is a dashboard and portfolio sheets added into this
Enter your transactions like buy price…quantity of shares..date…giving current value and portfolio value etx…so the whole excel is from look at potential investments to portfolios transxations to overview of holdings
Thank you for showing interest in our template and sharing your valuable feedback.
The data sheet is hidden by default. If you are trying to unhide it, right-click any of the sheet name and click unhide. Then select the DATA sheet and unhide.
Regarding portfolio tracking,
We have separate template to track portfolio basic. Following is the link to the same for quick reference:
https://indzara.com/2021/04/stock-portfolio-tracker-excel-template/
Following link holds all the stock market related template available in our website:
https://indzara.com/stock-market-templates/
Best wishes.