Link to our Telegram Channel - [ Ссылка ]
Link to our Twitter Profile - [ Ссылка ]
In this video, I teach you how to use the new Stocks data type feature in Microsoft Excel 365. As of now the Stocks data type is only available to standard Microsoft 365 accounts. So, if you do not have a Microsoft 365 account, you might want to sign up to use this awesome feature.
But before we get to the topic, let's learn what a linked data type really is?
Linked data type is an exclusive feature to Microsoft Excel 365.
As the name suggests, it is a data type connected to an online data source that allows you to import abundant, interesting information that you can work with and refresh.
Currently two linked data types are available in Excel - Stocks and Geography. The scope of this video is limited to just the Stocks data type.
You will find these on the Data tab of the ribbon.
The Stocks data type feature has made it a lot easier to pull market data into Excel.
The biggest disadvantage of the Stocks data type feature is that there is no possibility to refresh the prices automatically.
Now to the topic of the video.
In Step 1 you will collect the list of Nifty 500 stocks.
Go to nseindia.com
Click on Market Data and then on Indices under Market Watch.
Click on Nifty 500.
And then click on the link “Download list of Nifty 500 stocks”.
A CSV file named ind_nifty500list will be downloaded to your computer.
Now to Step 2
Open a new Excel document and navigate to the Data tab.
Select all the data in the CSV file you downloaded from nseindia.com in Step 1 and paste the data to A1 cell of the new excel spreadsheet.
Click Text to Columns and select Tab and Comma under Select Delimiters and click Apply.
Delete the columns that you do not want to use.
For this exercise you just need the Symbol column.
That’s it; you have successfully imported the ind_nifty500list CSV file to Excel!
Now to Step 3
Select all the cells in the Symbol column.
Click on the Stocks data type in the Data tab.
This will transform the cells to a Stocks data type.
This will take a few seconds to run, so be patient.
Once the transformation is done an icon in the shape of a bank will appear to the left of each company. This icon indicates that additional information is available for the data stored in the cell.
Rename the file as desired. Here I named it as Realtimestockprices. The default extension is .xlsx.
Select one or more cells, and the “Insert Data: Extract data to a cell” button will appear. Click that button, and then click a field name to extract more information. For example, you might pick Price.
Click the Insert Data button again to add more fields such as P/E, Industry and Year Incorporated.
Now whenever you want to get current data for your data types, right click a cell with the linked data type and select Data Type and Refresh.
This will refresh not only the cell you selected, but also any other cells that have that same data type.
If you wish to configure the Excel file to refresh data every time you open it, you should add the following macro to the document. Note that the Developer Tab option is not available in Excel Online. So to do this you have to use the Desktop version of Microsoft Excel 365.
Select the Developer tab and then select Visual Basic option.
Right-click on the ThisWorkbook object, and choose View Code. Enter the following code.
Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
MsgBox "Stock Data has been refreshed!"
End Sub
Click the small play button on the top to test the code. You should see the dialog box with text “Stock Data has been refreshed!” pop up.
Finally please do not forget to save this Excel file as an xlsm file to make it work every time you open the file up! Simply go to File, Save as, and choose the drop down for macro files.
To test if the file is working fine, close it and open again. You should see the dialog box with text “Stock Data has been refreshed!” pop up. This indicates that the file is working fine.
In the next part of the video, you will learn the practical purposes of the fetched data.
