Minggu, 26 Februari 2012

[smf_addin] Digest Number 2129

Messages In This Digest (7 Messages)

1a.
Re: Historical prices won't pull in for specific SP5 telecom ETF From: naderphilip
2a.
Re: Adjusted Closes From: lexstar
2b.
Re: Adjusted Closes From: lexstar
2c.
Re: Adjusted Closes From: Randy Harmelink
2d.
Re: Adjusted Closes From: pharnsbe
3a.
Re: S&P 500 Total Return History? From: naderphilip
3b.
Re: S&P 500 Total Return History? From: Randy Harmelink

Messages

1a.

Re: Historical prices won't pull in for specific SP5 telecom ETF

Posted by: "naderphilip" naderphilip@yahoo.com   naderphilip

Sat Feb 25, 2012 2:37 pm (PST)



Oh I didn't realize how new new that fund was.. thx again Randy!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> It would be difficult to get prices prior to the fund's inception date...
>
> You're getting all the data you can get.
>
> On Fri, Feb 24, 2012 at 9:00 PM, naderphilip <naderphilip@...> wrote:
>
> > Well I meant to say it doesn't pull up data before 1/27/2011. Can you see
> > if it works for you?
> > It's driving me nuts. I have every sector ETF and a ton of stocks in this
> > spreadsheet linked to a few cells with the dates and everything else using
> > the same code, so I'm pretty sure it's not the regional settings.
> > I've tried just typing the ticker XTL into a column that is working fine,
> > and same result. Doesn't pull up the closing prices prior to 1/27/11.
> > I mean i probably could have just went to yahoofinance.com and copy and
> > pasted the prices 100 times over from the time i've spent trying to figure
> > this out haha
> >
>

2a.

Re: Adjusted Closes

Posted by: "lexstar" lexstar@yahoo.com   lexstar

Sat Feb 25, 2012 2:59 pm (PST)



Randy, I think you answer most of my questions. Just one clarification. If the 1-yr, 2-yr, and 5-yr returns are fixed and don't need to be updated, then am I correct to say that I'd only need to adjust the prices for the last 12 months?

If that's the case, then I think I've solved my problem. I thought, from my earlier post, that you've said that ALL historical prices would have to be adjusted for any NEW dividends, meaning including all those prices older than 12-mths would have to be re-adjusted too.

If all prices older than 12-mths DO NOT need to be re-adjusted, then I only need to worry about that last 12-mths data, that which I think I can manage, as a few hours of data download for my 1000 symbols are not a big problem.

But to answer your question as to what I need to prices for.... Like I said, I'm doing a few back-testing, and I need the adjusted prices for the data points in my back-testing. I want to see what a TOTAL RETURN back-test would do vs. a PRICE RETURN back-test.

In my earlier backtests, using a TOTAL RETURN provided a better return than using PRICE RETURN. Hope that clarifies what I'm trying to figure out here.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I don't do anything with mutual funds -- it appears all of my mutual fund
> screener bookmarks are either extinct or significantly changed...
>
> But my original question remains -- what are you doing with all the prices?
>
> For example, if you need 1-year, 2-year, and 5-year returns, those wouldn't
> change even if prices are adjusted. So, once set, they would never need to
> be updated.
>
> On Sat, Feb 25, 2012 at 2:57 PM, lexstar <lexstar@...> wrote:
>
> > 1. I want to do a few backtest comparing adjusted prices vs. non-adjusted
> > prices.
> >
> > 2. Do you recommend any filtering/screening product you're talking about?
> > Some of the ones I've looked at in the past doesn't provide the necessary
> > data I need.
> >
>

2b.

Re: Adjusted Closes

Posted by: "lexstar" lexstar@yahoo.com   lexstar

Sat Feb 25, 2012 3:05 pm (PST)



BUT.... then if I fixed the 1-yr, 2-yr or 5-yr, and I have the last 12-mths OHLC data subject to adjustments... doesn't that create a problem between the 1-yr fixed data and the last 12-mths data? Because the last 12-mths data will be adjusted per dividend payments? If that's the case, then it sets me back to square one.

My brain is fried just by thinking about this for the last several days. I can't seem to find an answer for it. Maybe it's simpler than I thought, but I'm just not seeing the big picture.


