Yahoo Finance and stock price for my Excel spreadsheet

[ifreedom451]

Hi all,

Just wondering if anyone have sample to get updates stock price on Yahoo Finance in order for me to automatically keep my stocks prices up to date.

Here is an example for an ETF stock on the Toronto Stock Exchange (TSE)

https://ca.finance.yahoo.com/quote/ZWU.TO?p=ZWU.TO&.tsrc=fin-srch

it is an https site, would like to be able to get the relevent stock information and then I would write some code to update my spreadsheets :-)

Thanks in advance for the help.
Daniel

[stanl]

I have old scripts [circa 2005-2008] that access http://finance.yahoo.com and create multi-tabbed workbooks

Code Select Expand



;  using a lookup table, this script obtains a Company Name
;  then creates an (optionally saved) workbook with 6
;  worksheets giving:
;  Sheet1: Company Profile
;  Sheet2: Current [reported] Stock Status
;  Sheet3: Valuation
;  Sheet4: Income Statement
;  Sheet5: Revenue Statement
;  Sheet6: Case Flow



uses IE Object to connect. If you think it may be a useful look-see I can post.

[ifreedom451]

Thanks, but I have read since the Yahoo acquisition, the API is no longer accessible, so I guess, the only way to get the data from the https page would be to be able to load the page and find a way to read it's content..  Anyone have a way to read the content from an https web page ? with sample code please

[stanl]

First, my script didn't use an api, it just read directly and parsed the HTML elements. Second, there ae several recent threads that discuss accessing https pages, with code..


[Edit]
Otherwise just a simple webscrape if you know the symbols, i.e. https://ca.finance.yahoo.com/quote/IBM

[ifreedom451]

Great, could you please provide you script, I don't see it in the thread, thanks, Dan

[stanl]

Great, could you please provide you script, I don't see it in the thread, thanks, Dan

You can get to the Yahoo API, but it is subscription service. My 2005 script wouldn't be much help. Below I quickly kludged code I submitted in a previous thread for batting line-ups: the output is a little messy but should give you a good start. If you have experience with Excel Power Query you could bring financial information in as charts or tables.

Code (WINBATCH) Select Expand



strUrl = "https://finance.yahoo.com/quote/AAPL/financials?p=AAPL"  ;Apple
ObjectClrOption('useany', 'System')
objWebUtil = ObjectClrNew('System.Net.WebUtility')


objUri = ObjectClrNew('System.Uri', strUrl)
objSvcManager = ObjectClrNew('System.Net.ServicePointManager')
protocols = ObjectClrType("System.Net.SecurityProtocolType",3072|768) 
objSvcManager.SecurityProtocol = protocols
objSvcPoint = objSvcManager.FindServicePoint(objUri)


objWebRequest = ObjectClrNew('System.Net.WebRequest')
objWebRequest = objWebRequest.Create(objUri)
objWebRequest.Timeout = objWebRequest.Timeout * 6
objResponse = objWebRequest.GetResponse()
objResponseStream = objResponse.GetResponseStream
Encoding = ObjectClrNew( 'System.Text.Encoding' )
objStreamReader = ObjectClrNew("System.IO.StreamReader", objResponseStream, Encoding.UTF8)


;; Load the CLR stream into a COM Automation object.
objDom = ObjectCreate('htmlfile')
objDom.Write(objStreamReader.ReadToEnd())
objResponse.Close()


strOut  = ''
objTbl = objDom.getElementsByClassName("D(tbr)")
ForEach objTr in objTbl
   strOut :=  objTr.innerText:@LF
Next


FilePut("C:\temp\Apple.txt", strOut)
exit


[td]

Thanks Stan.

[stanl]

Thanks Stan.

You are welcome. A much easier approach would be to get a download as .csv which goes into excel

Code (WINBATCH) Select Expand



index = "IBM"
strUrl = "https://query1.finance.yahoo.com/v7/finance/download/%index%?period1=1530489600&period2=1593561600&interval=1wk&events=history"



If you just paste the url [with IBM substituted for %index%] it will download IBM.csv to your download folder. period1 and period2 are Unix which could be calculated in WB from base 1970:01:01 and and would return 1 week intervals.


I don't have time now to mess with more code as SAP Hana is calling from work, but might make a nice script for Tech DB.

