12 Messages
Digest #3309
3b
Re: RCHGetYahoohistory: getting dividend and closing price in one sh by "Randy Harmelink" rharmelink
Messages
Sun Feb 1, 2015 5:11 am (PST) . Posted by:
panda317
And if I had all Symbols in Column D, what would the format for those two be then?
I tried this, but it didn't work;
=RCHGetTableCell("http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t="&smfJoin($D2:$D$16,","),1,"?t="&$D2&"&" http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=VFINX,6,"class=""row_lbl""")
Right now, I have 22 mutual funds. That's not too many, is it? I don't expect it to get much larger than 40 at the very most.
I have 4 other sheets in the workbook, with 12, 35, 18, & 15 symbols each. Takes a minute or two to open, sometimes three. Not too bad....
Right now, I open the workbook and just wait until it's done getting all the info from finviz for my stocks. I think I'm going to change to Yahoo!, though, because it's more reliable, isn't it?
I tried this, but it didn't work;
=RCHGetTableCell("http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t="&smfJoin($D2:$D$16,","),1,"?t="&$D2&"&" http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=VFINX,6,"class=
Right now, I have 22 mutual funds. That's not too many, is it? I don't expect it to get much larger than 40 at the very most.
I have 4 other sheets in the workbook, with 12, 35, 18, & 15 symbols each. Takes a minute or two to open, sometimes three. Not too bad....
Right now, I open the workbook and just wait until it's done getting all the info from finviz for my stocks. I think I'm going to change to Yahoo!, though, because it's more reliable, isn't it?
Sun Feb 1, 2015 9:54 am (PST) . Posted by:
"Randy Harmelink" rharmelink
You don't need the smfJoin() function, because it's just one function per
ticker symbol, and one web page per ticker symbol. So:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=
"&D2,6,"class=""row_lbl""")
Yahoo has issues from time to time, but so do all of the data sources.
On Sun, Feb 1, 2015 at 6:11 AM, pat@patrosenheim.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> And if I had all Symbols in Column D, what would the format for those two
> be then?
>
> I tried this, but it didn't work;
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t="&smfJoin($D2:$D$16,","),1,"?t="&$D2&"&"
> <http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=VFINX>
> ,6,"class=""row_lbl""")
>
> Right now, I have 22 mutual funds. That's not too many, is it? I don't
> expect it to get much larger than 40 at the very most.
>
> I have 4 other sheets in the workbook, with 12, 35, 18, & 15 symbols each.
> Takes a minute or two to open, sometimes three. Not too bad....
>
> Right now, I open the workbook and just wait until it's done getting all
> the info from finviz for my stocks. I think I'm going to change to Yahoo!,
> though, because it's more reliable, isn't it?
>
ticker symbol, and one web page per ticker symbol. So:
=RCHGetTableCell("
http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=
"&D2,6,"class=""row_lbl""")
Yahoo has issues from time to time, but so do all of the data sources.
On Sun, Feb 1, 2015 at 6:11 AM, pat@patrosenheim.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> And if I had all Symbols in Column D, what would the format for those two
> be then?
>
> I tried this, but it didn't work;
>
> =RCHGetTableCell("
> http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t="&smfJoin($D2:$D$16,","),1,"?t="&$D2&"&"
> <http://performance.morningstar.com/Performance/fund/trailing-total-returns.action?t=VFINX>
> ,6,"class=
>
> Right now, I have 22 mutual funds. That's not too many, is it? I don't
> expect it to get much larger than 40 at the very most.
>
> I have 4 other sheets in the workbook, with 12, 35, 18, & 15 symbols each.
> Takes a minute or two to open, sometimes three. Not too bad....
>
> Right now, I open the workbook and just wait until it's done getting all
> the info from finviz for my stocks. I think I'm going to change to Yahoo!,
> though, because it's more reliable, isn't it?
>
Sun Feb 1, 2015 10:31 am (PST) . Posted by:
mff2805
I have a list of several hundred tickers on sheet1. sheets 2 and 3 are the IBD and the YAHOO template pages. i have a macro that, after selecting a range of tickers, will copy a ticker to sheets 2 and 3 and then copy selected info back onto sheet 1. creating my own personal database on my favorite info. however, sometimes, excel just goes "not responding" and i have to kill excel and restart, recovering the data file, since i save it after each ticker. what might be causing excel to hang? sometimes it's during the process of getting the yahoo data, sometimes the ibd data. and it doesn't seem to be happening after some specific number of tickers. after recovering, i can continue with my tickers, until the next time.
any ideas on what i could investigate next would be appreciated
thanks,
mike
any ideas on what i could investigate next would be appreciated
thanks,
mike
Sun Feb 1, 2015 11:00 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Are you giving it time to respond? "Not responding" doesn't always mean it
has stopped working.
If you're creating a small "database" (note that IBD has a limit on the
number of Checkups you can do in a 24-hour period), have you looked at
using the smfUpdateDownloadTable process?
On Sun, Feb 1, 2015 at 11:31 AM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have a list of several hundred tickers on sheet1. sheets 2 and 3 are
> the IBD and the YAHOO template pages. i have a macro that, after selecting
> a range of tickers, will copy a ticker to sheets 2 and 3 and then copy
> selected info back onto sheet 1. creating my own personal database on my
> favorite info. however, sometimes, excel just goes "not responding" and i
> have to kill excel and restart, recovering the data file, since i save it
> after each ticker. what might be causing excel to hang? sometimes it's
> during the process of getting the yahoo data, sometimes the ibd data. and
> it doesn't seem to be happening after some specific number of tickers.
> after recovering, i can continue with my tickers, until the next time.
> any ideas on what i could investigate next would be appreciated
>
has stopped working.
If you're creating a small "database" (note that IBD has a limit on the
number of Checkups you can do in a 24-hour period), have you looked at
using the smfUpdateDownloadTable process?
On Sun, Feb 1, 2015 at 11:31 AM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I have a list of several hundred tickers on sheet1. sheets 2 and 3 are
> the IBD and the YAHOO template pages. i have a macro that, after selecting
> a range of tickers, will copy a ticker to sheets 2 and 3 and then copy
> selected info back onto sheet 1. creating my own personal database on my
> favorite info. however, sometimes, excel just goes "not responding" and i
> have to kill excel and restart, recovering the data file, since i save it
> after each ticker. what might be causing excel to hang? sometimes it's
> during the process of getting the yahoo data, sometimes the ibd data. and
> it doesn't seem to be happening after some specific number of tickers.
> after recovering, i can continue with my tickers, until the next time.
> any ideas on what i could investigate next would be appreciated
>
Sun Feb 1, 2015 4:17 pm (PST) . Posted by:
mff2805
i've given it the time. generally after 10 minutes, it's a sure sign something is not right, as each ticker update typically takes 5-10 seconds. sometimes i've even let it go overnight.
i am aware of the IBD checkup limit - bummer, but manageable.
i'll look into the process you mention.
thanks,
mike
i am aware of the IBD checkup limit - bummer, but manageable.
i'll look into the process you mention.
thanks,
mike
Sun Feb 1, 2015 5:46 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
BTW, if you have a beta version of the add-in, there may be things there
that can help.
-- At the beginning of May last year, I added the ability to select rows
and/or columns of data for smfUpdateDownloadTable to update. If you blank
out the area first, you'll be able to see EXACTLY where things hang up, if
they do.
-- Toward the end of May last year, I added some context menu options to
turn logging on and off. If you had logging on, it might help to get a
better idea in your current process of where you're hanging.
-- Also towards the end of May last year, I added some context menu options
to toggle between synchronous and asynchronous calls. This doesn't make it
run any faster by allowing multiple calls to the Internet at the same time,
but it does allow EXCEL to update things more easily while Internet
retrievals are going on. For example, I have a process that updates the
status bar with messages to show me where I am in a process. It pulls in
Amazon search pages so I can cull a list of books. Each call to Amazon
takes about 2 seconds. If I enable asynchronous processing, EXCEL can
update my status bar as it process each web page. If disabled, I only see
those messages from the first 2 pages of updating. I much prefer seeing an
update after each page. Otherwise, it can sit there for a while without
appearing to do anything.
On Sun, Feb 1, 2015 at 1:17 PM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> i've given it the time. generally after 10 minutes, it's a sure sign
> something is not right, as each ticker update typically takes 5-10 seconds.
> sometimes i've even let it go overnight.
> i am aware of the IBD checkup limit - bummer, but manageable.
> i'll look into the process you mention.
>
that can help.
-- At the beginning of May last year, I added the ability to select rows
and/or columns of data for smfUpdateDownloadTable to update. If you blank
out the area first, you'll be able to see EXACTLY where things hang up, if
they do.
-- Toward the end of May last year, I added some context menu options to
turn logging on and off. If you had logging on, it might help to get a
better idea in your current process of where you're hanging.
-- Also towards the end of May last year, I added some context menu options
to toggle between synchronous and asynchronous calls. This doesn't make it
run any faster by allowing multiple calls to the Internet at the same time,
but it does allow EXCEL to update things more easily while Internet
retrievals are going on. For example, I have a process that updates the
status bar with messages to show me where I am in a process. It pulls in
Amazon search pages so I can cull a list of books. Each call to Amazon
takes about 2 seconds. If I enable asynchronous processing, EXCEL can
update my status bar as it process each web page. If disabled, I only see
those messages from the first 2 pages of updating. I much prefer seeing an
update after each page. Otherwise, it can sit there for a while without
appearing to do anything.
On Sun, Feb 1, 2015 at 1:17 PM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> i've given it the time. generally after 10 minutes, it's a sure sign
> something is not right, as each ticker update typically takes 5-10 seconds.
> sometimes i've even let it go overnight.
> i am aware of the IBD checkup limit - bummer, but manageable.
> i'll look into the process you mention.
>
Sun Feb 1, 2015 8:09 pm (PST) . Posted by:
mff2805
hmmm,
i do believe i have the beta version. i have the following, with updated element files.
Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program Files\SMF Add-In; ; 1)
i looked at the update download table template. seems like it would work with the yahoo data i pull in to my table, but would it also work with the IBD data because of the multistep method of getting the proper website location that's needed with tIBD site. the ibd template uses a couple lines to start with a ticker, then get the exchange, then the actual call to get data needs the exchange+ticker.
i don;t know about the context menu options. where can i learn more; are there examples? the looging options seems like it could be helpful in debugging, while the synchronous/asynchronous feature might also be helpful. my spreadsheet follows other examples that update the status bar as i pull info from each site, so i know which sheet results in a hang. but the hanging of excel seems to be random, sometimes the ibd sheet, sometimes the yahoo sheet.
also, i do pull ticker info from this Louis Navellier website http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704 http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704
is there a template file for this site available? i use a collection of formulas like this one
=smfstrExtr(smfGetTagContent(G3,"tr",-1,"Rating:")&"|", "Rating:</strong> ", "</td>")
to pull out info one item at a time. is there a better way?
i do believe i have the beta version. i have the following, with updated element files.
Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\Program Files\SMF Add-In; ; 1)
i looked at the update download table template. seems like it would work with the yahoo data i pull in to my table, but would it also work with the IBD data because of the multistep method of getting the proper website location that's needed with tIBD site. the ibd template uses a couple lines to start with a ticker, then get the exchange, then the actual call to get data needs the exchange+ticker.
i don;t know about the context menu options. where can i learn more; are there examples? the looging options seems like it could be helpful in debugging, while the synchronous/asynchronous feature might also be helpful. my spreadsheet follows other examples that update the status bar as i pull info from each site, so i know which sheet results in a hang. but the hanging of excel seems to be random, sometimes the ibd sheet, sometimes the yahoo sheet.
also, i do pull ticker info from this Louis Navellier website http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704 http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704
is there a template file for this site available? i use a collection of formulas like this one
=smfstrExtr(
to pull out info one item at a time. is there a better way?
Sun Feb 1, 2015 8:15 pm (PST) . Posted by:
mff2805
oops, looked like i had a prior-to-beta version i got the latest beta and now am using that.
Sun Feb 1, 2015 9:21 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
On Sun, Feb 1, 2015 at 9:09 PM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> hmmm,
> i do believe i have the beta version. i have the following, with updated
> element files.
> Stock Market Functions add-in, Version 2.1.2014.0130 (C:\Program Files\SMF
> Add-In; ; 1)
>
The 2014.01.03 version won't have any of the program updates I mentioned,
since they were done in May of last year.
Element definition updates don't count as program updates. I externalized
them so they'd be easier to update, without requiring program changes.
> i looked at the update download table template. seems like it would work
> with the yahoo data i pull in to my table, but would it also work with the
> IBD data because of the multistep method of getting the proper website
> location that's needed with tIBD site. the ibd template uses a couple
> lines to start with a ticker, then get the exchange, then the actual call
> to get data needs the exchange+ticker.
>
You can still do the multi-step process. For example, if one column has
this to retrieve the necessary URL:
"http://research.investors.com/stockcheckup/"&smfstrExtr(RCHGetWebData("
http://www.investors.com/search/searchresults.aspx?Ntt=~~~~~
","/quotes/"),"/quotes/","'")
Then it can be used it later columns with something like this (which gets
the EPS rank):
RCHGetTableCell("~~~1~~~",1,">EPS Rating")
The "~~~1~~~" portion of that tells the add-in to substitute the data from
the first column prior to the one being processed. So it would create the
necessary text-string URL from the previous column. If you want the RS Rank
in the next column, you'd need:
RCHGetTableCell("~~~2~~~",1,">RS Rating")
...because now the created URL was 2 columns prior to the current column.
I've attached an example.
> i don;t know about the context menu options. where can i learn more; are
> there examples? the looging options seems like it could be helpful in
> debugging, while the synchronous/asynchronous feature might also be
> helpful. my spreadsheet follows other examples that update the status bar
> as i pull info from each site, so i know which sheet results in a hang.
> but the hanging of excel seems to be random, sometimes the ibd sheet,
> sometimes the yahoo sheet.
>
No examples. Everything there is new. One of the other add-in users
actually came up with idea and the original coding. I just modified and
extended it a bit.
> also, i do pull ticker info from this Louis Navellier website
>
>
> http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704
> is there a template file for this site available? i use a collection of
> formulas like this one
> =smfstrExtr(smfGetTagContent(G3,"tr",-1,"Rating:")&"|",
> "Rating:</strong> ", "</td>")
> to pull out info one item at a time. is there a better way?
>
There used to be one, but I pulled it when Navellier changed their web site
structure. I did find a new version I was playing with at the time, but
decided not to upload. Also attached. But it still does things one item at
a time. I'm not sure I fully understand your question there, since most
items are extracted one at a time?
In any case, it pulls rating with:
=MID(RCHGetTableCell("
http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?q=
"&Ticker,0,"Rating:"),8,99)
...although if I did it today, I'd do:
=smfstrExtr(RCHGetTableCell("
http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?q=
"&Ticker,0,"Rating:"),":","~")
smf_addin@yahoogroups.com> wrote:
>
> hmmm,
> i do believe i have the beta version. i have the following, with updated
> element files.
> Stock Market Functions add-in, Version 2.1.2014.0130 (C:\Program Files\SMF
> Add-In; ; 1)
>
The 2014.01.03 version won't have any of the program updates I mentioned,
since they were done in May of last year.
Element definition updates don't count as program updates. I externalized
them so they'd be easier to update, without requiring program changes.
> i looked at the update download table template. seems like it would work
> with the yahoo data i pull in to my table, but would it also work with the
> IBD data because of the multistep method of getting the proper website
> location that's needed with tIBD site. the ibd template uses a couple
> lines to start with a ticker, then get the exchange, then the actual call
> to get data needs the exchange+ticker.
>
You can still do the multi-step process. For example, if one column has
this to retrieve the necessary URL:
"http://research.investors.com/stockcheckup/"&smfstrExtr(RCHGetWebData("
http://www.investors.com/search/searchresults.aspx?Ntt=~~~~~
","/quotes/"),"/quotes/","'")
Then it can be used it later columns with something like this (which gets
the EPS rank):
RCHGetTableCell("~~~1~~~",1,">EPS Rating")
The "~~~1~~~" portion of that tells the add-in to substitute the data from
the first column prior to the one being processed. So it would create the
necessary text-string URL from the previous column. If you want the RS Rank
in the next column, you'd need:
RCHGetTableCell("~~~2~~~",1,">RS Rating")
...because now the created URL was 2 columns prior to the current column.
I've attached an example.
> i don;t know about the context menu options. where can i learn more; are
> there examples? the looging options seems like it could be helpful in
> debugging, while the synchronous/asynchronous feature might also be
> helpful. my spreadsheet follows other examples that update the status bar
> as i pull info from each site, so i know which sheet results in a hang.
> but the hanging of excel seems to be random, sometimes the ibd sheet,
> sometimes the yahoo sheet.
>
No examples. Everything there is new. One of the other add-in users
actually came up with idea and the original coding. I just modified and
extended it a bit.
> also, i do pull ticker info from this Louis Navellier website
>
>
> http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?t=ACOR&port=438704
> is there a template file for this site available? i use a collection of
> formulas like this one
> =smfstrExtr(smfGetTagContent(G3,"tr",-1,"Rating:")&"|",
> "Rating:</strong> ", "</td>")
> to pull out info one item at a time. is there a better way?
>
There used to be one, but I pulled it when Navellier changed their web site
structure. I did find a new version I was playing with at the time, but
decided not to upload. Also attached. But it still does things one item at
a time. I'm not sure I fully understand your question there, since most
items are extracted one at a time?
In any case, it pulls rating with:
=MID(RCHGetTableCell("
http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?q=
"&Ticker,0,"Rating:"),8,99)
...although if I did it today, I'd do:
=smfstrExtr(RCHGetTableCell("
http://navelliergrowth.investorplace.com/portfolio-grader/stock-report.html?q=
"&Ticker,
Attachment(s) from Randy Harmelink
2 of 2 File(s)
Sun Feb 1, 2015 9:24 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Some people went back to:
http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions-2.1.2014.05.25.zip
...because the changes I made for Yahoo options web pages were invalidated
when Yahoo backed out their website changes. I still expect them to
reinstate them, but for now the October update files won't work for Yahoo
options pages.
On Sun, Feb 1, 2015 at 9:15 PM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> oops, looked like i had a prior-to-beta version i got the latest beta and
> now am using that.
>
http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions-2.1.2014.05.25.zip
...because the changes I made for Yahoo options web pages were invalidated
when Yahoo backed out their website changes. I still expect them to
reinstate them, but for now the October update files won't work for Yahoo
options pages.
On Sun, Feb 1, 2015 at 9:15 PM, mff3429@comcast.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> oops, looked like i had a prior-to-beta version i got the latest beta and
> now am using that.
>
Sun Feb 1, 2015 11:33 pm (PST) . Posted by:
rannavajjhala
Hi Randy,
Is it possible to get the closing price of a stock over a period of time and corresponding dividend payout (if any during that period) all in one shot with RCHGetYahooHistory? Currently, I have to run it twice and the dates for dividend are only on the pay out dates and do not line up with the historical close price dates.
Thanks,
Ravi
Is it possible to get the closing price of a stock over a period of time and corresponding dividend payout (if any during that period) all in one shot with RCHGetYahooHistory? Currently, I have to run it twice and the dates for dividend are only on the pay out dates and do not line up with the historical close price dates.
Thanks,
Ravi
Mon Feb 2, 2015 12:11 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Sorry, no. Yahoo creates the CSV file that RCHGetYahooQuotes() uses, and it
only has one or the other.
On Mon, Feb 2, 2015 at 12:33 AM, rannavajjhala@... wrote:
>
> Is it possible to get the closing price of a stock over a period of time
> and corresponding dividend payout (if any during that period) all in one
> shot with RCHGetYahooHistory? Currently, I have to run it twice and the
> dates for dividend are only on the pay out dates and do not line up with
> the historical close price dates.
>
only has one or the other.
On Mon, Feb 2, 2015 at 12:33 AM, rannavajjhala@
>
> Is it possible to get the closing price of a stock over a period of time
> and corresponding dividend payout (if any during that period) all in one
> shot with RCHGetYahooHistory? Currently, I have to run it twice and the
> dates for dividend are only on the pay out dates and do not line up with
> the historical close price dates.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar