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
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.
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
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
Great, could you please provide you script, I don't see it in the thread, thanks, Dan
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.
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
Thanks Stan.
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
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.
Sans date calculations:
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!')
Nice Tony. I went old school with WinHttp COM and threw in a quick Excel, sans formatting
;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)
Using WinHttp certainly does cut down on the typing.
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.
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.
For the fans of Excel [who like pretty]
;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)
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.
;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
;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
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 (https://techsupt.winbatch.com/webcgi/webbatch.exe?techsupt/nftechsupt.web+WinBatch/JSON+GeoNames~WSC~Json~Sample.txt)
As always the contributions are appreciated.
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.
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
Here is a similar thing converted using the SQLite Extender. Not sure how one might want the formatting but easily changed.
Jim
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)
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'
;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)
Yes on the concatenation.
Jim
Yep, and the $. is a powershell shortcut to refer back to previous objects.