[td]

Sans date calculations:

Code (winbatch) Select Expand

index = 'IBM'
strUrl = "https://query1.finance.yahoo.com/v7/finance/download/":index:"?period1=1530489600&period2=1593561600&interval=1wk&events=history"

ObjectClrOption('useany', 'System')
objWebUtil = ObjectClrNew('System.Net.WebUtility')

objUri = ObjectClrNew('System.Uri', strUrl)
objSvcManager = ObjectClrNew('System.Net.ServicePointManager')
protocols = ObjectClrType("System.Net.SecurityProtocolType",3072|768) 
objSvcManager.SecurityProtocol = protocols
objSvcPoint = objSvcManager.FindServicePoint(objUri)

objWebRequest = ObjectClrNew('System.Net.WebRequest')
objWebRequest = objWebRequest.Create(objUri)
objWebRequest.Timeout = objWebRequest.Timeout * 5
objResponse = objWebRequest.GetResponse()
objResponseStream = objResponse.GetResponseStream
Encoding = ObjectClrNew( 'System.Text.Encoding' )
objStreamReader = ObjectClrNew("System.IO.StreamReader", objResponseStream, Encoding.UTF8)

strOut = 'c:\temp\IMB.csv'
if FileExist(strOut) then FileDelete(strOut)
FilePut(strOut, objStreamReader.ReadToEnd())
if FileExist(strOut) then Run('notepad.exe', strOut)
else Pause('No Luck', 'wah wah wahhh!')

[stanl]

Nice Tony.  I went old school with WinHttp COM and threw in a quick Excel, sans formatting

Code (WINBATCH) Select Expand



;Winbatch 2020A - download Yahoo Finance data into Excel
;Quick Hack
;Stan Littlefield, September 30, 2020
;=========================================================================================
IntControl(73,1,0,0,0)
;set up for 2 week period
base = "1970:01:01:00:00:00"
base = TimeSubtract(base,"0000:00:00:05:00:00") ;adjust for EST, but shouldn't matter
Now=TimeYmdHms()
Now1 = TimeSubtract(Now,"0000:00:14:00:00:00")
p2 = TimeDiffSecs(Now,base)
p1 = TimeDiffSecs(Now1,base)


index = "IBM"
cFile = "c:\temp\%index%.csv"
If FileExist(cFile) Then FileDelete(cFile)
cUrl = "https://query1.finance.yahoo.com/v7/finance/download/%index%?period1=%p1%&period2=%p2%&interval=1wk&events=history"


Message("URL",cUrl) ;test remove when satisfied


request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()


FilePut(cFile,request.ResponseText)


If FileExist(cFile)
   ClipPut(StrReplace(FileGet(cFile),",",@TAB))
   oXL = CreateObject("Excel.Application")
   oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
   oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
   oXL.UserControl      = @TRUE
   oXL.DisplayAlerts    = @FALSE
   oXL.WorkBooks.Add()
   oWS = oXL.ActiveWorkBook.Worksheets(1)
   oWS.Activate()
   oWS.Name = index:"Yahoo_Financial"
   oWS.Cells(1,1).Select()
   oWS.Paste()
   oWS.UsedRange.Columns.Autofit()
   oWS=0
   oXL=0
   Pause("Data Loaded Into Excel","Save or Close Workbook")
Else
   Terminate(@TRUE,"File Not Created",cFile)
Endif
Exit
;=========================================================================================
:WBERRORHANDLER
oTask = 0
oClient = 0
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)




[td]

Using WinHttp certainly does cut down on the typing.

[stanl]

Seems 6 of 1.... but having a 3.4k plus of stock tickers as a lookup and scripting to place in a db table with option to output to Excel makes the WinHttp.WinHttpRequest.5.1 object workable.

[td]

I was attempting to be humorous but as is often the case I failed again. For fans of Excel, I am sure the approach is most useful.

[stanl]

For the fans of Excel [who like pretty]

Code (WINBATCH) Select Expand



;Winbatch 2020A - download Yahoo Finance data into Excel
;Quick Hack, but pretty
;Stan Littlefield, September 30, 2020
;=========================================================================================
IntControl(73,1,0,0,0)
;set up for 2 week period
base = "1970:01:01:00:00:00"
base = TimeSubtract(base,"0000:00:00:05:00:00") ;adjust for EST, but shouldn't matter
Now=TimeYmdHms()
Now1 = TimeSubtract(Now,"0000:00:14:00:00:00")
p2 = TimeDiffSecs(Now,base)
p1 = TimeDiffSecs(Now1,base)


