9 Messages
Digest #2973
Messages
Tue Feb 18, 2014 12:27 pm (PST) . Posted by:
infinityholdings@ymail.com
I am trying to build a more automated attribution report and I need S&P500 individual sector total returns and price returns. The S&P website has them but I do not fully understand how to use the add-in to grab that data. Could someone help with how to this.
Thank you
Adam
Thank you
Adam
Tue Feb 18, 2014 12:54 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Sample URL and more details, please?
On Tue, Feb 18, 2014 at 1:27 PM, <coons_adam@yahoo.com> wrote:
> I am trying to build a more automated attribution report and I need
> S&P500 individual sector total returns and price returns. The S&P website
> has them but I do not fully understand how to use the add-in to grab that
> data. Could someone help with how to this.
>
On Tue, Feb 18, 2014 at 1:27 PM, <coons_adam@yahoo.
> I am trying to build a more automated attribution report and I need
> S&P500 individual sector total returns and price returns. The S&P website
> has them but I do not fully understand how to use the add-in to grab that
> data. Could someone help with how to this.
>
Tue Feb 18, 2014 2:06 pm (PST) . Posted by:
infinityholdings@ymail.com
I apologize for being too vague. A URL for consumer discretionary is http://www.spindices.com/indices/equity/sp-500-consumer-discretionary-sector http://www.spindices.com/indices/equity/sp-500-consumer-discretionary-sector I am wanting to be able to pull MTD, QTD, and YTD total and price return for each of the S&P500 sectors. If there is another website that gives sector TR that is fine but this is the only site I know of that gives that info.
Tue Feb 18, 2014 2:21 pm (PST) . Posted by:
mikemcq802
I'll leave it to Randy to help you with extracting from that site.
But, there is another way to get that data. Use SMF to pull the Yahoo History for ETFs that mimic the sectors. The SPDR sectors are large, liquid, and have good amounts of history. Yahoo provides the "raw" closing price for just price returns and also provides an adjusted close which can be used for total returns.
Here's the list of SPDR sector ETFs:
http://www.sectorspdr.com/sectorspdr/ http://www.sectorspdr.com/sectorspdr/
The only drawback I can imagine would be errors in Yahoo dividend history that might skew its adjusted close price and thus the total return calculation. You could use SMF to extract the dividend info and review those to ensure consistency.
But, there is another way to get that data. Use SMF to pull the Yahoo History for ETFs that mimic the sectors. The SPDR sectors are large, liquid, and have good amounts of history. Yahoo provides the "raw" closing price for just price returns and also provides an adjusted close which can be used for total returns.
Here's the list of SPDR sector ETFs:
http://www.sectorsp
The only drawback I can imagine would be errors in Yahoo dividend history that might skew its adjusted close price and thus the total return calculation. You could use SMF to extract the dividend info and review those to ensure consistency.
Tue Feb 18, 2014 4:02 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The data can be extracted from there, but it's buried within JavaScript
code. For example:
=smfConvertData(smfstrExtr(RCHGetWebData("
http://us.spindices.com/indices/equity/sp-500-consumer-discretionary-sector",":""S&P
500 Consumer Discretionary (Sector)
(TR)"",""closeCount"":"),"monthToDateReturn"":",","))
As Mike suggests, I'd encourage just calculating them yourself from the
ETFs that mimic the S&P 500 sectors. Take a look at this workbooks from the
"Uploads by Forum Members" folder in the FILES area:
Price-Performance-Data-Retrieval-v2.xls
Price-Performance-Data-Retrieval-v3.xls
Price-Performance-Data-Retrieval-v4.xls
They all use the smfPricesByDates() function to get adjusted closing prices
for various dates. Those should give you usable approximations of the total
returns for the various sectors. These worksheets would also put you in
control of whichever custom periods you want to see.
On Tue, Feb 18, 2014 at 3:06 PM, <coons_adam@yahoo.com> wrote:
>
> I apologize for being too vague. A URL for consumer discretionary is
> http://www.spindices.com/indices/equity/sp-500-consumer-discretionary-sector
>
> I am wanting to be able to pull MTD, QTD, and YTD total and price return
> for each of the S&P500 sectors. If there is another website that gives
> sector TR that is fine but this is the only site I know of that gives that
> info.
>
>
code. For example:
=smfConvertData(
http://us.spindices
500 Consumer Discretionary (Sector)
(TR)""
As Mike suggests, I'd encourage just calculating them yourself from the
ETFs that mimic the S&P 500 sectors. Take a look at this workbooks from the
"Uploads by Forum Members" folder in the FILES area:
Price-Performance-
Price-Performance-
Price-Performance-
They all use the smfPricesByDates(
for various dates. Those should give you usable approximations of the total
returns for the various sectors. These worksheets would also put you in
control of whichever custom periods you want to see.
On Tue, Feb 18, 2014 at 3:06 PM, <coons_adam@yahoo.
>
> I apologize for being too vague. A URL for consumer discretionary is
> http://www.spindice
>
> I am wanting to be able to pull MTD, QTD, and YTD total and price return
> for each of the S&P500 sectors. If there is another website that gives
> sector TR that is fine but this is the only site I know of that gives that
> info.
>
>
Tue Feb 18, 2014 7:09 pm (PST) . Posted by:
sihlai
Under VBA
RCHGetYahooHistory(tick, 2014, 1, 2, 2014, 2, 18, , "H", 0, , , 20, 1)
returns correct value. If I have bbb declared as date and set to 2014-02-18
RCHGetYahooHistory(tick, 2014, 1, 2, Year(bbb), Month(bbb), Day(bbb), , "H", 0, , , 20, 1)
returns the high of past 20 quotes from Feb 17 and back, missing Feb 18. I can't figure out the reason for this. Is that a VBA of RCH issue?
RCHGetYahooHistory(
returns correct value. If I have bbb declared as date and set to 2014-02-18
RCHGetYahooHistory(
returns the high of past 20 quotes from Feb 17 and back, missing Feb 18. I can't figure out the reason for this. Is that a VBA of RCH issue?
Tue Feb 18, 2014 7:31 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's Feb 18 today. Did you run it before Yahoo updated historical quotes
for today?
I tried it right now, and I get back the high for Feb 18.
On Tue, Feb 18, 2014 at 8:09 PM, <sihlai@yahoo.ca> wrote:
>
> Under VBA
> RCHGetYahooHistory(tick, 2014, 1, 2, 2014, 2, 18, , "H", 0, , , 20, 1)
> returns correct value. If I have bbb declared as date and set to 2014-02-18
> RCHGetYahooHistory(tick, 2014, 1, 2, Year(bbb), Month(bbb), Day(bbb), ,
> "H", 0, , , 20, 1)
> returns the high of past 20 quotes from Feb 17 and back, missing Feb 18. I
> can't figure out the reason for this. Is that a VBA of RCH issue?
>
for today?
I tried it right now, and I get back the high for Feb 18.
On Tue, Feb 18, 2014 at 8:09 PM, <sihlai@yahoo.
>
> Under VBA
> RCHGetYahooHistory(
> returns correct value. If I have bbb declared as date and set to 2014-02-18
> RCHGetYahooHistory(
> "H"
> returns the high of past 20 quotes from Feb 17 and back, missing Feb 18. I
> can't figure out the reason for this. Is that a VBA of RCH issue?
>
Tidak ada komentar:
Posting Komentar