15 Messages
Digest #3635
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
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
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.
>
>
>
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
> 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.
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.
>
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
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,
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.
>
>
>
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,
> Return %",$B13)
>
> 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
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,
>> Return %",$B13)
>>
>> 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...
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,
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...
>
=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,"
>
> *
> 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...
>
>
>
>
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,
> 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...
>
>
>
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,"
>>
>> *
>> 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.
>
>
>
"+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,
> 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.
Sheets(SelStr)
SelPack = ChuzEquity(1)
Range("
SelStr shows up as "MyData" which is the correct worksheet & SelPack = ChuzEquity(1) shows up as "TLT"
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?
>
>
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)
> SelPack = ChuzEquity(1)
> Range("
> 1, 1, 1, 20000, 2)
>
> SelStr shows up as "MyData" which is the correct worksheet & SelPack =
> ChuzEquity(1) shows up as "TLT"
> 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:
Posting Komentar