index = "AAPL"
cFile = "c:\temp\%index%.csv"
If FileExist(cFile) Then FileDelete(cFile)
cUrl = "https://query1.finance.yahoo.com/v7/finance/download/%index%?period1=%p1%&period2=%p2%&interval=1wk&events=history"


Message("URL",cUrl) ;test remove when satisfied


request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()


FilePut(cFile,request.ResponseText)


If FileExist(cFile)
   ClipPut(StrReplace(FileGet(cFile),",",@TAB))
   oXL = CreateObject("Excel.Application")
   oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
   oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
   oXL.UserControl      = @TRUE
   oXL.DisplayAlerts    = @FALSE
   oXL.WorkBooks.Add()
   oWS = oXL.ActiveWorkBook.Worksheets(1)
   oWS.Activate()
   oWS.Name = index:"_Yahoo_Financial"
   oWS.Cells(1,1).Select()
   oWS.Paste()
   oWS.UsedRange.Select()
   oXL.Selection.Font.Name = 'Tahoma'
   oXL.Selection.Font.Size = 9
   oXL.Selection.Font.Bold = @True
   oWS.UsedRange.Columns.Autofit()
   oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
   oWS.Range("Table1[#All]").Select()
   oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
   oXL.ActiveWindow.DisplayGridlines = @False
   oWS.Cells(1,1).Select()
   oWS=0
   oXL=0
   Pause("Data Loaded Into Excel","Save or Close Workbook")
Else
   Terminate(@TRUE,"File Not Created",cFile)
Endif
Exit
;=========================================================================================
:WBERRORHANDLER
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)




[stanl]

and for the fans who like Json, below gets data for multiple tickers [using Tony's code]. I think this could be parsed with Jscript.

Code (WINBATCH) Select Expand



;Tony's code to obtain Yahoo finance for multiple stocks; returns Json
strUrl = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=IBM,GOOG,AAPL"
ObjectClrOption('useany', 'System')
objWebUtil = ObjectClrNew('System.Net.WebUtility')


objUri = ObjectClrNew('System.Uri', strUrl)
objSvcManager = ObjectClrNew('System.Net.ServicePointManager')
protocols = ObjectClrType("System.Net.SecurityProtocolType",3072|768) 
objSvcManager.SecurityProtocol = protocols
objSvcPoint = objSvcManager.FindServicePoint(objUri)


objWebRequest = ObjectClrNew('System.Net.WebRequest')
objWebRequest = objWebRequest.Create(objUri)
objWebRequest.Timeout = objWebRequest.Timeout * 5
objResponse = objWebRequest.GetResponse()
objResponseStream = objResponse.GetResponseStream
Encoding = ObjectClrNew( 'System.Text.Encoding' )
objStreamReader = ObjectClrNew("System.IO.StreamReader", objResponseStream, Encoding.UTF8)


strOut = 'c:\temp\Output.Json'
if FileExist(strOut) then FileDelete(strOut)
FilePut(strOut, StrReplace(objStreamReader.ReadToEnd(),",",",":@LF))
if FileExist(strOut) then Run('notepad.exe', strOut)
else Pause('No Luck', 'wah wah wahhh!')



[EDIT]: I dug up an old Jscript udf I used years ago, data returned as bar-delimited

Code (WINBATCH) Select Expand



;Tony's code to obtain Yahoo finance for multiple stocks; returns Json
IntControl(73,1,0,0,0)
Gosub udfs
strUrl = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=IBM,GOOG,AAPL"
ObjectClrOption('useany', 'System')
objWebUtil = ObjectClrNew('System.Net.WebUtility')


objUri = ObjectClrNew('System.Uri', strUrl)
objSvcManager = ObjectClrNew('System.Net.ServicePointManager')
protocols = ObjectClrType("System.Net.SecurityProtocolType",3072|768) 
objSvcManager.SecurityProtocol = protocols
objSvcPoint = objSvcManager.FindServicePoint(objUri)


objWebRequest = ObjectClrNew('System.Net.WebRequest')
objWebRequest = objWebRequest.Create(objUri)
objWebRequest.Timeout = objWebRequest.Timeout * 5
objResponse = objWebRequest.GetResponse()
objResponseStream = objResponse.GetResponseStream
Encoding = ObjectClrNew( 'System.Text.Encoding' )
objStreamReader = ObjectClrNew("System.IO.StreamReader", objResponseStream, Encoding.UTF8)


;comment/uncomment either section below to view results




;1. if just saving Json as file


;strOut = 'c:\temp\Output.Json'
;if FileExist(strOut) then FileDelete(strOut)
;FilePut(strOut, StrReplace(objStreamReader.ReadToEnd(),",",",":@LF))
;if FileExist(strOut) then Run('notepad.exe', strOut)
;else Pause('No Luck', 'wah wah wahhh!')


;2. convert json to bar-delimited file


strOut = 'c:\temp\Output.Json'
if FileExist(strOut) then FileDelete(strOut)
jdata = StrReplace(objStreamReader.ReadToEnd(),",",",":@LF)
output = GetJSON(jdata)
output = StrReplace(output,",","|")
FilePut(strOut, StrReplace (output, @LF, @CRLF))
if FileExist(strOut) then Display(2,'Json Data as Bar Delimited', strOut)
if FileExist(strOut) then Run('notepad.exe', strOut)
Exit




;=========================================================================================
:WBERRORHANDLER
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)


