2 Messages
Digest #4532
Messages
Tue Apr 30, 2019 10:17 am (PDT) . Posted by:
hlstevens772
Randy,
Using your smfGetYahooPortfolioView as a template, I am trying to modify it to use IEX Trading as a backup.
The problem I have run into is that it will only return 4 rows of data even if I have more rows of ticker that are not the same as the 4 returned. I don't have any issue with the number of columns just the number of rows of data.
I have stepped through the code but I cannot seem to find the issue. I have to assume it is in the extraction of the returned JSON data.
Can you take a look at the code and see what I have overlooked? I have also attached it as a text file in case the code get scrambled in the post.
Thanks
Harry
Public Function smfGetIEXPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = "0102030405060708091011121314151617181920212223242526272829303132333435363738394041424344454647484950", _
Optional ByVal pMultiple As String = "N", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)
'----------------------------------------------------------*
' User defined function to download columns from a portfolio view on IEXTrading!
'----------------------------------------------------------*
' 2016.08.05 -- Created by Randy Harmelink (rharmelink@gmail.com)
' 2017.05.02 -- Obsoleted because portfolio table was replaced by JSON file
' 2017.06.21 -- Rewrite to extract fields from JSON file
' 2017.10.21 -- Rewrite to extract line by line instead of by field name
' 2017.11.02 -- Minor updates
' 2017.11.03 -- Fix to handle non-US currency combinations
' 2017.11.04 -- Add 52 additional fields
' 2017.11.04 -- Create list of request fields instead of asking for everything
' 2017.11.04 -- Maintain order of ticker symbol requests
' 2017.11.04 -- Add processing for EXCEL serial date/time values
' 2017.11.04 -- Divide percentage fields by 100, as needed
' 2017.11.04 -- Fix earnings dates
' 2017.11.06 -- Allow a ticker of "NONE" in first spot to bypass processing
' 2017.11.06 -- Adjust necessary date/time fields by GMT offset
' 2017.11.08 -- Fix errors on percentage adjustments when value returned is non-numeric
' 2017.11.09 -- Backed out percentage adjustments for fields 58, 61, 65, 68
' 2017.11.09 -- Fixed field list adjustment when only default fields are requested
' 2017.11.17 -- Allow a ticker symbol to be returned more than once
'----------------------------------------------------------*
' > Examples of invocations to get portfolio quotes for IBM and MMM:
'
' =smfGetYahooPortfolioView("IBM,MMM")
' =smfGetYahooPortfolioView("IBM,MMM","00010203")
' =smfGetYahooPortfolioView("IBM,MMM","0001021011",,1)
'----------------------------------------------------------*
Dim aFieldNeed() As String: aFieldNeed = Split("0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1", ",")
Dim aFieldName() As String: aFieldName = Split("--,companyName,marketcap,beta,week52high,week52low,week52change,shortInterest,shortDate,dividendRate,dividendYield,exDividendDate,latestEPS," _
& "latestEPSDate,sharesOutstanding,float,returnOnEquity,symbol,EBITDA,revenue,grossProfit,cash,debt,ttmEPS,revenuePerShare,revenuePerEmployee," _
& "peRatioHigh,peRatioLow,consensusEPS,numberOfEstimates,EPSSurpriseDollar,EPSSurprisePercent,returnOnAssets,returnOnCapital,profitMargin," _
& "priceToSales,priceToBook,day200MovingAvg,day50MovingAvg,institutionPercent,insiderPercent,shortRatio,year5ChangePercent,year2ChangePercent," _
& "year1ChangePercent,ytdChangePercent,month6ChangePercent,month3ChangePercent,month1ChangePercent,day5ChangePercent,day30ChangePer", ",")
Dim aHeading() As String: aHeading = Split("--,Company Name,Market Cap,Beta,52 Wk High,52 Wk Low,52 Wk Change,Short Interest,Short Date,Dividend Rate,Yield,Ex Div Date,EPS,EPS Date," _
& "Outstanding Shres,Float,Return On Equity,Symbol,EBITDA,Revenue,Profit,Cash,Debt,EPS ttm,Revenue Per Share,Revenue Per Employee,PE Ratio High," _
& "PE Ratio Low,Consensus EPS,Num of Est,EPS Surprise $,EPS Surprise %,ROA,ROC,Profit Margin,Price to Sales,Price to Book,200 DMA,50 DMA," _
& "Institution Percent,Insider Percent,Short Ratio,5 Year % Chg,2 Year % Chg,1 Year % Chg,YTD % Chg,6 Mon % Chg,3 Mon % Chg,1 Mon % Chg," _
& "5 Day % Chg,30 Day %", ",")
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, s1 As String
'------------------> Determine size of array to return
Dim iRows As Integer, iCols As Integer
iRows = pDim1 ' Rows
iCols = pDim2 ' Columns
If pDim1 = 0 Or pDim2 = 0 Then
If pDim1 = 0 Then iRows = 200 ' Old default
If pDim2 = 0 Then iCols = 100 ' Old default
On Error Resume Next
iRows = Application.Caller.Rows.Count
iCols = Application.Caller.Columns.Count
On Error GoTo ErrorExit
End If
'------------------> Initialize return array
ReDim vData(1 To iRows, 1 To iCols) As Variant
For i1 = 1 To iRows
For i2 = 1 To iCols
vData(i1, i2) = "" '"--"
Next i2
Next i1
'------------------> Verify item and ticker and view parameters
Dim oCell As Range, sItems As String, sTickers As String, aCols(1 To 50) As String, sFieldList As String
Dim iFind As Integer, aTickers As Variant
Select Case VarType(pItems)
Case vbString
sItems = LCase(Replace(pItems, " ", ""))
Case Is >= 8192
sItems = ""
For Each oCell In pItems
If oCell.Value > "" Then sItems = sItems & Right(LCase(Format(oCell.Value, "00")), 2)
Next oCell
Case Else
smfGetIEXPortfolioView = "Invalid items parameter: " & pItems
Exit Function
End Select
i1 = Len(sItems) / 2
If i1 < iCols Then iCols = i1
sFieldList = ","
For i1 = 1 To iCols
s1 = Mid(sItems & String$(68, "0"), 2 * i1 - 1, 2)
Select Case s1
Case "00" To "50"
aCols(i1) = CInt(s1)
If aFieldNeed(s1) <> 0 Then
iFind = InStr(sFieldList, "," & aFieldName(s1) & ",")
If iFind = 0 Then sFieldList = sFieldList & aFieldName(s1) & ","
End If
Case Else: aCols(i1) = 0
End Select
Next i1
If Len(sFieldList) > 2 Then sFieldList = Mid(sFieldList, 2, Len(sFieldList) - 2) ' Remove leading and trailing comma
Select Case VarType(pTickers)
Case vbString
sTickers = UCase(pTickers)
Case Is >= 8192
sTickers = ""
For Each oCell In pTickers
sTickers = sTickers & IIf(oCell.Value <> "", UCase(oCell.Value), "XXXXX") & ","
Next oCell
sTickers = Left(sTickers, Len(sTickers) - 1)
Case Else
smfGetIEXPortfolioView = "Invalid tickers parameter: " & pTickers
Exit Function
End Select
aTickers = Split(sTickers, ",")
'------------------> Create header if requested
If pHeader = 1 Then
For i1 = 1 To iCols
vData(1, i1) = aHeading(aCols(i1))
Next i1
End If
If aTickers(0) = "NONE" Then GoTo ErrorExit
'------------------> Extract requested data items
Dim iPtr As Long, iPos1 As Long, sData As String, sLine As String, sURL As String, v1 As Variant
sURL = "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & Replace(sTickers, ",XXXXX", "") & "&types=stats&filter=symbol," & sFieldList
iPtr = 1
sData = RCHGetWebData(sURL, iPtr, 6000)
iPos1 = InStr(2, sData, "results")
iPtr = iPtr + iPos1 + 1
sData = RCHGetWebData(sURL, iPtr, 6000)
For i2 = 1 + pHeader To iRows
iPos1 = InStr(2, sData, "{")
If iPos1 = 0 Then Exit For
iPtr = iPtr + iPos1 + 1
sData = RCHGetWebData(sURL, iPtr, 6000)
sLine = """" & smfStrExtr(sData & "}", "~", "}") & ","""
s1 = smfStrExtr(sLine & ",", """symbol"":""", """")
For i3 = 0 To UBound(aTickers)
If s1 = aTickers(i3) Then
i4 = i3 + 1 + pHeader
For i1 = 1 To iCols
v1 = smfStrExtr(sLine & ",", """" & aFieldName(aCols(i1)) & """:", ",""", 1)
If v1 = "" Then
vData(i4, i1) = "--"
Else
If Left(v1, 1) = """" Then v1 = smfStrExtr(v1 & """", """", """", 1)
Select Case 0 + aCols(i1)
Case 11
vData(i4, i1) = Mid(v1, 1, 10)
Case Else
vData(i4, i1) = v1
End Select
End If ' v1
Next i1
If pMultiple = "N" Then Exit For
End If ' s1
Next i3
Next i2
ErrorExit:
smfGetIEXPortfolioView = vData
End Function
Using your smfGetYahooPortfolioView as a template, I am trying to modify it to use IEX Trading as a backup.
The problem I have run into is that it will only return 4 rows of data even if I have more rows of ticker that are not the same as the 4 returned. I don't have any issue with the number of columns just the number of rows of data.
I have stepped through the code but I cannot seem to find the issue. I have to assume it is in the extraction of the returned JSON data.
Can you take a look at the code and see what I have overlooked? I have also attached it as a text file in case the code get scrambled in the post.
Thanks
Harry
Public Function smfGetIEXPortfolioView(ByVal pTickers As Variant, _
Optional ByVal pItems As Variant = "0102030405060708091011121314151617181920212223242526272829303132333435363738394041424344454647484950", _
Optional ByVal pMultiple As String = "N", _
Optional ByVal pHeader As Integer = 0, _
Optional ByVal pDim1 As Integer = 0, _
Optional ByVal pDim2 As Integer = 0)
'----------------------------------------------------------*
' User defined function to download columns from a portfolio view on IEXTrading!
'----------------------------------------------------------*
' 2016.08.05 -- Created by Randy Harmelink (rharmelink@gmail.com)
' 2017.05.02 -- Obsoleted because portfolio table was replaced by JSON file
' 2017.06.21 -- Rewrite to extract fields from JSON file
' 2017.10.21 -- Rewrite to extract line by line instead of by field name
' 2017.11.02 -- Minor updates
' 2017.11.03 -- Fix to handle non-US currency combinations
' 2017.11.04 -- Add 52 additional fields
' 2017.11.04 -- Create list of request fields instead of asking for everything
' 2017.11.04 -- Maintain order of ticker symbol requests
' 2017.11.04 -- Add processing for EXCEL serial date/time values
' 2017.11.04 -- Divide percentage fields by 100, as needed
' 2017.11.04 -- Fix earnings dates
' 2017.11.06 -- Allow a ticker of "NONE" in first spot to bypass processing
' 2017.11.06 -- Adjust necessary date/time fields by GMT offset
' 2017.11.08 -- Fix errors on percentage adjustments when value returned is non-numeric
' 2017.11.09 -- Backed out percentage adjustments for fields 58, 61, 65, 68
' 2017.11.09 -- Fixed field list adjustment when only default fields are requested
' 2017.11.17 -- Allow a ticker symbol to be returned more than once
'----------------------------------------------------------*
' > Examples of invocations to get portfolio quotes for IBM and MMM:
'
' =smfGetYahooPortfolioView("IBM,MMM")
' =smfGetYahooPortfolioView("IBM,MMM","00010203")
' =smfGetYahooPortfolioView("IBM,MMM","0001021011",,1)
'----------------------------------------------------------*
Dim aFieldNeed() As String: aFieldNeed = Split("0,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1,1", ",")
Dim aFieldName() As String: aFieldName = Split("--,companyName,marketcap,beta,week52high,week52low,week52change,shortInterest,shortDate,dividendRate,dividendYield,exDividendDate,latestEPS," _
& "latestEPSDate,sharesOutstanding,float,returnOnEquity,symbol,EBITDA,revenue,grossProfit,cash,debt,ttmEPS,revenuePerShare,revenuePerEmployee," _
& "peRatioHigh,peRatioLow,consensusEPS,numberOfEstimates,EPSSurpriseDollar,EPSSurprisePercent,returnOnAssets,returnOnCapital,profitMargin," _
& "priceToSales,priceToBook,day200MovingAvg,day50MovingAvg,institutionPercent,insiderPercent,shortRatio,year5ChangePercent,year2ChangePercent," _
& "year1ChangePercent,ytdChangePercent,month6ChangePercent,month3ChangePercent,month1ChangePercent,day5ChangePercent,day30ChangePer", ",")
Dim aHeading() As String: aHeading = Split("--,Company Name,Market Cap,Beta,52 Wk High,52 Wk Low,52 Wk Change,Short Interest,Short Date,Dividend Rate,Yield,Ex Div Date,EPS,EPS Date," _
& "Outstanding Shres,Float,Return On Equity,Symbol,EBITDA,Revenue,Profit,Cash,Debt,EPS ttm,Revenue Per Share,Revenue Per Employee,PE Ratio High," _
& "PE Ratio Low,Consensus EPS,Num of Est,EPS Surprise $,EPS Surprise %,ROA,ROC,Profit Margin,Price to Sales,Price to Book,200 DMA,50 DMA," _
& "Institution Percent,Insider Percent,Short Ratio,5 Year % Chg,2 Year % Chg,1 Year % Chg,YTD % Chg,6 Mon % Chg,3 Mon % Chg,1 Mon % Chg," _
& "5 Day % Chg,30 Day %", ",")
Dim i1 As Integer, i2 As Integer, i3 As Integer, i4 As Integer, s1 As String
'------------------> Determine size of array to return
Dim iRows As Integer, iCols As Integer
iRows = pDim1 ' Rows
iCols = pDim2 ' Columns
If pDim1 = 0 Or pDim2 = 0 Then
If pDim1 = 0 Then iRows = 200 ' Old default
If pDim2 = 0 Then iCols = 100 ' Old default
On Error Resume Next
iRows = Application.Caller.Rows.Count
iCols = Application.Caller.Columns.Count
On Error GoTo ErrorExit
End If
'------------------> Initialize return array
ReDim vData(1 To iRows, 1 To iCols) As Variant
For i1 = 1 To iRows
For i2 = 1 To iCols
vData(i1, i2) = "" '"--"
Next i2
Next i1
'------------------> Verify item and ticker and view parameters
Dim oCell As Range, sItems As String, sTickers As String, aCols(1 To 50) As String, sFieldList As String
Dim iFind As Integer, aTickers As Variant
Select Case VarType(pItems)
Case vbString
sItems = LCase(Replace(pItems, " ", ""))
Case Is >= 8192
sItems = ""
For Each oCell In pItems
If oCell.Value > "" Then sItems = sItems & Right(LCase(Format(oCell.Value, "00")), 2)
Next oCell
Case Else
smfGetIEXPortfolioView = "Invalid items parameter: " & pItems
Exit Function
End Select
i1 = Len(sItems) / 2
If i1 < iCols Then iCols = i1
sFieldList = ","
For i1 = 1 To iCols
s1 = Mid(sItems & String$(68, "0"), 2 * i1 - 1, 2)
Select Case s1
Case "00" To "50"
aCols(i1) = CInt(s1)
If aFieldNeed(s1) <> 0 Then
iFind = InStr(sFieldList, "," & aFieldName(s1) & ",")
If iFind = 0 Then sFieldList = sFieldList & aFieldName(s1) & ","
End If
Case Else: aCols(i1) = 0
End Select
Next i1
If Len(sFieldList) > 2 Then sFieldList = Mid(sFieldList, 2, Len(sFieldList) - 2) ' Remove leading and trailing comma
Select Case VarType(pTickers)
Case vbString
sTickers = UCase(pTickers)
Case Is >= 8192
sTickers = ""
For Each oCell In pTickers
sTickers = sTickers & IIf(oCell.Value <> "", UCase(oCell.Value), "XXXXX") & ","
Next oCell
sTickers = Left(sTickers, Len(sTickers) - 1)
Case Else
smfGetIEXPortfolioView = "Invalid tickers parameter: " & pTickers
Exit Function
End Select
aTickers = Split(sTickers, ",")
'------------------> Create header if requested
If pHeader = 1 Then
For i1 = 1 To iCols
vData(1, i1) = aHeading(aCols(i1))
Next i1
End If
If aTickers(0) = "NONE" Then GoTo ErrorExit
'------------------> Extract requested data items
Dim iPtr As Long, iPos1 As Long, sData As String, sLine As String, sURL As String, v1 As Variant
sURL = "https://api.iextrading.com/1.0/stock/market/batch?symbols=" & Replace(sTickers, ",XXXXX"
iPtr = 1
sData = RCHGetWebData(
iPos1 = InStr(2, sData, "results"
iPtr = iPtr + iPos1 + 1
sData = RCHGetWebData(
For i2 = 1 + pHeader To iRows
iPos1 = InStr(2, sData, "{"
If iPos1 = 0 Then Exit For
iPtr = iPtr + iPos1 + 1
sData = RCHGetWebData(
sLine = ""
s1 = smfStrExtr(sLine & ","
For i3 = 0 To UBound(aTickers)
If s1 = aTickers(i3) Then
i4 = i3 + 1 + pHeader
For i1 = 1 To iCols
v1 = smfStrExtr(sLine & ","
If v1 = "" Then
vData(i4, i1) = "--"
Else
If Left(v1, 1) = ""
Select Case 0 + aCols(i1)
Case 11
vData(i4, i1) = Mid(v1, 1, 10)
Case Else
vData(i4, i1) = v1
End Select
End If ' v1
Next i1
If pMultiple = "N" Then Exit For
End If ' s1
Next i3
Next i2
ErrorExit:
smfGetIEXPortfolioV
End Function
Tue Apr 30, 2019 12:17 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm not having a trouble with the number of rows here:
01 15
*Ticker Symbol* *Company Name* *Float*
MMM 3M Company $574,552,280.00
IBM International Business Machines Corporation $887,784,662.00
WMT Walmart Inc. $2,815,360,141.00
MCD McDonald's Corporation $762,127,023.00
NUE Nucor Corporation $301,296,162.00
LLY Eli Lilly and Company $966,241,363.00
MO Altria Group Inc. $1,863,620,136.00
I just took the smfGetYahooPortfolioView template, added your routine and a
reference to the add-in, then put in a number of ticker symbols, and just
changed the array-entered formula to use your routine.
Could it be a bad ticker symbol? Or did you not array-enter the formula
over enough rows? Or perhaps a different ticker symbol or a different field
has some unexpected or missing data? Without your list of ticker symbols,
it's impossible for me to say.
But you could paste the above list of ticker symbols into your
array-entered range and see if it works for all of them?
You basically did more or less the same thing I did when I adapted it to
run for BarChart, back when their JSON files didn't require credentials.
BTW, I notice most of the financial statement fields like EBITDA, Revenue,
Profit, Cash, etc. are all return zero values. In the JSON file.
On Tue, Apr 30, 2019 at 10:34 AM hlstevens772@ <hlstevens772@yahoo.com>...
wrote:
> Using your smfGetYahooPortfolioView as a template, I am trying to modify
> it to use IEX Trading as a backup.
>
> The problem I have run into is that it will only return 4 rows of data
> even if I have more rows of ticker that are not the same as the 4
> returned. I don't have any issue with the number of columns just the
> number of rows of data.
>
> I have stepped through the code but I cannot seem to find the issue. I
> have to assume it is in the extraction of the returned JSON data.
>
> Can you take a look at the code and see what I have overlooked? I have
> also attached it as a text file in case the code get scrambled in the post.
>
>
>
01 15
*Ticker Symbol* *Company Name* *Float*
MMM 3M Company $574,552,280.00
IBM International Business Machines Corporation $887,784,662.00
WMT Walmart Inc. $2,815,360,141.00
MCD McDonald's Corporation $762,127,023.00
NUE Nucor Corporation $301,296,162.00
LLY Eli Lilly and Company $966,241,363.00
MO Altria Group Inc. $1,863,620,136.00
I just took the smfGetYahooPortfolioView template, added your routine and a
reference to the add-in, then put in a number of ticker symbols, and just
changed the array-entered formula to use your routine.
Could it be a bad ticker symbol? Or did you not array-enter the formula
over enough rows? Or perhaps a different ticker symbol or a different field
has some unexpected or missing data? Without your list of ticker symbols,
it's impossible for me to say.
But you could paste the above list of ticker symbols into your
array-entered range and see if it works for all of them?
You basically did more or less the same thing I did when I adapted it to
run for BarChart, back when their JSON files didn't require credentials.
BTW, I notice most of the financial statement fields like EBITDA, Revenue,
Profit, Cash, etc. are all return zero values. In the JSON file.
On Tue, Apr 30, 2019 at 10:34 AM hlstevens772@ <hlstevens772@yahoo.com>...
wrote:
> Using your smfGetYahooPortfoli
> it to use IEX Trading as a backup.
>
> The problem I have run into is that it will only return 4 rows of data
> even if I have more rows of ticker that are not the same as the 4
> returned. I don't have any issue with the number of columns just the
> number of rows of data.
>
> I have stepped through the code but I cannot seem to find the issue. I
> have to assume it is in the extraction of the returned JSON data.
>
> Can you take a look at the code and see what I have overlooked? I have
> also attached it as a text file in case the code get scrambled in the post.
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar