15 Messages
Digest #4218
1b
Re: alternative to RCHgetYahooQuotes for Last Trade and Previous Clo by "Randy Harmelink" rharmelink
1c
Re: alternative to RCHgetYahooQuotes for Last Trade and Previous Clo by "Randy Harmelink" rharmelink
Messages
Tue Nov 7, 2017 10:01 am (PST) . Posted by:
a.bereziuk
Now it looks so. 1 hour ago I had all data.
Attachment(s) from
1 of 1 Photo(s)
Tue Nov 7, 2017 10:18 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Right now, the only documentation is the cited example on the blog, and the
example worksheet.
Array-entered ranges have limitations. You can't sort within them and you
can't delete or insert columns or rows within them. You can't change any
values within them.
You didn't give any specific examples of "not working smooth", so I've
nothing to investigate?
If you want to change the size of the array, you'd need to re-enter it over
the new range size:
"To array-enter a formula in EXCEL, first highlight the range of cells
where you would like the returned data to appear -- the number of rows for
the range should be AT LEAST the number of ticker symbols you are
requesting from the function, while the number of columns for the range
should be AT LEAST the number of data items you are requesting for each
ticker symbol from the function. Next, enter your formula and then press
Ctrl-Shift-Enter."
On Tue, Nov 7, 2017 at 10:55 AM, a.bereziuk@
...
wrote:
>
> I am now testing the fresh example with smfGetYahooPortfolioView(). But it
> is not working smooth. Sometimes it does not deliver some ticker data.
> Seems that Yahoo has problems with provision of requested data.
> Besides that there is no documentation on this function. I don't know how
> to change the array. How to remove some rows?
>
example worksheet.
Array-entered ranges have limitations. You can't sort within them and you
can't delete or insert columns or rows within them. You can't change any
values within them.
You didn't give any specific examples of "not working smooth"
nothing to investigate?
If you want to change the size of the array, you'd need to re-enter it over
the new range size:
"To array-enter a formula in EXCEL, first highlight the range of cells
where you would like the returned data to appear -- the number of rows for
the range should be AT LEAST the number of ticker symbols you are
requesting from the function, while the number of columns for the range
should be AT LEAST the number of data items you are requesting for each
ticker symbol from the function. Next, enter your formula and then press
Ctrl-Shift-Enter.
On Tue, Nov 7, 2017 at 10:55 AM, a.bereziuk@
...
wrote:
>
> I am now testing the fresh example with smfGetYahooPortfoli
> is not working smooth. Sometimes it does not deliver some ticker data.
> Seems that Yahoo has problems with provision of requested data.
> Besides that there is no documentation on this function. I don't know how
> to change the array. How to remove some rows?
>
Tue Nov 7, 2017 10:32 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Working fine here right now.
Yahoo does return currencies data first, so my assumption would be that you
are running into some kind of error either during that first entry item.
When an error occurs, it just returns what it has at that time. It looks
like it stopped processing at the calculation of % change.
Hmm. In one of my other uses of JSON data, I've seen "unch" in the file
when something is unchanged. That would cause an error, as the add-in does
divide some percentages by 100 so they were numerically the correct
magnitude. That would fit all your symptoms. I can add an error handler on
that situation.
On Tue, Nov 7, 2017 at 11:01 AM, a.bereziuk@
...
wrote:
>
> Now it looks so. 1 hour ago I had all data. ._,_.___
>
> Attachment(s) from a.bereziuk@yahoo.de [smf_addin] | View attachments on
> the web
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJybWtmZmVmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDdmlld09uV2ViBHN0aW1lAzE1MTAwNzc3MDU->
>
> 1 of 1 Photo(s)
> [image: 2017-11-07_19-59-52.jpg]
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJucWZqNXJmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDcGhvdG8Ec3RpbWUDMTUxMDA3NzcwNQ--?itemid=1774230244>
> 2017-11-07_19-59-52.jpg
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJucWZqNXJmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDcGhvdG8Ec3RpbWUDMTUxMDA3NzcwNQ--?itemid=1774230244>
> ------------------------------
>
Yahoo does return currencies data first, so my assumption would be that you
are running into some kind of error either during that first entry item.
When an error occurs, it just returns what it has at that time. It looks
like it stopped processing at the calculation of % change.
Hmm. In one of my other uses of JSON data, I've seen "unch" in the file
when something is unchanged. That would cause an error, as the add-in does
divide some percentages by 100 so they were numerically the correct
magnitude. That would fit all your symptoms. I can add an error handler on
that situation.
On Tue, Nov 7, 2017 at 11:01 AM, a.bereziuk@
...
wrote:
>
> Now it looks so. 1 hour ago I had all data. ._,_.___
>
> Attachment(s) from a.bereziuk@yahoo.de [smf_addin] | View attachments on
> the web
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJybWtmZmVmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDdmlld09uV2ViBHN0aW1lAzE1MTAwNzc3MDU->
>
> 1 of 1 Photo(s)
> [image: 2017-11-07_19-59-52.jpg]
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJucWZqNXJmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDcGhvdG8Ec3RpbWUDMTUxMDA3NzcwNQ--?itemid=1774230244>
> 2017-11-07_19-59-52.jpg
> <https://groups.yahoo.com/neo/groups/smf_addin/attachments/383858072;_ylc=X3oDMTJucWZqNXJmBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDYXR0YWNobWVudARzbGsDcGhvdG8Ec3RpbWUDMTUxMDA3NzcwNQ--?itemid=1774230244>
> ------------
>
Tue Nov 7, 2017 10:02 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Keep in mind that when working properly, the add-in can only return the
data provided to it.
Re: WFCPL. If I go to the Yahoo quotes page, it cites a current price as
$1321 and says, "As of September 26 2:05PM EDT". That's also the last date
available on Yahoo's historical quotes page.
Re: BRK-B Book. Element #969 returns the same 187,767 value for me, which
matches the value on Yahoo's Key Statistics page. How are you getting the
121.84?
Re: sWebCache:
I had forgotten about that variable. It was an experiment. It's not
reliable. It turns itself back to "Y" on the FIRST web page it is
requesting in a recalculation. It also causes a VBA error if only part of
an array-entered range is selected.
So, it will work well as long as your range only needs a single web page
refreshed, and the selection contains all of a selected formula. Your
special routine appears to fit that criteria?
Hmm. Now that I think of it, the setting back to "Y" probably should be in
a "Calculate" event of some type. Not sure if that would work or not. It
still doesn't address the issue of the selection being incomplete. I
suppose I could do an "On Error Resume Next" to skip the error rather than
let VBA flag it.
Maybe something to go back and look at again, with a fresh perspective...
On Tue, Nov 7, 2017 at 10:22 AM, tamurphy@cableone.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm using the 2017.11.04 version.
>
> Re: WFCPL, The problem is that the last trade date I'm getting is
> 09/26/17, and the corresponding price is, as you show, $1321. That price is
> incorrect for the last trade date as of today. The dates and prices for
> other tickers appear to be correct.
>
>
> Re: BRK-B Book, I believe for all other tickers Item #41 returns
> Book/Share, whereas for BRK it returns total Book value. For example, WMT
> shows as 25.52 both here and when using smfGetElement, whereas BRK-B shows
> 182767 when using GetPortfolioView and 121.84 when using GetElement.
>
> Re: selectively recalculating a smfGetPortfolioValue array without using
> smfForceRecalculation. I've managed to make this work by creating a public
> procedure that does essentially what your private procedure smfMenuRecalculateSelection()
> does. The only difference is that it refers to a named range [pvTblData]
> rather than to a selection...
>
> Public Sub RecalcPortView()
> sWebCache = "N" ' this is a SMF public variable
> With [pvTblData]
> .Dirty
> .Calculate
> End With
> End Sub
>
> According to the Time item, smfGetPortfolioView is now retrieving
> practically real-time quotes in a fraction of the time previously required
> using smfGetYahooQuotes.
>
> Thanks again for your most valuable assistance over so many years, Randy.
> My Portfolio Manager is heavily dependent upon the fine utilities you've
> created.
>
>
data provided to it.
Re: WFCPL. If I go to the Yahoo quotes page, it cites a current price as
$1321 and says, "As of September 26 2:05PM EDT". That's also the last date
available on Yahoo's historical quotes page.
Re: BRK-B Book. Element #969 returns the same 187,767 value for me, which
matches the value on Yahoo's Key Statistics page. How are you getting the
121.84?
Re: sWebCache:
I had forgotten about that variable. It was an experiment. It's not
reliable. It turns itself back to "Y" on the FIRST web page it is
requesting in a recalculation. It also causes a VBA error if only part of
an array-entered range is selected.
So, it will work well as long as your range only needs a single web page
refreshed, and the selection contains all of a selected formula. Your
special routine appears to fit that criteria?
Hmm. Now that I think of it, the setting back to "Y" probably should be in
a "Calculate" event of some type. Not sure if that would work or not. It
still doesn't address the issue of the selection being incomplete. I
suppose I could do an "On Error Resume Next" to skip the error rather than
let VBA flag it.
Maybe something to go back and look at again, with a fresh perspective...
On Tue, Nov 7, 2017 at 10:22 AM, tamurphy@cableone.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm using the 2017.11.04 version.
>
> Re: WFCPL, The problem is that the last trade date I'm getting is
> 09/26/17, and the corresponding price is, as you show, $1321. That price is
> incorrect for the last trade date as of today. The dates and prices for
> other tickers appear to be correct.
>
>
> Re: BRK-B Book, I believe for all other tickers Item #41 returns
> Book/Share, whereas for BRK it returns total Book value. For example, WMT
> shows as 25.52 both here and when using smfGetElement, whereas BRK-B shows
> 182767 when using GetPortfolioView and 121.84 when using GetElement.
>
> Re: selectively recalculating a smfGetPortfolioValu
> smfForceRecalculati
> procedure that does essentially what your private procedure smfMenuRecalculateS
> does. The only difference is that it refers to a named range [pvTblData]
> rather than to a selection...
>
> Public Sub RecalcPortView(
> sWebCache = "N" ' this is a SMF public variable
> With [pvTblData]
> .Dirty
> .Calculate
> End With
> End Sub
>
> According to the Time item, smfGetPortfolioView is now retrieving
> practically real-time quotes in a fraction of the time previously required
> using smfGetYahooQuotes.
>
> Thanks again for your most valuable assistance over so many years, Randy.
> My Portfolio Manager is heavily dependent upon the fine utilities you've
> created.
>
>
Tue Nov 7, 2017 10:56 am (PST) . Posted by:
eremon9
Re: BRK-B Book. My apologies for not having noticed this earlier. I've employed a workaround for smfGetElement() to accommodate the same problem there. Instead of calling your function directly, I've inserted this subroutine:
Public Function GetElement(ByVal sTicker As String, _
Optional ByVal nElement As Integer, _
Optional ByVal dMultiple As Double = 1) As Variant
Dim vRetVal As Variant
On Error GoTo ErrorExit
Application.ScreenUpdating = False
vRetVal = RCHGetElementNumber(sTicker, nElement)
If vRetVal = "Error" Or vRetVal = "N/A" Or IsError(vRetVal) Or vRetVal = 0 Then
GetElement = ""
ElseIf IsNumeric(vRetVal) Then
GetElement = vRetVal * dMultiple
Else
GetElement = vRetVal
End If
Exit Function
ErrorExit:
GetElement = ""
End Function
And in the case of BRK-B my call is:
= GetElement(Ticker, LookupNum, 1 / 1500)
where LookupNum is 969, and 1/1500 is an adjustment for number of B shares per A share. No other tickers require the dMultiple optional argument.
Re: WFCPL. At the moment I'm able to get the proper latest price (checked with Google Finance) using either:
= smfGetYahooHistory(Ticker, DateCurrent, DateCurrent, "d", "u", 0)
or
= smfPricesByDates(Ticker, DateCurrent)
However, both of these have returned errors at different times of late. So my present somewhat ridiculous formula for retrieving this particular price is:
= IFERROR(smfGetYahooHistory("WFC-PL", DateCurrent, DateCurrent, "d", "u", 0),
IFERROR(smfPricesByDates("WFC-PL", DateCurrent),
OFFSET(pvTopLeft, MATCH("WFCPL", pvTickers, 0), MATCH(15, pvElements, 0))))
where the final OFFSET function points to the cell containing LastPrice for WFCPL in the smfGetPortfolioView() array, invoked as a last resort in the event the previous two approaches have failed.
I'm most receptive to any suggestions for improving upon this rather ugly kludge.
Public Function GetElement(ByVal sTicker As String, _
Optional ByVal nElement As Integer, _
Optional ByVal dMultiple As Double = 1) As Variant
Dim vRetVal As Variant
On Error GoTo ErrorExit
Application.
vRetVal = RCHGetElementNumber
If vRetVal = "Error" Or vRetVal = "N/A" Or IsError(vRetVal) Or vRetVal = 0 Then
GetElement = ""
ElseIf IsNumeric(vRetVal) Then
GetElement = vRetVal * dMultiple
Else
GetElement = vRetVal
End If
Exit Function
ErrorExit:
GetElement = ""
End Function
And in the case of BRK-B my call is:
= GetElement(Ticker, LookupNum, 1 / 1500)
where LookupNum is 969, and 1/1500 is an adjustment for number of B shares per A share. No other tickers require the dMultiple optional argument.
Re: WFCPL. At the moment I'm able to get the proper latest price (checked with Google Finance) using either:
= smfGetYahooHistory(
or
= smfPricesByDates(
However, both of these have returned errors at different times of late. So my present somewhat ridiculous formula for retrieving this particular price is:
= IFERROR(smfGetYahoo
IFERROR(smfPricesBy
OFFSET(pvTopLeft, MATCH("
where the final OFFSET function points to the cell containing LastPrice for WFCPL in the smfGetPortfolioView
I'm most receptive to any suggestions for improving upon this rather ugly kludge.
Tue Nov 7, 2017 11:11 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, handling such things piecemeal would be impossible for the
add-in. Those are issues that need to be addressed at the data provider
level. When I run into unusual data or missing data on GuruFocus, I just
issue a ticket. It's usually corrected quickly. Yahoo isn't responsive to
such requests.
As far as WFCPL, why aren't you just using the symbol that Yahoo uses,
WFC-PL?
On Tue, Nov 7, 2017 at 11:56 AM, tamurphy@
...
wrote:
>
> Re: BRK-B Book. My apologies for not having noticed this earlier. I've
> employed a workaround for smfGetElement() to accommodate the same problem
> there. Instead of calling your function directly, I've inserted this
> subroutine:
>
>
> Public Function GetElement(ByVal sTicker As String, _
> Optional ByVal nElement As Integer, _
> Optional ByVal dMultiple As Double = 1) As
> Variant
> Dim vRetVal As Variant
>
> On Error GoTo ErrorExit
>
> Application.ScreenUpdating = False
>
> vRetVal = RCHGetElementNumber(sTicker, nElement)
>
> If vRetVal = "Error" Or vRetVal = "N/A" Or IsError(vRetVal) Or vRetVal
> = 0 Then
> GetElement = ""
> ElseIf IsNumeric(vRetVal) Then
> GetElement = vRetVal * dMultiple
> Else
> GetElement = vRetVal
> End If
>
> Exit Function
>
> ErrorExit:
> GetElement = ""
> End Function
>
>
> And in the case of BRK-B my call is:
>
> = GetElement(Ticker, LookupNum, 1 / 1500)
>
> where LookupNum is 969, and 1/1500 is an adjustment for number of B shares
> per A share. No other tickers require the dMultiple optional argument.
>
>
> Re: WFCPL. At the moment I'm able to get the proper latest price (checked
> with Google Finance) using either:
>
> = smfGetYahooHistory(Ticker, DateCurrent, DateCurrent, "d", "u", 0)
> or
> = smfPricesByDates(Ticker, DateCurrent)
>
> However, both of these have returned errors at different times of late. So
> my present somewhat ridiculous formula for retrieving this particular price
> is:
>
> = IFERROR(smfGetYahooHistory("WFC-PL", DateCurrent, DateCurrent,
> "d", "u", 0),
> IFERROR(smfPricesByDates("WFC-PL", DateCurrent),
> OFFSET(pvTopLeft, MATCH("WFCPL", pvTickers, 0), MATCH(15,
> pvElements, 0))))
>
> where the final OFFSET function points to the cell containing LastPrice
> for WFCPL in the smfGetPortfolioView() array, invoked as a last resort in
> the event the previous two approaches have failed.
>
> I'm most receptive to any suggestions for improving upon this rather ugly
> kludge.
>
>
add-in. Those are issues that need to be addressed at the data provider
level. When I run into unusual data or missing data on GuruFocus, I just
issue a ticket. It's usually corrected quickly. Yahoo isn't responsive to
such requests.
As far as WFCPL, why aren't you just using the symbol that Yahoo uses,
WFC-PL?
On Tue, Nov 7, 2017 at 11:56 AM, tamurphy@
...
wrote:
>
> Re: BRK-B Book. My apologies for not having noticed this earlier. I've
> employed a workaround for smfGetElement(
> there. Instead of calling your function directly, I've inserted this
> subroutine:
>
>
> Public Function GetElement(ByVal sTicker As String, _
> Optional ByVal nElement As Integer, _
> Optional ByVal dMultiple As Double = 1) As
> Variant
> Dim vRetVal As Variant
>
> On Error GoTo ErrorExit
>
> Application.
>
> vRetVal = RCHGetElementNumber
>
> If vRetVal = "Error" Or vRetVal = "N/A" Or IsError(vRetVal) Or vRetVal
> = 0 Then
> GetElement = ""
> ElseIf IsNumeric(vRetVal) Then
> GetElement = vRetVal * dMultiple
> Else
> GetElement = vRetVal
> End If
>
> Exit Function
>
> ErrorExit:
> GetElement = ""
> End Function
>
>
> And in the case of BRK-B my call is:
>
> = GetElement(Ticker, LookupNum, 1 / 1500)
>
> where LookupNum is 969, and 1/1500 is an adjustment for number of B shares
> per A share. No other tickers require the dMultiple optional argument.
>
>
> Re: WFCPL. At the moment I'm able to get the proper latest price (checked
> with Google Finance) using either:
>
> = smfGetYahooHistory(
> or
> = smfPricesByDates(
>
> However, both of these have returned errors at different times of late. So
> my present somewhat ridiculous formula for retrieving this particular price
> is:
>
> = IFERROR(smfGetYahoo
> "d"
> IFERROR(smfPricesBy
> OFFSET(pvTopLeft, MATCH("
> pvElements, 0))))
>
> where the final OFFSET function points to the cell containing LastPrice
> for WFCPL in the smfGetPortfolioView
> the event the previous two approaches have failed.
>
> I'm most receptive to any suggestions for improving upon this rather ugly
> kludge.
>
>
Tue Nov 7, 2017 12:09 pm (PST) . Posted by:
eremon9
I was sure I'd tried WFC-PL initially, as that's what I use for other calls in my Port Manager, and couldn't get any data. I now see that WFC-PL appears to pull the correct results. What a pain! Sorry for the confusion, however it may have originated.
Tue Nov 7, 2017 10:55 am (PST) . Posted by:
"Randy Harmelink" rharmelink
(1) Something similar:
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
","div",-1,"vkey=""LastPrice""",,,,1)
(2) I'm not aware of such quotes by ticker symbol on Morningstar. But I
don't use them either.
On Tue, Nov 7, 2017 at 6:30 AM, armando@
...
wrote:
> Thanks you very much for your hint on how to get ETF last price from
> Morningstar.
>
> I have two follow up questions:
>
> (1) Is there a way to get Last Price for Stocks from Morningstar?
>
> (2) I used to get US 30-year, 10-year and 5-year yields from Yahoo
> (respectively
>
> ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from
> Morningstar?
>
>
>
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
","
(2) I'm not aware of such quotes by ticker symbol on Morningstar. But I
don't use them either.
On Tue, Nov 7, 2017 at 6:30 AM, armando@
...
wrote:
> Thanks you very much for your hint on how to get ETF last price from
> Morningstar.
>
> I have two follow up questions:
>
> (1) Is there a way to get Last Price for Stocks from Morningstar?
>
> (2) I used to get US 30-year, 10-year and 5-year yields from Yahoo
> (respectively
>
> ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from
> Morningstar?
>
>
>
Thanks Randy.
I use them (US 30-year, 10-year and 5-year yields) to calculate
my portfolio's SHARPE and have a sense of My Actual Yield versus
risk-less yield.
BARCHART uses the $ sign rather than the ^ sign.
Thanks again.
Armando
-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Nov 7, 2017 4:55 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] LAST PRICE FROM MORNINGSTAR
(1) Something similar:
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?\
t=MMM <http://quotes.morningstar.com/stockq/c-header?t=MMM>
","div",-1,"vkey=""LastPrice""",,,,1)
(2) I'm not aware of such quotes by ticker symbol on Morningstar.
But I don't use them either.
On Tue, Nov 7, 2017 at 6:30 AM, armando@... wrote:
Thanks you very much for your hint on how to get ETF last price
from Morningstar.
I have two follow up questions:
(1) Is there a way to get Last Price for Stocks from Morningstar?
(2) I used to get US 30-year, 10-year and 5-year yields from
Yahoo (respectively
^TYX, ^TNX, and ^FVX). Is there a way to get
something similar from Morningstar?
I use them (US 30-year, 10-year and 5-year yields) to calculate
my portfolio's SHARPE and have a sense of My Actual Yield versus
risk-less yield.
BARCHART uses the $ sign rather than the ^ sign.
Thanks again.
Armando
-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Nov 7, 2017 4:55 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] LAST PRICE FROM MORNINGSTAR
(1) Something similar:
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?\
t=MMM <http://quotes.morningstar.com/stockq/c-header?t=MMM>
","
(2) I'm not aware of such quotes by ticker symbol on Morningstar.
But I don't use them either.
On Tue, Nov 7, 2017 at 6:30 AM, armando@... wrote:
Thanks you very much for your hint on how to get ETF last price
from Morningstar.
I have two follow up questions:
(1) Is there a way to get Last Price for Stocks from Morningstar?
(2) I used to get US 30-year, 10-year and 5-year yields from
Yahoo (respectively
^TYX, ^TNX, and ^FVX). Is there a way to get
something similar from Morningstar?
Tue Nov 7, 2017 1:45 pm (PST) . Posted by:
"Marco Deen" marco.deen
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
","div",-1,"vkey=""LastPrice""",,,,1)
How would I use this formula if I want it to refer to cell A1?
On Tue, Nov 7, 2017 at 2:09 PM, armando@pipeline.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> Thanks Randy.
>
> I use them (US 30-year, 10-year and 5-year yields) to calculate my
> portfolio's SHARPE and have a sense of My Actual Yield versus risk-less
> yield.
>
> BARCHART uses the $ sign rather than the ^ sign.
>
> Thanks again.
>
> Armando
>
> -----Original Message-----
> From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
> Sent: Nov 7, 2017 4:55 PM
> To: smf_addin@yahoogroups.com
> Subject: Re: [smf_addin] LAST PRICE FROM MORNINGSTAR
>
>
>
> (1) Something similar:
>
> =smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
> ","div",-1,"vkey=""LastPrice""",,,,1)
>
> (2) I'm not aware of such quotes by ticker symbol on Morningstar. But I
> don't use them either.
>
> On Tue, Nov 7, 2017 at 6:30 AM, armando@
> ...
> wrote:
>
>> Thanks you very much for your hint on how to get ETF last price from
>> Morningstar.
>>
>> I have two follow up questions:
>>
>> (1) Is there a way to get Last Price for Stocks from Morningstar?
>>
>> (2) I used to get US 30-year, 10-year and 5-year yields from Yahoo
>> (respectively
>>
>> ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from
>> Morningstar?
>>
>>
>>
>
>
>
>
","div",-1,"vkey=""LastPrice""",,,,1)
How would I use this formula if I want it to refer to cell A1?
On Tue, Nov 7, 2017 at 2:09 PM, armando@pipeline.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> Thanks Randy.
>
> I use them (US 30-year, 10-year and 5-year yields) to calculate my
> portfolio's SHARPE and have a sense of My Actual Yield versus risk-less
> yield.
>
> BARCHART uses the $ sign rather than the ^ sign.
>
> Thanks again.
>
> Armando
>
> -----Original Message-----
> From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
> Sent: Nov 7, 2017 4:55 PM
> To: smf_addin@yahoogroups.com
> Subject: Re: [smf_addin] LAST PRICE FROM MORNINGSTAR
>
>
>
> (1) Something similar:
>
> =smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
> ","
>
> (2) I'm not aware of such quotes by ticker symbol on Morningstar. But I
> don't use them either.
>
> On Tue, Nov 7, 2017 at 6:30 AM, armando@
> ...
> wrote:
>
>> Thanks you very much for your hint on how to get ETF last price from
>> Morningstar.
>>
>> I have two follow up questions:
>>
>> (1) Is there a way to get Last Price for Stocks from Morningstar?
>>
>> (2) I used to get US 30-year, 10-year and 5-year yields from Yahoo
>> (respectively
>>
>> ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from
>> Morningstar?
>>
>>
>>
>
>
>
>
Tue Nov 7, 2017 1:50 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Just the plain old EXCEL concatenation:
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=
"&B5,"div",-1,"vkey=""LastPrice""",,,,1)
On Tue, Nov 7, 2017 at 2:45 PM, Marco Deen marco.deen@
...
wrote:
>
> =
>
> smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
> ","div",-1,"vkey=""LastPrice""",,,,1)
>
> How would I use this formula if I want it to refer to cell A1?
>
>
>
=smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=
"&B5,"div",-1,"vkey=""LastPrice""",,,,1)
On Tue, Nov 7, 2017 at 2:45 PM, Marco Deen marco.deen@
...
wrote:
>
> =
>
> smfGetTagContent("http://quotes.morningstar.com/stockq/c-header?t=MMM
> ","
>
> How would I use this formula if I want it to refer to cell A1?
>
>
>
Tue Nov 7, 2017 2:36 pm (PST) . Posted by:
dogcocoa2003
Probably a year or more ago, I installed the SMF Add-In and entered symbols I wanted prices on in an example Excel SS named "1000 input tickers with RCHGetYahooQuotes function". After some startup issues, this SS worked great till the recent changes within Yahoo. I downloaded the most recent SMF Add-In and also downloaded a different new SS "smfGetPortfolioView Example" but haven't been able to get it to update the data. Also, when I input a new symbol on the SS, it causes all the cells to be filled with #NAME?
Can someone help me with simple directions to help me 1)be able to continue to use the "1000 input tickers" SS or 2)determine what to do to get current data from the new "smfGetPortfolio" example SS and be able to enter and retrieve EOD price data for roughly 20 stocks.
Thanks,
Wayne
Can someone help me with simple directions to help me 1)be able to continue to use the "1000 input tickers" SS or 2)determine what to do to get current data from the new "smfGetPortfol
Thanks,
Wayne
Tue Nov 7, 2017 2:43 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
If you're getting #NAME? errors, it means the add-in either isn't
installed, or you've installed it in a different location than I have. If
the latter, you can just run the smfFixLinks macro to fix the location
errors.
To verify whether the add-in is installed, you can do this in a new
worksheet:
=RCHGetElementNumber("Version")
If you still have problems, let me know what that function returned.
On Tue, Nov 7, 2017 at 3:18 PM, wayne-moore@tx.rr.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Probably a year or more ago, I installed the SMF Add-In
> and entered symbols I wanted prices on in an example Excel SS named "1000
> input tickers with RCHGetYahooQuotes function". After some startup issues,
> this SS worked great till the recent changes within Yahoo. I downloaded the
> most recent SMF Add-In and also downloaded a different new SS
> "smfGetPortfolioView Example" but haven't been able to get it to update the
> data. Also, when I input a new symbol on the SS, it causes all the cells to
> be filled with #NAME?
>
> Can someone help me with simple directions to help me 1)be able to
> continue to use the "1000 input tickers" SS or 2)determine what to do to
> get current data from the new "smfGetPortfolio" example SS and be able to
> enter and retrieve EOD price data for roughly 20 stocks.
>
installed, or you've installed it in a different location than I have. If
the latter, you can just run the smfFixLinks macro to fix the location
errors.
To verify whether the add-in is installed, you can do this in a new
worksheet:
=RCHGetElementNumber("Version")
If you still have problems, let me know what that function returned.
On Tue, Nov 7, 2017 at 3:18 PM, wayne-moore@tx.rr.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Probably a year or more ago, I installed the SMF Add-In
> and entered symbols I wanted prices on in an example Excel SS named "1000
> input tickers with RCHGetYahooQuotes function"
> this SS worked great till the recent changes within Yahoo. I downloaded the
> most recent SMF Add-In and also downloaded a different new SS
> "smfGetPortfol
> data. Also, when I input a new symbol on the SS, it causes all the cells to
> be filled with #NAME?
>
> Can someone help me with simple directions to help me 1)be able to
> continue to use the "1000 input tickers" SS or 2)determine what to do to
> get current data from the new "smfGetPortfol
> enter and retrieve EOD price data for roughly 20 stocks.
>
Tue Nov 7, 2017 3:26 pm (PST) . Posted by:
"Pete A" option2z
I've posted a workbook that uses Randy's list of field names and a separate list of tickers to smooth the process of making & updating a "data table" with columns selected by placing an "X" next to the fields you want.
Pressing a button creates a page with just the field names you've checked off, for all the tickers listed in another sheet.
Nothing magic, but I'm finding it useful to extract just the few columns (from the 100 or so available) with just the click of a button.
Go here<http://wp.me/p3tuo7-1h5> to download I (more description). Or here<http://www.sundognorth.com/TtP-S/wp-content/uploads/smfPortfolioView-Data.xlsm> for just the file to play with.
Inputs:
[cid:image001.png@01D357D5.971B84D0]
[cid:image002.png@01D357D5.F8AF6090]
Outputs (with the slected fields from above:
[cid:image003.png@01D357D5.F8AF6090]
Pete A
Pressing a button creates a page with just the field names you've checked off, for all the tickers listed in another sheet.
Nothing magic, but I'm finding it useful to extract just the few columns (from the 100 or so available) with just the click of a button.
Go here<http://wp.me/p3tuo7-1h5> to download I (more description). Or here<http://www.sundognorth.com/TtP-S/wp-content/uploads/smfPortfolioView-Data.xlsm> for just the file to play with.
Inputs:
[cid:image001.png@01D357D5.971B84D0]
[cid:image002.png@01D357D5.F8AF6090]
Outputs (with the slected fields from above:
[cid:image003.png@01D357D5.F8AF6090]
Pete A
Tue Nov 7, 2017 3:57 pm (PST) . Posted by:
lewglenn
Randy,
I don't have a list of the field identifiers used in RCHGetYahooQuotes. Could you please provide the Yahoo Field Names for the string: "st1d1l1"?
Thanks.
I don't have a list of the field identifiers used in RCHGetYahooQuotes. Could you please provide the Yahoo Field Names for the string: "st1d1l1"
Thanks.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar