[smf_addin] Digest Number 4071[1 Attachment]

EXCEL Stock Market Functions Add-in Group

11 Messages

Digest #4071

1a

Re: Is there a func to see if a stock is listed in some Index? by a.bereziuk

1b

Re: Is there a func to see if a stock is listed in some Index? by a.bereziuk

1c

Re: Is there a func to see if a stock is listed in some Index? by "Randy Harmelink" rharmelink

1d

Re: Is there a func to see if a stock is listed in some Index? by a.bereziuk

1e

Re: Is there a func to see if a stock is listed in some Index? by "Randy Harmelink" rharmelink

2a

Re: Incorrect data coming from Yahoo by stevet99uk

3a

Some code corrections / suggestions by fran0591

3b

Re: Some code corrections / suggestions by "Randy Harmelink" rharmelink

4a

Is there a way to pull in key metrics for ETFs? by ech0045

4b

Re: Is there a way to pull in key metrics for ETFs? by "Randy Harmelink" rharmelink

5a

Re: Update on Yahoo Historical Quotes (as of 2017-06-09) by eadamy

Messages

1a

Re: Is there a func to see if a stock is listed in some Index?

Mon Jun 26, 2017 3:44 am (PDT) . Posted by:

a.bereziuk

I copy the formula as is and I cannot even paste it in the excel cell. Excel shows a message "There is an error in the formula". Then Excel marks this fragment: [F7,"td",1,"] (without brackets). Something is wrong with this fragment. I have Excel 2016 and the last version of AddIn with this function:

Public Function smfGetTagContent(ByVal pURL As String, _
ByVal pTag As String, _
Optional ByVal pTags As Integer = 1, _
Optional ByVal pFind1 As String = "<", _
Optional ByVal pFind2 As String = " ", _
Optional ByVal pFind3 As String = " ", _
Optional ByVal pFind4 As String = " ", _
Optional ByVal pConv As Integer = 0, _
Optional ByVal pError As Variant = "Error", _
Optional ByVal pType As Integer = 0, _
Optional ByVal pLen As Integer = 32767) As Variant

Formula:

=smfGetTagContent("http://finviz.com/quote.ashx?t="&F7,"td",1,">Index",,,,1)

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1b

Re: Is there a func to see if a stock is listed in some Index?

Mon Jun 26, 2017 3:50 am (PDT) . Posted by:

a.bereziuk

When I paste:

=smfGetTagContent("http://finance.yahoo.com/q/ks?s=MMM","title",1)

it accepts the formula.

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1c

Re: Is there a func to see if a stock is listed in some Index?

Mon Jun 26, 2017 10:43 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Once again, I can cut and paste your formula with no problem.

I've attached an example of the formula in a workbook. Does that one work
for you?

On Mon, Jun 26, 2017 at 3:44 AM, a.bereziuk@
​...
wrote:

>
> I copy the formula as is and I cannot even paste it in the excel cell.
> Excel shows a message "There is an error in the formula". Then Excel marks
> this fragment: [F7,"td",1,"] (without brackets). Something is wrong with
> this fragment. I have Excel 2016 and the last version of AddIn with this
> function:
>
> Public Function smfGetTagContent(ByVal pURL As String, _
> ByVal pTag As String, _
> Optional ByVal pTags As Integer = 1, _
> Optional ByVal pFind1 As String = "<", _
> Optional ByVal pFind2 As String = " ", _
> Optional ByVal pFind3 As String = " ", _
> Optional ByVal pFind4 As String = " ", _
> Optional ByVal pConv As Integer = 0, _
> Optional ByVal pError As Variant = "Error", _
> Optional ByVal pType As Integer = 0, _
> Optional ByVal pLen As Integer = 32767) As Variant
>
> Formula:
>
> =smfGetTagContent("http://finviz.com/quote.ashx?t="&F7,"td",1,">Index",,,,1)
>
>
> __._,_.__
>

Attachment(s) from Randy Harmelink

1 of 1 File(s)

SMF-Example-FinViz-Index-Extraction.xls

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1d

Re: Is there a func to see if a stock is listed in some Index?

Mon Jun 26, 2017 12:25 pm (PDT) . Posted by:

a.bereziuk

Yes, it works now! But you used semicolons instead of commas ))
Strange, but this example from documentation:
=smfGetTagContent("http://finance.yahoo.com/q/ks?s=MMM","title",1)
was accepted by Excel with commas! But thanks, now it is solved.

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

1e

Re: Is there a func to see if a stock is listed in some Index?

Mon Jun 26, 2017 1:53 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I used commas, not semi-colons. Evidently, you need semi-colons as your
delimiters, so EXCEL used them when it opened the workbook.

