Yahoo Finance and stock price for my Excel spreadsheet

Started by ifreedom451, September 29, 2020, 05:51:41 AM

Previous topic - Next topic

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


;  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

Quote from: ifreedom451 on September 29, 2020, 09:47:02 AM
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


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

"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: td on September 30, 2020, 07:01:55 AM
Thanks Stan.


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


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
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!')
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Nice Tony.  I went old school with WinHttp COM and threw in a quick Excel, sans formatting
Code (WINBATCH) Select


;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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

For the fans of Excel [who like pretty]
Code (WINBATCH) Select


;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


;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


;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.
"No one who sees a peregrine falcon fly can ever forget the beauty and thrill of that flight."
  - Dr. Tom Cade

stanl

Quote from: td on October 02, 2020, 07:11:19 AM
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


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


;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


stanl

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