15 New Messages
Digest #2681
Messages
Thu Jul 4, 2013 7:56 am (PDT) . Posted by:
"panda317" panda317
Is there a way to pull the current (or closing) price of a list of stocks for input into an adjacent cell and dividend amount(s) (regular and special) into another cell to then calculate yield? I've tried but I don't seem to get any results.
I'll upload the spreadsheet I currently update manually. I would like to automate the data collection.
Additionally, is there a way to pull next dividend data (including amount, ex-dividend date, pay date)?
And, if it's not asking too much to get this from a variety of sites?
I'll upload the spreadsheet I currently update manually. I would like to automate the data collection.
Additionally, is there a way to pull next dividend data (including amount, ex-dividend date, pay date)?
And, if it's not asking too much to get this from a variety of sites?
Thu Jul 4, 2013 8:12 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Short answer -- yes, most of what you ask can be done.
You could use the RCHGetYahooQuotes() to get current price and trailing
dividend amount/yield:
=RCHGetYahooQuotes("MMM,IBM","l1dy";)
For forward annual dividend amount and yield, pay date, ex-dividend date
from Yahoo's Key Statistics page:
=RCHGetElementNumber("MMM";,4924)
=RCHGetElementNumber("MMM";,4925)
=RCHGetElementNumber("MMM";,991)
=RCHGetElementNumber("MMM";,992)
But I find Yahoo has a delay between declaration of dividend and update of
those dates.
For other sites, you'd probable need to use either the RCHGetTableCell() or
smfGetTagContent() functions, with a customized extraction.
I prefer to estimate the forward ex-dividend date myself, because too often
the dividend is declared only a few days ahead of the ex-dividend date.
It all depends on what you're using the data for.
On Thu, Jul 4, 2013 at 6:04 AM, panda317 <pat@patrosenheim.com > wrote:
> Is there a way to pull the current (or closing) price of a list of stocks
> for input into an adjacent cell and dividend amount(s) (regular and
> special) into another cell to then calculate yield? I've tried but I don't
> seem to get any results.
>
> I'll upload the spreadsheet I currently update manually. I would like to
> automate the data collection.
>
> Additionally, is there a way to pull next dividend data (including amount,
> ex-dividend date, pay date)?
>
> And, if it's not asking too much to get this from a variety of sites?
>
You could use the RCHGetYahooQuotes(
dividend amount/yield:
=RCHGetYahooQuotes(
For forward annual dividend amount and yield, pay date, ex-dividend date
from Yahoo's Key Statistics page:
=RCHGetElementNumbe
=RCHGetElementNumbe
=RCHGetElementNumbe
=RCHGetElementNumbe
But I find Yahoo has a delay between declaration of dividend and update of
those dates.
For other sites, you'd probable need to use either the RCHGetTableCell(
smfGetTagContent(
I prefer to estimate the forward ex-dividend date myself, because too often
the dividend is declared only a few days ahead of the ex-dividend date.
It all depends on what you're using the data for.
On Thu, Jul 4, 2013 at 6:04 AM, panda317 <pat@patrosenheim.
> Is there a way to pull the current (or closing) price of a list of stocks
> for input into an adjacent cell and dividend amount(s) (regular and
> special) into another cell to then calculate yield? I've tried but I don't
> seem to get any results.
>
> I'll upload the spreadsheet I currently update manually. I would like to
> automate the data collection.
>
> Additionally, is there a way to pull next dividend data (including amount,
> ex-dividend date, pay date)?
>
> And, if it's not asking too much to get this from a variety of sites?
>
Thu Jul 4, 2013 9:38 am (PDT) . Posted by:
"panda317" panda317
ideally, i'd like to read the symbol from column D on my spreadsheet and place the yield in column A and the return in column B
can't figure out how to do that...
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Short answer -- yes, most of what you ask can be done.
>
> You could use the RCHGetYahooQuotes() to get current price and trailing
> dividend amount/yield:
>
> =RCHGetYahooQuotes("MMM,IBM","l1dy";)
>
> For forward annual dividend amount and yield, pay date, ex-dividend date
> from Yahoo's Key Statistics page:
>
> =RCHGetElementNumber("MMM";,4924)
> =RCHGetElementNumber("MMM";,4925)
> =RCHGetElementNumber("MMM";,991)
> =RCHGetElementNumber("MMM";,992)
>
> But I find Yahoo has a delay between declaration of dividend and update of
> those dates.
>
> For other sites, you'd probable need to use either the RCHGetTableCell() or
> smfGetTagContent() functions, with a customized extraction.
>
> I prefer to estimate the forward ex-dividend date myself, because too often
> the dividend is declared only a few days ahead of the ex-dividend date.
>
> It all depends on what you're using the data for.
>
> On Thu, Jul 4, 2013 at 6:04 AM, panda317 <pat@...> wrote:
>
> > Is there a way to pull the current (or closing) price of a list of stocks
> > for input into an adjacent cell and dividend amount(s) (regular and
> > special) into another cell to then calculate yield? I've tried but I don't
> > seem to get any results.
> >
> > I'll upload the spreadsheet I currently update manually. I would like to
> > automate the data collection.
> >
> > Additionally, is there a way to pull next dividend data (including amount,
> > ex-dividend date, pay date)?
> >
> > And, if it's not asking too much to get this from a variety of sites?
> >
>
can't figure out how to do that...
--- In smf_addin@yahoogrou
>
> Short answer -- yes, most of what you ask can be done.
>
> You could use the RCHGetYahooQuotes(
> dividend amount/yield:
>
> =RCHGetYahooQuotes(
>
> For forward annual dividend amount and yield, pay date, ex-dividend date
> from Yahoo's Key Statistics page:
>
> =RCHGetElementNumbe
> =RCHGetElementNumbe
> =RCHGetElementNumbe
> =RCHGetElementNumbe
>
> But I find Yahoo has a delay between declaration of dividend and update of
> those dates.
>
> For other sites, you'd probable need to use either the RCHGetTableCell(
> smfGetTagContent(
>
> I prefer to estimate the forward ex-dividend date myself, because too often
> the dividend is declared only a few days ahead of the ex-dividend date.
>
> It all depends on what you're using the data for.
>
> On Thu, Jul 4, 2013 at 6:04 AM, panda317 <pat@...> wrote:
>
> > Is there a way to pull the current (or closing) price of a list of stocks
> > for input into an adjacent cell and dividend amount(s) (regular and
> > special) into another cell to then calculate yield? I've tried but I don't
> > seem to get any results.
> >
> > I'll upload the spreadsheet I currently update manually. I would like to
> > automate the data collection.
> >
> > Additionally, is there a way to pull next dividend data (including amount,
> > ex-dividend date, pay date)?
> >
> > And, if it's not asking too much to get this from a variety of sites?
> >
>
Thu Jul 4, 2013 9:50 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Return of what? If total return, do you still need yield?
Array entering this in A2:B2:
=smfPricesByDates(D2,$A$1:$B$1)
...where your starting and ending dates are in A1 and B1 and ticker symbol
in D1, would give you the adjusted closing prices for the period from A1 to
B1. Then:
=B2/A2-1
...would give you the total return between the two dates.
On Thu, Jul 4, 2013 at 9:38 AM, panda317 <pat@patrosenheim.com > wrote:
> ideally, i'd like to read the symbol from column D on my spreadsheet and
> place the yield in column A and the return in column B
>
> can't figure out how to do that...
>
Array entering this in A2:B2:
=smfPricesByDates(
...where your starting and ending dates are in A1 and B1 and ticker symbol
in D1, would give you the adjusted closing prices for the period from A1 to
B1. Then:
=B2/A2-1
...would give you the total return between the two dates.
On Thu, Jul 4, 2013 at 9:38 AM, panda317 <pat@patrosenheim.
> ideally, i'd like to read the symbol from column D on my spreadsheet and
> place the yield in column A and the return in column B
>
> can't figure out how to do that...
>
Thu Jul 4, 2013 9:57 am (PDT) . Posted by:
"panda317" panda317
maybe this is worded better;
i have a spreadsheet with a list of stock symbols in column D under the heading 'symbol'. i'd like to read the symbol from column D on my spreadsheet and place the annual yield in column A and the annual return in column B. don't need dates for annualized results, do i?
can't figure out how to do that...
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Return of what? If total return, do you still need yield?
>
> Array entering this in A2:B2:
>
> =smfPricesByDates(D2,$A$1:$B$1)
>
> ...where your starting and ending dates are in A1 and B1 and ticker symbol
> in D1, would give you the adjusted closing prices for the period from A1 to
> B1. Then:
>
> =B2/A2-1
>
> ...would give you the total return between the two dates.
>
> On Thu, Jul 4, 2013 at 9:38 AM, panda317 <pat@...> wrote:
>
> > ideally, i'd like to read the symbol from column D on my spreadsheet and
> > place the yield in column A and the return in column B
> >
> > can't figure out how to do that...
> >
>
i have a spreadsheet with a list of stock symbols in column D under the heading 'symbol&#
can't figure out how to do that...
--- In smf_addin@yahoogrou
>
> Return of what? If total return, do you still need yield?
>
> Array entering this in A2:B2:
>
> =smfPricesByDates(
>
> ...where your starting and ending dates are in A1 and B1 and ticker symbol
> in D1, would give you the adjusted closing prices for the period from A1 to
> B1. Then:
>
> =B2/A2-1
>
> ...would give you the total return between the two dates.
>
> On Thu, Jul 4, 2013 at 9:38 AM, panda317 <pat@...> wrote:
>
> > ideally, i'd like to read the symbol from column D on my spreadsheet and
> > place the yield in column A and the return in column B
> >
> > can't figure out how to do that...
> >
>
Thu Jul 4, 2013 10:00 am (PDT) . Posted by:
"panda317" panda317
I'd like to automate the results I get for columns A and B.
Is there a way to do that?
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Return of what? If total return, do you still need yield?
>
> Array entering this in A2:B2:
>
> =smfPricesByDates(D2,$A$1:$B$1)
>
> ...where your starting and ending dates are in A1 and B1 and ticker
symbol
> in D1, would give you the adjusted closing prices for the period from
A1 to
> B1. Then:
>
> =B2/A2-1
>
> ...would give you the total return between the two dates.
>
> On Thu, Jul 4, 2013 at 9:38 AM, panda317 pat@... wrote:
>
> > ideally, i'd like to read the symbol from column D on my spreadsheet
and
> > place the yield in column A and the return in column B
> >
> > can't figure out how to do that...
> >
>
Is there a way to do that?
--- In smf_addin@yahoogrou
>
> Return of what? If total return, do you still need yield?
>
> Array entering this in A2:B2:
>
> =smfPricesByDates(
>
> ...where your starting and ending dates are in A1 and B1 and ticker
symbol
> in D1, would give you the adjusted closing prices for the period from
A1 to
> B1. Then:
>
> =B2/A2-1
>
> ...would give you the total return between the two dates.
>
> On Thu, Jul 4, 2013 at 9:38 AM, panda317 pat@... wrote:
>
> > ideally, i'd like to read the symbol from column D on my spreadsheet
and
> > place the yield in column A and the return in column B
> >
> > can't figure out how to do that...
> >
>
Thu Jul 4, 2013 10:02 am (PDT) . Posted by:
"panda317" panda317
i tried to post a screen shot but it got truncated. i uploaded the spreadsheet called corepicks. that's the one i'm using.
--- In smf_addin@yahoogroups.com , "panda317"; <pat@...> wrote:
>
> I'd like to automate the results I get for columns A and B.
>
> Is there a way to do that?
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
> >
> > Return of what? If total return, do you still need yield?
> >
> > Array entering this in A2:B2:
> >
> > =smfPricesByDates(D2,$A$1:$B$1)
> >
> > ...where your starting and ending dates are in A1 and B1 and ticker
> symbol
> > in D1, would give you the adjusted closing prices for the period from
> A1 to
> > B1. Then:
> >
> > =B2/A2-1
> >
> > ...would give you the total return between the two dates.
> >
> > On Thu, Jul 4, 2013 at 9:38 AM, panda317 pat@ wrote:
> >
> > > ideally, i'd like to read the symbol from column D on my spreadsheet
> and
> > > place the yield in column A and the return in column B
> > >
> > > can't figure out how to do that...
> > >
> >
>
--- In smf_addin@yahoogrou
>
> I'd like to automate the results I get for columns A and B.
>
> Is there a way to do that?
>
>
>
> --- In smf_addin@yahoogrou
> >
> > Return of what? If total return, do you still need yield?
> >
> > Array entering this in A2:B2:
> >
> > =smfPricesByDates(
> >
> > ...where your starting and ending dates are in A1 and B1 and ticker
> symbol
> > in D1, would give you the adjusted closing prices for the period from
> A1 to
> > B1. Then:
> >
> > =B2/A2-1
> >
> > ...would give you the total return between the two dates.
> >
> > On Thu, Jul 4, 2013 at 9:38 AM, panda317 pat@ wrote:
> >
> > > ideally, i'd like to read the symbol from column D on my spreadsheet
> and
> > > place the yield in column A and the return in column B
> > >
> > > can't figure out how to do that...
> > >
> >
>
Thu Jul 4, 2013 10:16 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
But in your spreadsheet, you add columns A and B. If B is total return, it
already includes the dividend return, so there would be no reason to add
them. So I would just use the previous formula.
However, you can pick the two items up from FinViz. Most efficient process
for COREPICKS.xlsx:
A2: =RCHGetTableCell("http://finviz.com/quote.ashx?t= "&smfJoin($D$2:$D$43,","),1,"?t="&$D2&"&",">Dividend
%")
B2: =RCHGetTableCell("http://finviz.com/quote.ashx?t= "&smfJoin($D$2:$D$43,","),1,"?t="&$D2&"&",">Perf
Year")
...and copy down.
On Thu, Jul 4, 2013 at 9:57 AM, panda317 <pat@patrosenheim.com > wrote:
> maybe this is worded better;
>
> i have a spreadsheet with a list of stock symbols in column D under the
> heading 'symbol'. i'd like to read the symbol from column D on my
> spreadsheet and place the annual yield in column A and the annual return in
> column B. don't need dates for annualized results, do i?
>
> can't figure out how to do that...
>
already includes the dividend return, so there would be no reason to add
them. So I would just use the previous formula.
However, you can pick the two items up from FinViz. Most efficient process
for COREPICKS.xlsx:
A2: =RCHGetTableCell(
%")
B2: =RCHGetTableCell(
Year")
...and copy down.
On Thu, Jul 4, 2013 at 9:57 AM, panda317 <pat@patrosenheim.
> maybe this is worded better;
>
> i have a spreadsheet with a list of stock symbols in column D under the
> heading 'symbol&#
> spreadsheet and place the annual yield in column A and the annual return in
> column B. don't need dates for annualized results, do i?
>
> can't figure out how to do that...
>
Thu Jul 4, 2013 11:08 am (PDT) . Posted by:
"panda317" panda317
well, part of my reason for wanting to separate it is the way finviz and some other sites calculate dividend yield. finviz uses 'only' the regular dividend to calculate yield, completely ignoring any special dividend. For a stock like #4 APO on my list, the difference in computed values is significant, making it #3 instead of #4.
So, what I would need to get the true value is to actually calculate the yield myself, taking the TTM regular dividend and adding it to the TTM special dividend to get the true TTM annualized dividend yield.
But, this is small potatoes compared to what you've already given me!
Thanks, muchly!
I will update the new spreadsheet here.
Now, here's a dumb question: If someone were to download this sheet from my site, they would also need your add-in to see any results, correct?
Thanks again,
Pat
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> But in your spreadsheet, you add columns A and B. If B is total return, it
> already includes the dividend return, so there would be no reason to add
> them. So I would just use the previous formula.
>
> However, you can pick the two items up from FinViz. Most efficient process
> for COREPICKS.xlsx:
>
> A2: =RCHGetTableCell("http://finviz.com/quote.ashx?t= "&smfJoin($D$2:$D$43,","),1,"?t="&$D2&"&",">Dividend
> %")
> B2: =RCHGetTableCell("http://finviz.com/quote.ashx?t= "&smfJoin($D$2:$D$43,","),1,"?t="&$D2&"&",">Perf
> Year")
>
> ...and copy down.
>
> On Thu, Jul 4, 2013 at 9:57 AM, panda317 <pat@...> wrote:
>
> > maybe this is worded better;
> >
> > i have a spreadsheet with a list of stock symbols in column D under the
> > heading 'symbol'. i'd like to read the symbol from column D on my
> > spreadsheet and place the annual yield in column A and the annual return in
> > column B. don't need dates for annualized results, do i?
> >
> > can't figure out how to do that...
> >
>
So, what I would need to get the true value is to actually calculate the yield myself, taking the TTM regular dividend and adding it to the TTM special dividend to get the true TTM annualized dividend yield.
But, this is small potatoes compared to what you've already given me!
Thanks, muchly!
I will update the new spreadsheet here.
Now, here's a dumb question: If someone were to download this sheet from my site, they would also need your add-in to see any results, correct?
Thanks again,
Pat
--- In smf_addin@yahoogrou
>
> But in your spreadsheet, you add columns A and B. If B is total return, it
> already includes the dividend return, so there would be no reason to add
> them. So I would just use the previous formula.
>
> However, you can pick the two items up from FinViz. Most efficient process
> for COREPICKS.xlsx:
>
> A2: =RCHGetTableCell(
> %")
> B2: =RCHGetTableCell(
> Year")
>
> ...and copy down.
>
> On Thu, Jul 4, 2013 at 9:57 AM, panda317 <pat@...> wrote:
>
> > maybe this is worded better;
> >
> > i have a spreadsheet with a list of stock symbols in column D under the
> > heading 'symbol&#
> > spreadsheet and place the annual yield in column A and the annual return in
> > column B. don't need dates for annualized results, do i?
> >
> > can't figure out how to do that...
> >
>
Thu Jul 4, 2013 11:10 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Correct.
On Thu, Jul 4, 2013 at 11:08 AM, panda317 <pat@patrosenheim.com > wrote:
>
> Now, here's a dumb question: If someone were to download this sheet from
> my site, they would also need your add-in to see any results, correct?
>
On Thu, Jul 4, 2013 at 11:08 AM, panda317 <pat@patrosenheim.
>
> Now, here's a dumb question: If someone were to download this sheet from
> my site, they would also need your add-in to see any results, correct?
>
Thu Jul 4, 2013 11:19 am (PDT) . Posted by:
"panda317" panda317
so, I'll add the link to your group on my blog;
http://www.patrosenheim.com/PandAtrader/
or
http://HYHRD.com
Thanks again!
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Correct.
>
> On Thu, Jul 4, 2013 at 11:08 AM, panda317 <pat@...> wrote:
>
> >
> > Now, here's a dumb question: If someone were to download this sheet from
> > my site, they would also need your add-in to see any results, correct?
> >
>
http://www.patrosen
or
http://HYHRD.
Thanks again!
--- In smf_addin@yahoogrou
>
> Correct.
>
> On Thu, Jul 4, 2013 at 11:08 AM, panda317 <pat@...> wrote:
>
> >
> > Now, here's a dumb question: If someone were to download this sheet from
> > my site, they would also need your add-in to see any results, correct?
> >
>
Thu Jul 4, 2013 8:05 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
<http://profiles.
<http://groups.
Lookup.xls
We do appreciate your sharing the spreadsheet with the group.
Any chance you might have instructions on how to use it?
When we create a spreadsheet we usually don't need instructions because it
is our spreadsheet.
Although, as I get older I find it very helpful to include an instruction
worksheet. Especially, if I intend to share it.
It would be great if you can update your spreadsheet with an instructions
worksheet containing an explanation of what it is you are attempting to do
with it and how other can use it.
Thanks,
Kermit
Hello,
This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.
File : /Uploads by forum members/COREPICKS.
Uploaded by : panda317 <pat@patrosenheim.
Description : average annualized total (annualized yield + annualized return)
You can access this file at the URL:
http://groups.
To learn more about file sharing for your group, please visit:
http://help.
Regards,
panda317 <pat@patrosenheim.
Thu Jul 4, 2013 11:15 pm (PDT) . Posted by:
"T" thomas777crown
I'm looking to get the sectors for each stock that is listed on barchart.com. An example of this would be http://www.barchart.com/quotes/stocks/AAPL where it says sectors there is a highlighted link that says "Computer - Micro" for AAPL how do I retrieve that information for other stocks as well.
Thanks in advance,
Thomas
Thanks in advance,
Thomas
Fri Jul 5, 2013 1:08 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Suppose we put the ticker symbol in cell B6. In cell C6, we can extract the
sector code with:
=smfStrExtr(smfGetTagContent("http://www.barchart.com/quotes/stocks/
"&B6,"td",-1,
"Market Cap","/stocks/sectors/"),"/sectors/","?sym=")
Then, depending on which view of the sectors you want:
=RCHGetHTMLTable("http://www.barchart.com/stocks/sectors/
"&$C$6,">Links",-1,"",1)
=RCHGetHTMLTable("http://www.barchart.com/stocks/sectors/
"&$C$6&"?view=technical",">Links",-1,"",1)
=RCHGetHTMLTable("http://www.barchart.com/stocks/sectors/
"&$C$6&"?view=performance",">Links",-1,"",1)
=RCHGetHTMLTable("http://www.barchart.com/stocks/sectors/
"&$C$6&"?view=fundamental",">Links",-1,"",1)
If you'd also like the name of the sector:
=smfGetTagContent("http://www.barchart.com/quotes/stocks/ "&B6,"a",0,"Market
Cap","/stocks/sectors/")
On Thu, Jul 4, 2013 at 11:15 PM, T <thomas777crown@yahoo.com > wrote:
> I'm looking to get the sectors for each stock that is listed on
> barchart.com. An example of this would be
> http://www.barchart.com/quotes/stocks/AAPL where it says sectors there is
> a highlighted link that says "Computer - Micro" for AAPL how do I retrieve
> that information for other stocks as well.
>
sector code with:
=smfStrExtr(
"&B6,"
"Market Cap","
Then, depending on which view of the sectors you want:
=RCHGetHTMLTable(
"&$C$6,
=RCHGetHTMLTable(
"&$C$6&"
=RCHGetHTMLTable(
"&$C$6&"
=RCHGetHTMLTable(
"&$C$6&"
If you'd also like the name of the sector:
=smfGetTagContent(
Cap","
On Thu, Jul 4, 2013 at 11:15 PM, T <thomas777crown@
> I'm looking to get the sectors for each stock that is listed on
> barchart.com. An example of this would be
> http://www.barchart
> a highlighted link that says "Computer - Micro" for AAPL how do I retrieve
> that information for other stocks as well.
>
Tidak ada komentar:
Posting Komentar