Sabtu, 13 Februari 2016

[smf_addin] Digest Number 3635

15 Messages

Digest #3635
1a
M* YTD Returns by dcabler
1b
Re: M* YTD Returns by "Randy Harmelink" rharmelink
1c
Re: M* YTD Returns by jdownhower
1d
Re: M* YTD Returns by "Randy Harmelink" rharmelink
1e
Re: M* YTD Returns by jdownhower
1f
Re: M* YTD Returns by "Randy Harmelink" rharmelink
1g
Re: M* YTD Returns by jdownhower
1h
Re: M* YTD Returns by "Gordon Pedersen"
2a
Excel Help Please... by "Richard Milley" raminsj
2b
Re: Excel Help Please... by "Craig Passow" passow
2c
Re: Excel Help Please... by "Richard Milley" raminsj
2d
Re: Excel Help Please... by "Craig Passow" passow
2e
Re: Excel Help Please... by "Randy Harmelink" rharmelink
3b
Re: Problem with RCHGetYahooHistory by "Randy Harmelink" rharmelink

Messages

Sat Feb 13, 2016 7:23 am (PST) . Posted by:

dcabler

Hey folks,
Been using SMF for years, but for the last several months, when I open a spreadsheet I use for tracking various funds, it just sits for about 30 seconds. After that, the cells where I'm grabbing data either update properly, or it gives up and I get "Error" in the calculated cells. Seems that for the last few days, I only get an error.


The cells have RCHGetElementNumber(cell with ticker symbol, 4892) to grab YTD trailing returns from Morningstar.


I double checked this morning and I noticed that hard paths were coded for RCHGetElementNumber and I fixed that. I also noticed I had an older version of SMF, so I updated. After all that, no change.


Running Excel under MS Office 2013 under Windows 10.


All that best and thanks in advance!
Dru




Sat Feb 13, 2016 7:27 am (PST) . Posted by:

"Randy Harmelink" rharmelink

MorningStar removed ETF/Mutual fund print page that the data used to come
from. So what you're seeing is the add-in timing out, trying to retrieve
that nonexistent web page.

I looked at moving the data retrieval over to their new pages, but there
isn't a one-to-one relationship between data elements, and mutual funds and
ETF's would have different web pages. So I'm kind of lost about what to do.

On Sat, Feb 13, 2016 at 5:38 AM, dcabler@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Been using SMF for years, but for the last several months, when I open
> a spreadsheet I use for tracking various funds, it just sits for about 30
> seconds. After that, the cells where I'm grabbing data either update
> properly, or it gives up and I get "Error" in the calculated cells. Seems
> that for the last few days, I only get an error.
>
> The cells have RCHGetElementNumber(cell with ticker symbol, 4892) to
> grab YTD trailing returns from Morningstar.
>
> I double checked this morning and I noticed that hard paths were coded
> for RCHGetElementNumber and I fixed that. I also noticed I had an older
> version of SMF, so I updated. After all that, no change.
>
> Running Excel under MS Office 2013 under Windows 10.
>
>
>

Sat Feb 13, 2016 7:48 am (PST) . Posted by:

jdownhower

I also started recently getting these errors. I thought it was something messed up on my machine.

Please let me know when a fix/workaround is available. The SMF add-in has been working great for me up until now. Thanks.

Sat Feb 13, 2016 7:55 am (PST) . Posted by:

"Randy Harmelink" rharmelink

At the moment, the workaround would be to grab them yourself. Funds from
something like:

http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t=WAAEX

...and ETFs from something like:

http://performance.morningstar.com/Performance/cef/trailing-total-returns.action?ndec=2&ep=true&align=d&t=SPY

Or to actually generate them yourself with smfPricesByDates().

On Sat, Feb 13, 2016 at 8:43 AM, jdownhower@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I also started recently getting these errors. I thought it was something
> messed up on my machine.
>
> Please let me know when a fix/workaround is available. The SMF add-in has
> been working great for me up until now. Thanks.
>

Sat Feb 13, 2016 11:34 am (PST) . Posted by:

jdownhower

Thank you! I just need mutual fund data, so here are the formulas I am using as a workaround for now based on your suggestion:


