2

I want to get the live market price(Indian Market) of some stocks which are already mentioned in my MSExcel sheet, which can automatically update. I saw a function in google sheet (=googleFinance()) that does the same thing. But this function is not available in MS EXcel 2016. How to use this function in MSExcel or are there any alternatives?

Ayush
  • 373
  • 4
  • 12
  • What version of Excel do you have? – BigBen Jan 21 '21 at 14:20
  • msexcel 2016 @BigBen – Ayush Jan 21 '21 at 14:20
  • 1
    https://support.microsoft.com/en-us/office/excel-data-types-stocks-and-geography-61a33056-9935-484f-8ac8-f1a89e210877?ui=en-us&rs=en-us&ad=us – Pandas INC Jan 21 '21 at 16:50
  • @PandasINC that is only for excel365, I have msexcel 2016 – Ayush Jan 22 '21 at 05:02
  • Can you make web queries from msexcel 16? You should be able, in the ribon head to data, in data select "get external data" and check from web. If that works you should be able to serach for your stock in google finnace and then import the data. If this doesn't work. Please tell me there are two more methods a bit longer/ complicated. – Pandas INC Jan 22 '21 at 13:49
  • Yes, I am able to import the data but this will be a lengthy task, as I want the current price to auto-update every minute. @PandasINC – Ayush Jan 23 '21 at 07:15

1 Answers1

1

I did a code that works for me.

Function GoogleFinance(index As String)
Dim resultado As Double, ie As Object

Set ie = CreateObject("internetexplorer.application")
ie.navigate "https://www.google.com/finance/quote/" + index + ":BVMF"
Do While ie.busy

DoEvents
Loop
On Error Resume Next
resultado = ie.document.getElementsByClassName("YMlKec fxKbKc")(0).innertext
ie.Quit
GoogleFinance = resultado

End function
David Leal
  • 6,373
  • 4
  • 29
  • 56
Pedro Carlos Ferraz
  • 11
  • 3