:udfs
#DefineFunction GetJSON(jdata)
If jdata == "" Then Return ""
objJSC = CreateObject ("MSScriptControl.ScriptControl")
objJSC.Language = "JScript"
objJSC.AddCode(: `function json2txt(obj,path){var txt='';for(var key in obj){if(obj.hasOwnProperty(key)){if('object'==typeof(obj[key])){txt+=json2txt(obj[key],path+(path?'|':'')+key);}else{txt+=path+'|'+key+','+obj[key]+'\n';}}}return txt;}`)
Return objJSC.Eval(: `json2txt(` : jdata : `,'')`)
; JS code from Patrick Fisher at "http://stackoverflow.com/questions/10221229/list-all-keys-and-values-of-json" ; 2012-04-19T03:48:24.
#EndFunction
;------------------------------------------------------------------------------------------------------------------------------------------


Return


[td]

This reminded me of another approach using javascript to parse JSON that you wrote ~ten years ago.

https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/JSON+GeoNames~WSC~Json~Sample.txt

As always the contributions are appreciated.

[stanl]

This reminded me of another approach using javascript to parse JSON that you wrote ~ten years ago.

God. that long ago, and we hadn't even considered a terabyte :o .  Did a lot of those .wsc files back then.

[stanl]

Want a good Covid lesson:  take my code [the one in this thread that makes excel pretty].


change to:
Now1 = TimeSubtract(Now,"0000:00:350:00:00:00")
index = "DAL"


and see how Delta Airlines has fared since late 2019, unless it is fake news :o

[JTaylor]

Here is a similar thing converted using the SQLite Extender.  Not sure how one might want the formatting but easily changed.

Jim

Code (winbatch) Select Expand



DirChange(DirScript())
IntControl(73,1,0,0,0)

AddExtender("wbsql44i.dll")
db  = ":memory:"      ;INCLUDE FULL PATH FOR DATABASE
dbOpen(db,@TRUE)

  strOut = "output.txt"
  cFile = "stocks.json"
  cUrl = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=IBM,GOOG,AAPL"
  request = Createobject("WinHttp.WinHttpRequest.5.1")
  request.Open("GET", cUrl, @False )
  request.Send()
  json = request.ResponseText
  ObjectClose(request)

  If dbQueryValue(db,"SELECT json_valid('":json:"') ;") == 0 Then
    Message("Error","JSON Is Not Valid?")
    Exit
  EndIf

  len = dbQueryValue(db,"Select json_array_length('":json:"','$.quoteResponse.result')")

  txt = ""
  SQLText = "SELECT key || '|' FROM json_tree('":json:"','$.quoteResponse.result[0]') WHERE json_tree.type NOT IN ('object','array')"
  txt     = ItemRemove(-1,dbQueryValue(db,SQLText),"|"):@CRLF

  For x = 0 to len
    SQLText = "SELECT value || '|' FROM json_tree('":json:"','$.quoteResponse.result[%x%]') WHERE json_tree.type NOT IN ('object','array')"
    txt     = txt:ItemRemove(-1,dbQueryValue(db,SQLText),"|"):@CRLF
  Next
  FilePut(strOut,txt)
  clipput(txt)
  Message("dbQueryValue()",txt)