--- In smf_addin@yahoogroups.com, "lexstar" <lexstar@...> wrote:
>
> Randy, I think you answer most of my questions. Just one clarification. If the 1-yr, 2-yr, and 5-yr returns are fixed and don't need to be updated, then am I correct to say that I'd only need to adjust the prices for the last 12 months?
>
> If that's the case, then I think I've solved my problem. I thought, from my earlier post, that you've said that ALL historical prices would have to be adjusted for any NEW dividends, meaning including all those prices older than 12-mths would have to be re-adjusted too.
>
> If all prices older than 12-mths DO NOT need to be re-adjusted, then I only need to worry about that last 12-mths data, that which I think I can manage, as a few hours of data download for my 1000 symbols are not a big problem.
>
> But to answer your question as to what I need to prices for.... Like I said, I'm doing a few back-testing, and I need the adjusted prices for the data points in my back-testing. I want to see what a TOTAL RETURN back-test would do vs. a PRICE RETURN back-test.
>
> In my earlier backtests, using a TOTAL RETURN provided a better return than using PRICE RETURN. Hope that clarifies what I'm trying to figure out here.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > I don't do anything with mutual funds -- it appears all of my mutual fund
> > screener bookmarks are either extinct or significantly changed...
> >
> > But my original question remains -- what are you doing with all the prices?
> >
> > For example, if you need 1-year, 2-year, and 5-year returns, those wouldn't
> > change even if prices are adjusted. So, once set, they would never need to
> > be updated.
> >
> > On Sat, Feb 25, 2012 at 2:57 PM, lexstar <lexstar@> wrote:
> >
> > > 1. I want to do a few backtest comparing adjusted prices vs. non-adjusted
> > > prices.
> > >
> > > 2. Do you recommend any filtering/screening product you're talking about?
> > > Some of the ones I've looked at in the past doesn't provide the necessary
> > > data I need.
> > >
> >
>

2c.

Re: Adjusted Closes

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Feb 25, 2012 3:15 pm (PST)



Oops. Let me rephrase that -- fixed periods would have a fixed return. I
was thinking more in terms of calendar years.

The NEW dividends WOULD adjust all historical periods. However, the
proportional change on every day would be the same for the beginning and
ending prices, so the total return between them would stay the same. So it
really wouldn't make any sense to re-compute a total return between two
given dates.

On Sat, Feb 25, 2012 at 3:59 PM, lexstar <lexstar@yahoo.com> wrote:

> Randy, I think you answer most of my questions. Just one clarification.
> If the 1-yr, 2-yr, and 5-yr returns are fixed and don't need to be
> updated, then am I correct to say that I'd only need to adjust the prices
> for the last 12 months?
>
> If that's the case, then I think I've solved my problem. I thought, from
> my earlier post, that you've said that ALL historical prices would have to
> be adjusted for any NEW dividends, meaning including all those prices older
> than 12-mths would have to be re-adjusted too.
>
> If all prices older than 12-mths DO NOT need to be re-adjusted, then I
> only need to worry about that last 12-mths data, that which I think I can
> manage, as a few hours of data download for my 1000 symbols are not a big
> problem.
>
> But to answer your question as to what I need to prices for.... Like I
> said, I'm doing a few back-testing, and I need the adjusted prices for the
> data points in my back-testing. I want to see what a TOTAL RETURN
> back-test would do vs. a PRICE RETURN back-test.
>
> In my earlier backtests, using a TOTAL RETURN provided a better return
> than using PRICE RETURN. Hope that clarifies what I'm trying to figure out
> here.
>
2d.

Re: Adjusted Closes

Posted by: "pharnsbe" harnsberger@gmail.com   pharnsbe

Sat Feb 25, 2012 5:24 pm (PST)



Lexstar,

I agree with Randy in that this seems to be the type of project that would be better suited with a paid for data vendor.

I work with yahoo data for both stocks and mutual funds. I have found the yahoo data to be fairly accurate for stock data, but much less reliable for mutual fund data... I find mf data errors every month without fail. Sometimes yahoo will fix the errors the next day, sometimes it takes them a week, and sometimes the errors stay in the history permanently. For this reason we use yahoo as one of 4 data providers for mf data and double check all 4 against each other on a daily basis.