The other example probably was accepted because there was no cell reference
in it. I doesn't work, though, because Yahoo now requires "https" instead
of "http".

On Mon, Jun 26, 2017 at 12:25 PM, a.bereziuk@
​...
wrote:

>
> Yes, it works now! But you used semicolons instead of commas ))
> Strange, but this example from documentation:
> =
> ​​
> smfGetTagContent("http://finance.yahoo.com/q/ks?s=MMM","title",1)
> was accepted by Excel with commas! But thanks, now it is solved.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (9) . Top ^

2a

Re: Incorrect data coming from Yahoo

Mon Jun 26, 2017 4:23 am (PDT) . Posted by:

stevet99uk

USING RCHGETYAHOOQUOTES. This gives errors if used overnight (in UK) but OK again by about 6am.
Anyone noticed any similar behaviour

Reply to sender . Reply to group . Reply via Web Post . All Messages (4) . Top ^

3a

Some code corrections / suggestions

Mon Jun 26, 2017 9:06 am (PDT) . Posted by:

fran0591

Dear Randy,
great work you're doing here, very impressive!
I've started skimming over your VBA code recently and wanted to share my findings with you.
Code and row positions taken from latest official release (2017-05-03).

smfConvertYahooTicker_
ROW 42
old Case Right(sTicker, 3) = ".V": sTicker = "TSE:" & Replace(sTicker, ".V", "")
new Case Right(sTicker, 2) = ".V": sTicker = "TSE:" & Replace(sTicker, ".V", "")
why:
comparing a length 3 string to a length 2 string will always return FALSE

Suggestion 1:
Change CASE statements to this scheme:
Case InStr(sTicker, ".V") > 0: sTicker = "TSE:" & Left(sTicker, Len(sTicker) - Len(".V"))

Suggestion 2:
Insert @ ROW 22:
Const sSufOB As String = ".OB"
Const sSufTO As String = ".TO"
Const sSufV As String = ".V"
Const sSufX As String = ".X"
Change code to this scheme:
Case InStr(sTicker, sSufV) > 0: sTicker = "TSE:" & Left(sTicker, Len(sTicker) - Len(sSufV))

__________________________________________________________
smfConvertData_
ROWS 14-19
old
If InStr(s1, "/") > 0 Then
Else
If s1 = "-" Then s1 = "0"
If s1 = "--" Then s1 = "0"
If s1 = "---" Then s1 = "0"
If s1 = Chr(150) Then s1 = "0"

new
If InStr(s1, "/") = 0 Then
Select Case s1
Case "-", "--", "---", Chr(150): s1 = "0"
End Select

why:
shorter, more comprehensible version

ROWS 25-36
old
Case UCase(Right(s2, 1)) = "B": s2 = Left(s2, Len(s2) - 1): nMult = 1000000
Case UCase(Right(s2, 1)) = "K": s2 = Left(s2, Len(s2) - 1): nMult = 1000
Case UCase(Right(s2, 1)) = "M": s2 = Left(s2, Len(s2) - 1): nMult = 1000
Case Right(s2, 1) = "%": s2 = Left(s2, Len(s2) - 1): nMult = 0.01
Case Right(s2, 4) = " Bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
Case Right(s2, 4) = " Mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
Case Right(s2, 4) = " bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
Case Right(s2, 4) = " mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
Case Right(s2, 5) = " Bill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000000
Case Right(s2, 5) = " Mill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000
Case Right(s2, 8) = " Billion": s2 = Left(s2, Len(s2) - 8): nMult = 1000000000
Case Right(s2, 8) = " Million": s2 = Left(s2, Len(s2) - 8): nMult = 1000000

new
Case Right(s2, 8) = " Billion": s2 = Left(s2, Len(s2) - 8): nMult = 1000000000
Case Right(s2, 8) = " Million": s2 = Left(s2, Len(s2) - 8): nMult = 1000000
Case Right(s2, 5) = " Bill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000000
Case Right(s2, 5) = " Mill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000
Case Right(s2, 4) = " Bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
Case Right(s2, 4) = " Mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
Case Right(s2, 4) = " bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
Case Right(s2, 4) = " mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
Case UCase(Right(s2, 1)) = "B": s2 = Left(s2, Len(s2) - 1): nMult = 1000000
Case UCase(Right(s2, 1)) = "K": s2 = Left(s2, Len(s2) - 1): nMult = 1000
Case UCase(Right(s2, 1)) = "M": s2 = Left(s2, Len(s2) - 1): nMult = 1000
Case Right(s2, 1) = "%": s2 = Left(s2, Len(s2) - 1): nMult = 0.01

