CA Mukesh Kumar

ca-mukesh-kumar

Have you ever wanted to have live stock up dates in Microsoft Excel? You 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 I’m 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.

statement-of-gain-or-loss-on-mr-ram-portfolio

For this article I have sharedherewith my portfolio which I hold over the period of time.

Click here to download MicrosoftExcel 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.

open-internet-browser

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-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-3-go-to-microsoft-excel-and-create-new-sheet-and-name-it-as-gainer

Step -4 Go to “From Web” under Data Tab

step-4-go-to-from-web-under-data-tab

Step 5 Paste link http://money.rediff.com/gainers/bse/daily/groupa?src=gain_lose in Address tab and click go.

step-5-paste-link-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

new-web-query

Step 8 Click on Import button

step-8-click-on-import-button

Step 9 Go to properties

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. 

external-data-range-properties

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-11-select-ok

Step 12 all Information related Gainers are loaded into Gainersheet.

step-12-all-information-related-gainers-are-loaded-into-gainersheet

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-13-create-one-more-sheet-and-name-it-as-losers

Step 14 Go to the From Web under Data tab

step-14-go-to-the-from-web-under-data-tab

Step 15 Paste http://money.rediff.com/gainers/bse/daily/groupa?src=gain_lose in Address tab and click go.

step-15-paste-in-address-tab-and-click-go

Step 16 Go to Losers option and select daily

step-16-go-to-losers-option-and-select-daily

Step 17 Select entire range and Import into Microsoft Excel

step-17-select-entire-range-and-import-into-microsoft-excel

Step 18 Change properties details

step-18change-properties-details

Step 17 ion”Name this connection as “Losers_Informat, “Enable” Save query definition, “Disable” background refresh and Refresh every 10 minutes etc. and click enter. 

step-17-ion-name-this-connection-as-losers_informat-enable-save-query-definition-disable-background-refresh-and-refresh-every-10-minutes-etc

Step 18 Press ok

step-18-press-ok

Step 20 all Information related Losers are loaded into Losers sheet.

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))

step-21-now-come-to-the-main-report-sheet-and-type-a-simple-vlookup-function-to-get-latest-updated-information-for-our-shares

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-21-simply-multiply-current-market-price

Step 22 Deduct Total Cost from Current Market price (H4-F4) to compute the oritical information related to gain/or loss.

step-22-deduct-total-cost-from-current-market-price

Step 23 Copy and paste all Formula created in previous two step into other cells

step-23-copy-and-paste-all-formula-created-in-previous-two-step-into-other-cells

Finally we learned how to create dynamic Excel File which is linked with Internet and will be updated automatically.

In this article I just explained how to create Connections but in my next Article I’ll explain How to create Dynamic Charts with help of these data.

Hope you all are gain something from this article and if you have any query regarding this article or anything in Microsoft Excel feel free to comment or get in touch.

(Author can be reached at camukeshkumar@outlook.com)

More Under Finance

Posted Under

Category : Finance (3314)
Type : Articles (12597) Featured (4037)
Tags : Excel Tips (35)

13 responses to “How to Get Latest Stock Price in Microsoft Excel”

  1. Chandrasekharan says:

    This is great article. But only Group A shares are selected. How to select All shares

  2. supriya says:

    also can you suggest some similar methods for keeping track of mutual funds and NCD

  3. supriya says:

    very useful article. Thanks for sharing

  4. kunal says:

    Fantastic, How to get such fantastic idea

  5. Bankim Master says:

    Excellent and very useful information. Thanks a lot, Sir.

  6. Naveen says:

    Very Nice

  7. Rashmi says:

    Thank you for sharing your knowledge

  8. J bhar says:

    I have just read the article. It seems that it will be very helpfull. I request to share an article on bank loan project with cma data to prepare loan project easier.

  9. Inderpal Singh says:

    Thank you, It is nicely working.

  10. Harish Maheshwari says:

    Thanks for sharing.
    Keep it up.

  11. Arun says:

    Thanks for sharing your idea…!

  12. Pramod says:

    Thank you very very very Much 🙂 🙂

    Please Share some more Information Regarding Excel.

  13. Dheeraj Prasad Bhatt says:

    nice article

Leave a Reply

Your email address will not be published. Required fields are marked *

Search Posts by Date

April 2017
M T W T F S S
« Mar    
 12
3456789
10111213141516
17181920212223
24252627282930