Although I have never used them, I've done quite a bit of research on alternate data providers recently and I'm currently under the opinion that Reuters DataLink would be the best solution for accurate mf data. At $25/month the price is very reasonable and comes with EOD data for the entire market... stocks, indices, funds.

As for your problem, I would recommend storing your data as daily adjusted %change, rather than trying to store the closing price. Each day you would download the last few days, calculate the most recent daily %change, then add the %change # to your database. This way when there is a split or dividend adjustment, it is captured in the daily adjusted %change number, but you don't have to go back in your historical data and readjust the entire previous history, as there are no stored closing prices to be adjusted.

If you wanted to keep track of the adjustment days, you could store both the unadjusted daily %change and the adjusted daily % change. On most days these two numbers will be identical, with the difference between the two (if any) being the "adjustment ratio" on the days in which an adj occurs.

Your other option, which is similar but IMO more complex, would be to store the unadjusted closing prices (which will never differ from what you download in the future, unless there were errors that later get corrected) and then store the daily adjustment ratio each day as well (stored as a %change #... most days it will be 0.00%)

-ph

3a.

Re: S&P 500 Total Return History?

Posted by: "naderphilip" naderphilip@yahoo.com   naderphilip

Sat Feb 25, 2012 3:43 pm (PST)



Thanks Randy,

I'm sure you know this, but for anyone else who might be confused, the different versions of the S&P500 are the Price Index, Total Return index, and Net Return Index. They differ in how dividends are accounted for. The price return version (^GSPC)does not account for dividends; it only captures the changes in the prices of the index components. The total return version reflects the effects of dividend reinvestment.
So, yes you are right, the ^GSPC (Price Index) is adjusted for dividends, but only in terms of prices, which is why if you calculate the total return using "adjusted prices" for the SPY, ^GSPC or any stock, your calculation would be off by roughly the dividend yield. I'm currently a college/finance student and made this mistake calculating the returns of my University's Student Investment Fund last semester. Using adjusted closing price does not accurately reflect returns on your investment.
If you use "closing prices" and then add the dividend to the respective period- this gives you a much more accurate number in terms of calculating total return.
=(Price1+Div-(Price0)/(Price1) will give you a completely different number than using the adjusted prices..
Btw, this add-in is such a time saver for us, thx again!

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> I just use SPY, since the adjusted returns there include the dividends. You
> wouldn't need to "add" dividends.
>
> ^GSPC *is* dividend adjusted, because the INDEX itself doesn't pay
> dividends.
>
> Otherwise, I think you'll need to use Google and search for another source
> of the data.
>
> On Fri, Feb 24, 2012 at 9:33 PM, naderphilip <naderphilip@...> wrote:
>
> > Hi Randy,
> > Do you know if there's a way to pull in historical prices for the S&P500
> > Total Return Index?
> > The normal ^GSPC is just the price index and isn't very accurate as the
> > dividends aren't really taken into account, even though is says 'adjusted
> > for dividends'.
> > I could use the closing price of SPY and add dividends but it'd be off by
> > a couple bps and I'd rather have the exact figures.
> > Was wondering if you had ever done this..
> >
>

3b.

Re: S&P 500 Total Return History?

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Sat Feb 25, 2012 4:28 pm (PST)



It would depend on whether you consider "total return" as a reinvestment of
the earned dividends or withdrawal of the dividends at the time they are
paid. For example, if I computed a "total return" of 2011 for MMM, your
method wouldn't care whether the $2.20 in dividends was paid on Jan 1st or
on Dec 31st. But there is a value to having it paid earlier.

Adjusted prices would account for reinvestment of the dividends, but it
erroneously reinvests them on the ex-dividend date instead of the payment
date.

And that's excluding tax considerations. Oh, what a tangled web we weave. :)

On Sat, Feb 25, 2012 at 4:43 PM, naderphilip <naderphilip@yahoo.com> wrote:

> If you use "closing prices" and then add the dividend to the respective
> period- this gives you a much more accurate number in terms of calculating
> total return.
> =(Price1+Div-(Price0)/(Price1) will give you a completely different number
> than using the adjusted prices..
>
Recent Activity
Visit Your Group
Need traffic?

Drive customers

With search ads

on Yahoo!

Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Fashion News

What's the word on

fashion and style?

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web

Tidak ada komentar:

Posting Komentar