why:
The first true Case will be executed, execution will then continue at "End Select". I've reversed the order to not replace e.g. "B" if the string indeed contains "Bil" or "Billion".
__________________________________________________________
modGetYahooHistory
ROWS 120-122
old
IIf(pStartMonth = 0, "&a=0", "&a=" & (pStartMonth - 1)) & _
IIf(pStartDay = 0, "&b=1", "&b=" & pStartDay) & _
IIf(pStartYear = 0, "&c=" & iEndYear, "&c=" & pStartYear) & _
new
"&a=" & Application.Max(0, pStartMonth - 1) & _
"b=" & Application.Max(1, pStartDay) & _
"&c=" & IIf(pStartYear = 0, iEndYear, pStartYear) & _

why:
shorter, more comprehensible version (but propably obsolete because of changed Yahoo URL constitution...)

ROW 167
old nLines = IIf(kDim1 - pNames < UBound(vLine) + pNames, kDim1 - pNames, UBound(vLine) + pNames)
new nLines = Application.Min(kDim1 - pNames, UBound(vLine) + pNames)

why:
shorter, more comprehensible version

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

3b

Re: Some code corrections / suggestions

Mon Jun 26, 2017 10:34 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Thanks for reviewing the code. I did make the length correction in
smfConvertYahooTicker(). That function has been idle for a long time, since
it so rarely seems to come up. For me, anyway.

For smfConvertData(), I don't see the point of the "B"/"M"/"K" suffixes
being moved. They would never be true on the other cases, because of the
lengths being used? Or am I not seeing it right?

I've always had a bias against using the Application.Min() and
Application.Max(). Not sure why.I vaguely recall seeing something that said
interfacing with Application functions slows down the code, in some version
of VBA? Not sure.

On Mon, Jun 26, 2017 at 8:14 AM, fran0591@
​...
wrote:

>
> Dear Randy,
> great work you're doing here, very impressive!
> I've started skimming over your VBA code recently and wanted to share my
> findings with you.
> Code and row positions taken from latest official release (2017-05-03).
>
> smfConvertYahooTicker_
> ROW 42
> old Case Right(sTicker, 3) = ".V": sTicker = "TSE:" & Replace(sTicker,
> ".V", "")
> new Case Right(sTicker, 2) = ".V": sTicker = "TSE:" & Replace(sTicker,
> ".V", "")
> why:
> comparing a length 3 string to a length 2 string will always return FALSE
>
> Suggestion 1:
> Change CASE statements to this scheme:
> Case InStr(sTicker, ".V") > 0: sTicker = "TSE:" & Left(sTicker,
> Len(sTicker) - Len(".V"))
>
> Suggestion 2:
> Insert @ ROW 22:
> Const sSufOB As String = ".OB"
> Const sSufTO As String = ".TO"
> Const sSufV As String = ".V"
> Const sSufX As String = ".X"
> Change code to this scheme:
> Case InStr(sTicker, sSufV) > 0: sTicker = "TSE:" & Left(sTicker,
> Len(sTicker) - Len(sSufV))
>
> __________________________________________________________
> ________________________________________
> smfConvertData_
> ROWS 14-19
> old
> If InStr(s1, "/") > 0 Then
> Else
> If s1 = "-" Then s1 = "0"
> If s1 = "--" Then s1 = "0"
> If s1 = "---" Then s1 = "0"
> If s1 = Chr(150) Then s1 = "0"
>
> new
> If InStr(s1, "/") = 0 Then
> Select Case s1
> Case "-", "--", "---", Chr(150): s1 = "0"
> End Select
>
> why:
> shorter, more comprehensible version
>
> ROWS 25-36
> old
> Case UCase(Right(s2, 1)) = "B": s2 = Left(s2, Len(s2) - 1): nMult = 1000000
> Case UCase(Right(s2, 1)) = "K": s2 = Left(s2, Len(s2) - 1): nMult = 1000
> Case UCase(Right(s2, 1)) = "M": s2 = Left(s2, Len(s2) - 1): nMult = 1000
> Case Right(s2, 1) = "%": s2 = Left(s2, Len(s2) - 1): nMult = 0.01
> Case Right(s2, 4) = " Bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
> Case Right(s2, 4) = " Mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
> Case Right(s2, 4) = " bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
> Case Right(s2, 4) = " mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
> Case Right(s2, 5) = " Bill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000000
> Case Right(s2, 5) = " Mill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000
> Case Right(s2, 8) = " Billion": s2 = Left(s2, Len(s2) - 8): nMult =
> 1000000000
> Case Right(s2, 8) = " Million": s2 = Left(s2, Len(s2) - 8): nMult = 1000000
>
> new
> Case Right(s2, 8) = " Billion": s2 = Left(s2, Len(s2) - 8): nMult =
> 1000000000
> Case Right(s2, 8) = " Million": s2 = Left(s2, Len(s2) - 8): nMult = 1000000
> Case Right(s2, 5) = " Bill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000000
> Case Right(s2, 5) = " Mill": s2 = Left(s2, Len(s2) - 5): nMult = 1000000
> Case Right(s2, 4) = " Bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
> Case Right(s2, 4) = " Mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
> Case Right(s2, 4) = " bil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000000
> Case Right(s2, 4) = " mil": s2 = Left(s2, Len(s2) - 4): nMult = 1000000
> Case UCase(Right(s2, 1)) = "B": s2 = Left(s2, Len(s2) - 1): nMult = 1000000
> Case UCase(Right(s2, 1)) = "K": s2 = Left(s2, Len(s2) - 1): nMult = 1000
> Case UCase(Right(s2, 1)) = "M": s2 = Left(s2, Len(s2) - 1): nMult = 1000
> Case Right(s2, 1) = "%": s2 = Left(s2, Len(s2) - 1): nMult = 0.01
>
> why:
> The first true Case will be executed, execution will then continue at "End
> Select". I've reversed the order to not replace e.g. "B" if the string
> indeed contains "Bil" or "Billion".
> __________________________________________________________
> ________________________________________
> modGetYahooHistory
> ROWS 120-122
> old
> IIf(pStartMonth = 0, "&a=0", "&a=" & (pStartMonth - 1)) & _
> IIf(pStartDay = 0, "&b=1", "&b=" & pStartDay) & _
> IIf(pStartYear = 0, "&c=" & iEndYear, "&c=" & pStartYear) & _
> new
> "&a=" & Application.Max(0, pStartMonth - 1) & _
> "b=" & Application.Max(1, pStartDay) & _
> "&c=" & IIf(pStartYear = 0, iEndYear, pStartYear) & _
>
> why:
> shorter, more comprehensible version (but propably obsolete because of
> changed Yahoo URL constitution...)
>
> ROW 167
> old nLines = IIf(kDim1 - pNames < UBound(vLine) + pNames, kDim1 -
> pNames, UBound(vLine) + pNames)
> new nLines = Application.Min(kDim1 - pNames, UBound(vLine) + pNames)
>
> why:
> shorter, more comprehensible version
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