Trailing Returns - 1-Month - Total Return =RCHGetElementNumber($B13, 4890)/100 replaced by =smfGetTagContent("http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",3,"Total Return %",$B13)/100 Trailing Returns - 3-Month - Total Return =RCHGetElementNumber($B13, 4891)/100 replaced by =smfGetTagContent("http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",4,"Total Return %",$B13)/100 Trailing Returns - 1-Year - Total Return =RCHGetElementNumber($B13, 4893)/100 replaced by =smfGetTagContent("http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",6,"Total Return %",$B13)/100 Etc.


Thanks again







Sat Feb 13, 2016 12:47 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

By the way, you could have updated those elements yourself, using the
smf-elements-20.txt file. Since they are loaded in order, the 20th file is
loaded last, and would override any definitions in earlier files. So you
could have put this line in that file:

4890;MorningstarTR;Trailing Returns - 1-Month - Total
Return;=smfGetTagContent("
http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t=~~~~~","td",3,"Total
Return %","~~~~~")/100

That particular definition would work for ETFs or mutual funds.

But using the override might be an easier "work-around" since it would
apply to all workbooks using that element number. So the workbooks
themselves don't need to be changed. And, if they do get updated later, you
can always remove them from the smf-elements-20.txt file to revert to the
"official" element definitions.

On Sat, Feb 13, 2016 at 12:34 PM, jdownhower@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Thank you! I just need mutual fund data, so here are the formulas I am
> using as a workaround for now based on your suggestion:
>
>
> - Trailing Returns - 1-Month - Total Return
> - =RCHGetElementNumber($B13, 4890)/100
> - replaced by
> - =smfGetTagContent("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",3,"Total
> Return %",$B13)/100
>
>
> - Trailing Returns - 3-Month - Total Return
> - =RCHGetElementNumber($B13, 4891)/100
> - replaced by
> - =smfGetTagContent("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",4,"Total
> Return %",$B13)/100
>
>
> - Trailing Returns - 1-Year - Total Return
> - =RCHGetElementNumber($B13, 4893)/100
> - replaced by
> - =smfGetTagContent("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",6,"Total
> Return %",$B13)/100
>
> Etc.
>
>
>

Sat Feb 13, 2016 1:39 pm (PST) . Posted by:

"Gordon Pedersen"

That's a great tip about customizing using smf-elements-20.txt, thanks,
Randy.

On Sat, Feb 13, 2016 at 12:47 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> By the way, you could have updated those elements yourself, using the
> smf-elements-20.txt file. Since they are loaded in order, the 20th file is
> loaded last, and would override any definitions in earlier files. So you
> could have put this line in that file:
>
> 4890;MorningstarTR;Trailing Returns - 1-Month - Total
> Return;=smfGetTagContent("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t=~~~~~","td",3,"Total
> Return %","~~~~~")/100
>
> That particular definition would work for ETFs or mutual funds.
>
> But using the override might be an easier "work-around" since it would
> apply to all workbooks using that element number. So the workbooks
> themselves don't need to be changed. And, if they do get updated later, you
> can always remove them from the smf-elements-20.txt file to revert to the
> "official" element definitions.
>
> On Sat, Feb 13, 2016 at 12:34 PM, jdownhower@gmail.com [smf_addin] <
> smf_addin@yahoogroups.com> wrote:
>
>>
>> Thank you! I just need mutual fund data, so here are the formulas I am
>> using as a workaround for now based on your suggestion:
>>
>>
>> - Trailing Returns - 1-Month - Total Return
>> - =RCHGetElementNumber($B13, 4890)/100
>> - replaced by
>> - =smfGetTagContent("
>> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",3,"Total
>> Return %",$B13)/100
>>
>>
>> - Trailing Returns - 3-Month - Total Return
>> - =RCHGetElementNumber($B13, 4891)/100
>> - replaced by
>> - =smfGetTagContent("
>> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",4,"Total
>> Return %",$B13)/100
>>
>>
>> - Trailing Returns - 1-Year - Total Return
>> - =RCHGetElementNumber($B13, 4893)/100
>> - replaced by
>> - =smfGetTagContent("
>> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?ndec=2&ep=true&align=d&t="&$B13,"td",6,"Total
>> Return %",$B13)/100
>>
>> Etc.
>>
>>
>>
>
>

--
Gordon Pedersen

Sat Feb 13, 2016 1:28 pm (PST) . Posted by:

"Richard Milley" raminsj

Can I be so bold as to ask for a little Excel help. I've been struggling
with the Excel INDIRECT function for days. Just can't figure it out.

Trying to use the "RCHGetTableCell" query to get certain data for a large
file of individual stock symbols. Can not figure out how to use the
INDIRECT and CONCATENATE functions together with this query...

*=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
<http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:")*

Where the stock symbol "*AAPL*" is replaced with a data reference to a
spreadsheet cell that contains the symbol. I can do this with the
CONCATENATE function, like this...

*=CONCATENATE("=RCHGetTableCell(""http://finance.yahoo.com/q/pr?s=
<http://finance.yahoo.com/q/pr?s=>",(C2),"+Profile""",",1,","""Sector:""")*

Where cell *C2 = AAPL*, so the Concatenate function returns...

*=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
<http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:")*
But since the CONCATENATE function is a text function, the "RCHGet" query
does not actually execute. It's just a line of text.

I believe there is a way to use the INDIRECT function in conjunction with
the CONCATENATE function to make this work, but I can't figure it out.

Any guidance would be greatly appreciated.

Thanks...
Richard...

Sat Feb 13, 2016 2:10 pm (PST) . Posted by:

"Craig Passow" passow

You can use "&" to concatenate strings and build your URL that way:

=RCHGetTableCell("http://finance.yahoo.com/q/pr?s="&C2&"+Profile",1,"Sector:")

On 2/13/2016 3:27 PM, Richard Milley richard.in.lacruz@gmail.com
[smf_addin] wrote:
> Can I be so bold as to ask for a little Excel help. I've been
> struggling with the Excel INDIRECT function for days. Just can't
> figure it out.
>
> Trying to use the "RCHGetTableCell" query to get certain data for a
> large file of individual stock symbols. Can not figure out how to use
> the INDIRECT and CONCATENATE functions together with this query...
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile",1,"Sector:")*
>
> Where the stock symbol "*AAPL*" is replaced with a data reference to a
> spreadsheet cell that contains the symbol. I can do this with the
> CONCATENATE function, like this...
>
> *=CONCATENATE("=RCHGetTableCell(""http://finance.yahoo.com/q/pr?s=",(C2),"+Profile""",",1,","""Sector:""")*
>
>
> Where cell *C2 = AAPL*, so the Concatenate function returns...
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile",1,"Sector:")
>
> *
> But since the CONCATENATE function is a text function, the "RCHGet"
> query does not actually execute. It's just a line of text.
>
> I believe there is a way to use the INDIRECT function in conjunction
> with the CONCATENATE function to make this work, but I can't figure it
> out.
>
> Any guidance would be greatly appreciated.
>
> Thanks...
> Richard...
>

Sat Feb 13, 2016 2:21 pm (PST) . Posted by:

"Richard Milley" raminsj

Thanks so much... now I feel like a real Excel dummy.

Your suggestion worked just fine. And I thought the INDIRECT function was
the only answer.

Richard...
On Sat, Feb 13, 2016 at 4:10 PM, Craig Passow passow@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
>
> You can use "&" to concatenate strings and build your URL that way:
>
> =RCHGetTableCell("http://finance.yahoo.com/q/pr?s="
> <http://finance.yahoo.com/q/pr?s=>&C2&"+Profile",1,"Sector:")
>
>
> On 2/13/2016 3:27 PM, Richard Milley richard.in.lacruz@gmail.com
> [smf_addin] wrote:
>
>
> Can I be so bold as to ask for a little Excel help. I've been struggling
> with the Excel INDIRECT function for days. Just can't figure it out.
>
> Trying to use the "RCHGetTableCell" query to get certain data for a large
> file of individual stock symbols. Can not figure out how to use the
> INDIRECT and CONCATENATE functions together with this query...
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
> <http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:")*
>
> Where the stock symbol "*AAPL*" is replaced with a data reference to a
> spreadsheet cell that contains the symbol. I can do this with the
> CONCATENATE function, like this...
>
> *=CONCATENATE("=RCHGetTableCell(""
> <http://finance.yahoo.com/q/pr?s=>http://finance.yahoo.com/q/pr?s=
> <http://finance.yahoo.com/q/pr?s=>",(C2),"+Profile""",",1,","""Sector:""")*
>
> Where cell *C2 = AAPL*, so the Concatenate function returns...
>
>
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
> <http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:") *
> But since the CONCATENATE function is a text function, the "RCHGet" query
> does not actually execute. It's just a line of text.
>
> I believe there is a way to use the INDIRECT function in conjunction with
> the CONCATENATE function to make this work, but I can't figure it out.
>
> Any guidance would be greatly appreciated.
>
> Thanks...
> Richard...
>
>
>
>

Sat Feb 13, 2016 3:03 pm (PST) . Posted by:

"Craig Passow" passow

You are concatenating three pieces to form a URL to use as the first
parameter of RCHGetTableCell. Two of the pieces are always the same and
one is a cell reference. For the sake of completeness and because you
mentioned it, you can just as easily use the Concatenate instead of "&":

=RCHGetTableCell(CONCATENATE("http://finance.yahoo.com/q/pr?s=",C2,"+Profile"),1,"Sector:")

Here are a couple of useful techniques that can be used to debug issues
with Excel formulas:

Bonus tip #1

You can select part of a formula in
the formula bar and evaluate it
using F9. To check that you've
properly constructed the URL, select
the part of the formula that makes
the URL (make sure the parentheses
match) :
CONCATENATE("http://finance.yahoo.com/q/pr?s=",C2,"+Profile")

and hit F9. The expression is
replaced with the result of
evaluating that expression. The
result is selected, so just copy it
(Ctrl-C), paste it into a browser
window, remove the double quotes and
make sure it resolves to the
information you're trying to
reference. Back in Excel, you can
revert to the original expression
using Undo (Ctrl-Z) or escape (ESC).

Bonus tip #2

You can insert carriage returns into expressions
(ALT-Enter) and then use indentation to make them
easier to read. In this case, you can make it easier
to see the three inputs to RCHGetTableCell and that
the first is the output of a CONCATENATE operation
which itself has three inputs:

=RCHGetTableCell(
CONCATENATE(
"http://finance.yahoo.com/q/pr?s=",
C2,
"+Profile"
),
1,
"Sector:"
)

On 2/13/2016 4:20 PM, Richard Milley richard.in.lacruz@gmail.com
[smf_addin] wrote:
> Thanks so much... now I feel like a real Excel dummy.
>
> Your suggestion worked just fine. And I thought the INDIRECT function
> was the only answer.
>
> Richard...
> On Sat, Feb 13, 2016 at 4:10 PM, Craig Passow passow@yahoo.com
> <mailto:passow@yahoo.com> [smf_addin] <smf_addin@yahoogroups.com
> <mailto:smf_addin@yahoogroups.com>> wrote:
>
> You can use "&" to concatenate strings and build your URL that way:
>
> =RCHGetTableCell("http://finance.yahoo.com/q/pr?s="
> <http://finance.yahoo.com/q/pr?s=>&C2&"+Profile",1,"Sector:")
>
>
>
> On 2/13/2016 3:27 PM, Richard Milley richard.in.lacruz@gmail.com
> <mailto:richard.in.lacruz@gmail.com> [smf_addin] wrote:
>> Can I be so bold as to ask for a little Excel help. I've been
>> struggling with the Excel INDIRECT function for days. Just can't
>> figure it out.
>>
>> Trying to use the "RCHGetTableCell" query to get certain data for
>> a large file of individual stock symbols. Can not figure out how
>> to use the INDIRECT and CONCATENATE functions together with this
>> query...
>>
>> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile",1,"Sector:")*
>>
>> Where the stock symbol "*AAPL*" is replaced with a data reference
>> to a spreadsheet cell that contains the symbol. I can do this
>> with the CONCATENATE function, like this...
>>
>> *=CONCATENATE("=RCHGetTableCell(""http://finance.yahoo.com/q/pr?s=",(C2),"+Profile""",",1,","""Sector:""")*
>>
>>
>> Where cell *C2 = AAPL*, so the Concatenate function returns...
>>
>> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile",1,"Sector:")
>>
>> *
>> But since the CONCATENATE function is a text function, the
>> "RCHGet" query does not actually execute. It's just a line of text.
>>
>> I believe there is a way to use the INDIRECT function in
>> conjunction with the CONCATENATE function to make this work, but
>> I can't figure it out.
>>
>> Any guidance would be greatly appreciated.
>>
>> Thanks...
>> Richard...
>
>
>

Sat Feb 13, 2016 3:26 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I see you've got the issue resolved. Just wanted to mention that the
"+Profile" portion of the URL isn't needed. So:

=RCHGetTableCell("http://finance.yahoo.com/q/pr?s="&C2,1,"Sector:")

...which gets the same results as element #13865:

=RCHGetElementNumber(C2,13865)

On Sat, Feb 13, 2016 at 2:27 PM, Richard Milley richard.in.lacruz@... wrote:

>
> Can I be so bold as to ask for a little Excel help. I've been struggling
> with the Excel INDIRECT function for days. Just can't figure it out.
>
> Trying to use the "RCHGetTableCell" query to get certain data for a large
> file of individual stock symbols. Can not figure out how to use the
> INDIRECT and CONCATENATE functions together with this query...
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
> <http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:")*
>
> Where the stock symbol "*AAPL*" is replaced with a data reference to a
> spreadsheet cell that contains the symbol. I can do this with the
> CONCATENATE function, like this...
>
> *=CONCATENATE("=RCHGetTableCell(""http://finance.yahoo.com/q/pr?s=
> <http://finance.yahoo.com/q/pr?s=>",(C2),"+Profile""",",1,","""Sector:""")*
>
> Where cell *C2 = AAPL*, so the Concatenate function returns...
>
>
>
> *=RCHGetTableCell("http://finance.yahoo.com/q/pr?s=AAPL+Profile
> <http://finance.yahoo.com/q/pr?s=AAPL+Profile>",1,"Sector:")*
> But since the CONCATENATE function is a text function, the "RCHGet" query
> does not actually execute. It's just a line of text.
>
> I believe there is a way to use the INDIRECT function in conjunction with
> the CONCATENATE function to make this work, but I can't figure it out.
>
> Any guidance would be greatly appreciated.
>
>
>

Sat Feb 13, 2016 2:26 pm (PST) . Posted by:

lewglenn

I entered the following code:


Sheets(SelStr).Select
SelPack = ChuzEquity(1)
Range("A1:B20000") = RCHGetYahooHistory(SelPack, , , , , , , , "DA", 1, 1, 1, 20000, 2)



SelStr shows up as "MyData" which is the correct worksheet & SelPack = ChuzEquity(1) shows up as "TLT", which is the correct value. I expected to see the TLT data populated on the worksheet yet nothing happened, i.e., the worksheet remained blank.


I have used this macro many times in the past but it suddenly stopped working. Has anything changed?


Thanks.




Sat Feb 13, 2016 3:19 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Not sure what to tell you. The code works fine here, as long as "SelPack"
is defined as a string variable. I did you a literal of "TLT" instead of
the ChuzEquity() array.

On Sat, Feb 13, 2016 at 3:26 PM, lewglenn@... wrote:

> I entered the following code:
>
> Sheets(SelStr).Select
> SelPack = ChuzEquity(1)
> Range("A1:B20000") = RCHGetYahooHistory(SelPack, , , , , , , , "DA",
> 1, 1, 1, 20000, 2)
>
> SelStr shows up as "MyData" which is the correct worksheet & SelPack =
> ChuzEquity(1) shows up as "TLT", which is the correct value. I expected to
> see the TLT data populated on the worksheet yet nothing happened, i.e., the
> worksheet remained blank.
>
> I have used this macro many times in the past but it suddenly stopped
> working. Has anything changed?
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar