HOW TO GET LATEST STOCK PRICE IN MICROSOFT EXCEL
HOW TO GET LATEST STOCK PRICE IN MICROSOFT EXCEL Have you ever wanted to have livestock updates in Microsoft Excel You might interested to w
CA Deepak Gupta
Nov 22, 2016HOW TO GET LATEST STOCK PRICE IN MICROSOFT EXCEL
Have you ever wanted to have livestock updates in Microsoft ExcelYou might interested to watch Share Market to know the current value of your portfolio or your clients or else just for knowledge.
In this post we will learn about how to get stock quotes for specified all symbols using simple Excel Tricks.
In this article Im trying to explain how to create a link in excel with current market price i.e. all information related to your portfolio will be automatically fetch into your Microsoft Excel spreadsheet.
For this article I have shared herewith my portfolio which I hold over the period of time.
Click here to download Microsoft Excel Stock Workbook. It will be much easier to follow this tutorial if you refer to the workbook.
In this file, I have all basic information related to cost like name, date, price per unit and quality purchased. But crucial information Current Market is missing.
And to get real time updated information related to these shares, we need to follow these simple step
Step: 1 Open Internet Browser and type http://money.rediff.com/index.html and Select More Gainer.
This link simply provides information related to all shares who gained at that point of time i.e. Current market price is more than opening price.
Step -2 Select and Copy the path
Step 3 Go to Microsoft Excel and create new sheet (Short Cut key Alt+ F11) and name it as Gainer
Step -4 Go to From Web under Data Tab
Step 5 Paste link http://money.rediff.com/gainers/bse/daily/groupasrc=gain_lose in Address tab and click go.
Have a patience for a movement till the full page is getting to be loaded.
Step 7 click this option to select the entire page
Step 8 Click on Import button
Step 9 Go to properties
Step 10 Name this connection as Gainer_Information, Enable Save query definition, Disable background refresh and Refresh every 10 minutes etc. and click enter.
In this step I modified the default properties according to my needs, you may select something else and for example you may disable Refresh data when opening the file.
Step 11 Select OK
Step 12 all Information related Gainers are loaded into Gainer sheet.
Now we created a connected price information for all shares who gained during the day i.e. it will be updated automatically after each span of 10 minutes. But this connection contain only information related to gainers not for losers. And there is equal possibility that the price of our shares might not be gaining always and information related to those share will not be reflected in this imported data.
Therefore we need to again create one more connection for those shares who lose during the day and repeat all steps to create one connection for losers.
Step 13 Create One More sheet and name it as Losers
Step 14 Go to the From Web under Data tab
Step 15 Paste http://money.rediff.com/gainers/bse/daily/groupasrc=gain_lose in Address tab and click go.
Step 16 Go to Losers option and select daily
Step 17 Select entire range and Import into Microsoft Excel
Step 18 Change properties details
Step 17 Name this connection as Losers_Information, Enable Save query definition, Disable background refresh and Refresh every 10 minutes etc. and click enter.
Step 18 Press ok
Step 20 all Information related Losers are loaded into Losers sheet.
Now we successfully created connection for both Gainer and Losers current market price information listed on BSE.
Step 21 Now Come to the Main Report sheet and type a simple Vlookup function to get latest updated information for our Shares
=IFERROR(VLOOKUP(B4,Gainer!A:E,4,FALSE),VLOOKUP(Report!B4,Losers!A:E,4,FALSE))
In this formula I use VLOOKUP along with IFERROR function because while using only =VLOOKUP(B4,Gainer!A:E,4,FALSE) result will be #NA. it means share information related to SBI is not available in Gainer Sheet. Share Information related to SBI is in Losers information.
That the main reason why I use IFERROR function with VLOOKUP, if information related to any share is not available in gainer sheet the formula will switch to get information from losers sheet.
Step 21 Simply multiply Current Market Price with no of share (D4*G4) held to get current market value.
Step 22 Deduct Total Cost from Current Market price (H4-F4) to compute theoritical information related to gain/or loss.
Step 23 Copy and paste all Formula created in previous two step into other cells
Click here to download Microsoft Excel Stock Workbook. It will be much easier to follow this tutorial if you refer to the workbook.
In this file, I have all basic information related to cost like name, date, price per unit and quality purchased. But crucial information Current Market is missing.
And to get real time updated information related to these shares, we need to follow these simple step
Step: 1 Open Internet Browser and type http://money.rediff.com/index.html and Select More Gainer.
This link simply provides information related to all shares who gained at that point of time i.e. Current market price is more than opening price.
Step -2 Select and Copy the path
Step 3 Go to Microsoft Excel and create new sheet (Short Cut key Alt+ F11) and name it as Gainer
Step -4 Go to From Web under Data Tab
Step 5 Paste link http://money.rediff.com/gainers/bse/daily/groupasrc=gain_lose in Address tab and click go.
Have a patience for a movement till the full page is getting to be loaded.
Step 7 click this option to select the entire page
Step 8 Click on Import button
Step 9 Go to properties
Step 10 Name this connection as Gainer_Information, Enable Save query definition, Disable background refresh and Refresh every 10 minutes etc. and click enter.
In this step I modified the default properties according to my needs, you may select something else and for example you may disable Refresh data when opening the file.
Step 11 Select OK
Step 12 all Information related Gainers are loaded into Gainer sheet.
Now we created a connected price information for all shares who gained during the day i.e. it will be updated automatically after each span of 10 minutes. But this connection contain only information related to gainers not for losers. And there is equal possibility that the price of our shares might not be gaining always and information related to those share will not be reflected in this imported data.
Therefore we need to again create one more connection for those shares who lose during the day and repeat all steps to create one connection for losers.
Step 13 Create One More sheet and name it as Losers
Step 14 Go to the From Web under Data tab
Step 15 Paste http://money.rediff.com/gainers/bse/daily/groupasrc=gain_lose in Address tab and click go.
Step 16 Go to Losers option and select daily
Step 17 Select entire range and Import into Microsoft Excel
Step 18 Change properties details
Step 17 Name this connection as Losers_Information, Enable Save query definition, Disable background refresh and Refresh every 10 minutes etc. and click enter.
Step 18 Press ok
Step 20 all Information related Losers are loaded into Losers sheet.
Now we successfully created connection for both Gainer and Losers current market price information listed on BSE.
Step 21 Now Come to the Main Report sheet and type a simple Vlookup function to get latest updated information for our Shares
=IFERROR(VLOOKUP(B4,Gainer!A:E,4,FALSE),VLOOKUP(Report!B4,Losers!A:E,4,FALSE))
In this formula I use VLOOKUP along with IFERROR function because while using only =VLOOKUP(B4,Gainer!A:E,4,FALSE) result will be #NA. it means share information related to SBI is not available in Gainer Sheet. Share Information related to SBI is in Losers information.
That the main reason why I use IFERROR function with VLOOKUP, if information related to any share is not available in gainer sheet the formula will switch to get information from losers sheet.
Step 21 Simply multiply Current Market Price with no of share (D4*G4) held to get current market value.
Step 22 Deduct Total Cost from Current Market price (H4-F4) to compute theoritical information related to gain/or loss.
Step 23 Copy and paste all Formula created in previous two step into other cells
About Author
CA Deepak Gupta
Co Founder
CA Deepak Gupta,is Co-founder of Studycafe. He is Microsoft Office Specialist and Corporate Trainer of AI Tools, Microsoft Excel.
He is Finance Influencer having more than 250K followers on Social Media. CA Deepak Gupta, is Having more than 14 plus years of experience, and he has Worked with best brands Like, Hero, Wipro, Ericsson before Starting Studycafe. He has Trained more than 20000 Persons in Microsoft Excel, PowerPoint, Power BI, Google Sheet, Google Forms and Other Tools.
StudyCafe
Delhi, Delhi, India
3423My Recent Articles
- UltraTech Cement slapped with Rs. 808.78 Cr Income Tax Demand
- GST: High Court upheld constitutional validity of Section 16(2)(c), asks government to address ITC issues of genuine purchasers
- Old vs New Tax Regime for Tax Year 2026-27
- High court criticizes Income Tax Department for not releasing ITR Utilities despite 11 years of directions
- Fino Payments Bank CEO Rishi Gupta Gets Bail in GST Case, Bank Clarifies No Direct Link
Up Next
Loading suggestions…
Recent Posts
All Posts
Tags
No tags yet.
Recent Posts
All Posts
Tags
No tags yet.