;#############################################################

dbClose()


if FileExist(strOut) then Run('notepad.exe', strOut)

Exit


;=========================================================================================
:WBERRORHANDLER
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)





[stanl]

Nice. Very little mods needed to insert into Excel.  I'm assuming || in the SQL statement is concatenation - similar to Hana SQL, which I stumbled over confusing with a WB 'or'

Code (WINBATCH) Select Expand



;Using Jim Taylor's SQLite Extender - processing Yahoo quotes to Excel
IntControl(73,1,0,0,0)
path = "c:\scripts\sqlite\net\"
AddExtender(path:"wbsql44i.dll")  ;change location
db  = ":memory:"      ;INCLUDE FULL PATH FOR DATABASE
dbOpen(db,@TRUE)


cUrl = "https://query1.finance.yahoo.com/v7/finance/quote?symbols=IBM,GOOG,AAPL"
request = Createobject("WinHttp.WinHttpRequest.5.1")
request.Open("GET", cUrl, @False )
request.Send()
json = request.ResponseText
ObjectClose(request)


If dbQueryValue(db,"SELECT json_valid('":json:"') ;") == 0 Then
  Message("Error","JSON Is Not Valid?")
  Exit
EndIf


len = dbQueryValue(db,"Select json_array_length('":json:"','$.quoteResponse.result')")


txt = ""
SQLText = "SELECT key || '|' FROM json_tree('":json:"','$.quoteResponse.result[0]') WHERE json_tree.type NOT IN ('object','array')"
txt     = ItemRemove(-1,dbQueryValue(db,SQLText),"|"):@CRLF


For x = 0 to len
  SQLText = "SELECT value || '|' FROM json_tree('":json:"','$.quoteResponse.result[%x%]') WHERE json_tree.type NOT IN ('object','array')"
  txt     = txt:ItemRemove(-1,dbQueryValue(db,SQLText),"|"):@CRLF
Next
clipput(Strreplace(txt,"|",@TAB))
dbClose()


;insert into Excel
oXL = CreateObject("Excel.Application")
oXL.Visible          = @TRUE  ; change this to @FALSE to run hidden
oXL.ScreenUpdating   = @TRUE  ; if running hidden, change this to @FALSE
oXL.UserControl      = @TRUE
oXL.DisplayAlerts    = @FALSE
oXL.WorkBooks.Add()
oWS = oXL.ActiveWorkBook.Worksheets(1)
oWS.Activate()
oWS.Name = "Quotes_Yahoo_Financial"
oWS.Cells(1,1).Select()
oWS.Paste()
oWS.UsedRange.Select()
oXL.Selection.Font.Name = 'Tahoma'
oXL.Selection.Font.Size = 9
oXL.Selection.Font.Bold = @True
oWS.UsedRange.Columns.Autofit()
oWS.ListObjects.Add(:1,oWS.UsedRange, , 1).Name ="Table1"
oWS.Range("Table1[#All]").Select()
oWS.ListObjects("Table1").TableStyle = "TableStyleLight15"
oXL.ActiveWindow.DisplayGridlines = @False
oWS.Cells(1,1).Select()
oWS=0
oXL=0
Pause("Data Loaded Into Excel","Save or Close Workbook")




;if FileExist(strOut) then Run('notepad.exe', strOut)


Exit




;=========================================================================================
:WBERRORHANDLER
wberroradditionalinfo = wberrorarray[6]
lasterr = wberrorarray[0]
handlerline = wberrorarray[1]
textstring = wberrorarray[5]
linenumber = wberrorarray[8]
errmsg = "Error: ":lasterr:@LF:textstring:@LF:"Line (":linenumber:")":@LF:wberroradditionalinfo
Terminate(@TRUE,"Error Encountered",errmsg)


[JTaylor]

Yes on the concatenation.

Jim

[stanl]

Yep, and the $. is a powershell shortcut to refer back to previous objects.