4a

Is there a way to pull in key metrics for ETFs?

Mon Jun 26, 2017 9:08 am (PDT) . Posted by:

ech0045

Hello, I am trying to pull in key metrics for a few ETFs and have not been able to find a function that can do this.


The metrics I am looking for are:


1) P/E
2) EV/EBITDA

3) Div yield
4) Beta
5) Short Interest


The ETFS are:
IYH IYK KXI IXC IYG IYW IYJ IYM IYZ IYR IDU
SPY IWM

Thank you,
Eric

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

4b

Re: Is there a way to pull in key metrics for ETFs?

Mon Jun 26, 2017 10:32 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Before you would know which function to use, you'd need to have a source of
the metrics.

Google has a few of them. For example:

=smfGetTagContent("https://www.google.com/finance?q=KXI","td",1,">P/E",,,,1)
=smfGetTagContent("https://www.google.com/finance?q=KXI
","td",1,">Beta",,,,1)
=smfConvertData(smfStrExtr(smfGetTagContent("
https://www.google.com/finance?q=KXI","td",1,">Div/Yield"),"/","~"))

On Mon, Jun 26, 2017 at 8:33 AM, ericchardy@
​...
wrote:

> Hello, I am trying to pull in key metrics for a few ETFs and have not been
> able to find a function that can do this.
>
> The metrics I am looking for are:
>
> 1) P/E
>
> 2) EV/EBITDA
>
> 3) Div yield
>
> 4) Beta
>
> 5) Short Interest
>
> The ETFS are:
> IYH
> IYK
> KXI
> IXC
> IYG
> IYW
> IYJ
> IYM
> IYZ
> IYR
> IDU
>
> SPY
> IWM
>
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

5a

Re: Update on Yahoo Historical Quotes (as of 2017-06-09)

Mon Jun 26, 2017 7:00 pm (PDT) . Posted by:

eadamy

Randy, I am trying to stay on top of the revisions. I see reference to the new smf file dated June 9 located at

http://ogres-crypt.com/SMF/ Works-In-Progress/RCH_Stock_ Market_Functions.xla http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions.xla

I downloaded that file; however when I go to

http://ogres-crypt.com/SMF/Works-In-Progress/ http://ogres-crypt.com/SMF/Works-In-Progress/

I do not see any of the RCH_Stock_Market_Functions.xla files listed. Is there another folder I need to check for various versions?

Earl Adamy

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 12

• Privacy • Unsubscribe • Terms of Use