13 New Messages
Digest #2306
| 5a Disconnect Between Calculated ROCI and Reported ROCI on Quarterly Re by "highflight1985" highflight1985 5b Re: Disconnect Between Calculated ROCI and Reported ROCI on Quarterl by "Randy Harmelink" rharmelink |
Messages
Mon Aug 6, 2012 8:15 am (PDT) . Posted by:
"David" copthornelabs
Randy,
This may be a redundant request, but do you have any element numbers for FinViz data?
Dave
This may be a redundant request, but do you have any element numbers for FinViz data?
Dave
Mon Aug 6, 2012 8:30 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
No, but check out the two FinViz entries in the LINKS area of the group...
On Mon, Aug 6, 2012 at 8:15 AM, David <davecharlaff@hotmail.com > wrote:
> This may be a redundant request, but do you have any element numbers for
> FinViz data?
>
On Mon, Aug 6, 2012 at 8:15 AM, David <davecharlaff@
> This may be a redundant request, but do you have any element numbers for
> FinViz data?
>
Mon Aug 6, 2012 12:26 pm (PDT) . Posted by:
"dmbstud20" dmbstud20
I am trying to create an Excel formula that gives the total return for a position since it was purchased.
When I export a client portfolio from Morningstar, I am able to calculate the return based on the current market value. However, this does not account for dividends or interest on positions (unless they are reinvested).
Does anyone know a way I can use Excel to account for dividends and interest to get the total return on a position?
Any help would be greatly appreciated.
Thanks,
Nick
When I export a client portfolio from Morningstar, I am able to calculate the return based on the current market value. However, this does not account for dividends or interest on positions (unless they are reinvested).
Does anyone know a way I can use Excel to account for dividends and interest to get the total return on a position?
Any help would be greatly appreciated.
Thanks,
Nick
Mon Aug 6, 2012 12:31 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
What I usually do is collect the adjusted closing prices from the two dates
with smfPricesByDates() and compute the return those two value -- which
accounts for dividends paid. However, if you have an intraday entry price,
that may not be of help. I suppose you could use RCHGetYahooHistory() to
get both the closing price and adjusted closing price from your entry date,
and adjust your entry price with their ratio.
An alternative would be to use the EXCEL XIRR() function on the cash flow
of the position. You can use RCHGetYahooHistory() to get the dividend
history, both dates and amounts.
On Mon, Aug 6, 2012 at 10:04 AM, dmbstud20 <sampalis@wisc.edu > wrote:
> I am trying to create an Excel formula that gives the total return for a
> position since it was purchased.
>
> When I export a client portfolio from Morningstar, I am able to calculate
> the return based on the current market value. However, this does not
> account for dividends or interest on positions (unless they are reinvested).
>
> Does anyone know a way I can use Excel to account for dividends and
> interest to get the total return on a position?
>
> Any help would be greatly appreciated.
>
with smfPricesByDates(
accounts for dividends paid. However, if you have an intraday entry price,
that may not be of help. I suppose you could use RCHGetYahooHistory(
get both the closing price and adjusted closing price from your entry date,
and adjust your entry price with their ratio.
An alternative would be to use the EXCEL XIRR() function on the cash flow
of the position. You can use RCHGetYahooHistory(
history, both dates and amounts.
On Mon, Aug 6, 2012 at 10:04 AM, dmbstud20 <sampalis@wisc.
> I am trying to create an Excel formula that gives the total return for a
> position since it was purchased.
>
> When I export a client portfolio from Morningstar, I am able to calculate
> the return based on the current market value. However, this does not
> account for dividends or interest on positions (unless they are reinvested).
>
> Does anyone know a way I can use Excel to account for dividends and
> interest to get the total return on a position?
>
> Any help would be greatly appreciated.
>
Mon Aug 6, 2012 2:54 pm (PDT) . Posted by:
"Steven" stevenletzer
I am trying to capture data from www.moodys.com For example, at:
http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1
The formula RCHGetTableCell("http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1 ",2,"Market Segment:") - returns Error for all of the table cells.
If it is possible to use GetTableCell, what would the formula for capturing the company name? I am working with the bond CUSIP not the comon stock symbol.
http://www.moodys.
The formula RCHGetTableCell(
If it is possible to use GetTableCell, what would the formula for capturing the company name? I am working with the bond CUSIP not the comon stock symbol.
Mon Aug 6, 2012 3:05 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The data around "Market Segment:" is NOT within an HTML table, so you'd
need to use another function. For example, to capture the market segment:
=smfGetTagContent("
http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1# ","span",1,"Market
Segment:")
However, I'm confused why you would want to extract company name from the
web page, since you needed it in the first place to create the URL? In any
case:
=smfGetTagContent("
http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1#
","h1",-1,"mdcPageTitle")
On Mon, Aug 6, 2012 at 2:54 PM, Steven <stevenletzer@yahoo.com > wrote:
> I am trying to capture data from www.moodys.com For example, at:
>
> http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1
>
> The formula RCHGetTableCell("
> http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1 ",2,"Market
> Segment:") - returns Error for all of the table cells.
>
> If it is possible to use GetTableCell, what would the formula for
> capturing the company name? I am working with the bond CUSIP not the comon
> stock symbol.
>
need to use another function. For example, to capture the market segment:
=smfGetTagContent(
http://www.moodys.
Segment:")
However, I'm confused why you would want to extract company name from the
web page, since you needed it in the first place to create the URL? In any
case:
=smfGetTagContent(
http://www.moodys.
","h1",-1,"mdcPageT
On Mon, Aug 6, 2012 at 2:54 PM, Steven <stevenletzer@
> I am trying to capture data from www.moodys.com For example, at:
>
> http://www.moodys.
>
> The formula RCHGetTableCell(
> http://www.moodys.
> Segment:") - returns Error for all of the table cells.
>
> If it is possible to use GetTableCell, what would the formula for
> capturing the company name? I am working with the bond CUSIP not the comon
> stock symbol.
>
Mon Aug 6, 2012 3:25 pm (PDT) . Posted by:
"Steven" stevenletzer
I am working with a list of CUSIP numbers and capturing description information. The description for each CUSIP is dependent on the firm making the bond offer. [Thik of this as a Craig's List for trading firms making bond offers - we use BondDesk]. The same bond will have different issurer names.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> The data around "Market Segment:" is NOT within an HTML table, so you'd
> need to use another function. For example, to capture the market segment:
>
> =smfGetTagContent("
> http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1# ","span",1,"Market
> Segment:")
>
> However, I'm confused why you would want to extract company name from the
> web page, since you needed it in the first place to create the URL? In any
> case:
>
> =smfGetTagContent("
> http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1#
> ","h1",-1,"mdcPageTitle")
>
> On Mon, Aug 6, 2012 at 2:54 PM, Steven <stevenletzer@...> wrote:
>
> > I am trying to capture data from www.moodys.com For example, at:
> >
> > http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1
> >
> > The formula RCHGetTableCell("
> > http://www.moodys.com/credit-ratings/Dominion-Resources-Inc-credit-rating-243115?emvalue=CUS:25746UBH1 ",2,"Market
> > Segment:") - returns Error for all of the table cells.
> >
> > If it is possible to use GetTableCell, what would the formula for
> > capturing the company name? I am working with the bond CUSIP not the comon
> > stock symbol.
> >
>
--- In smf_addin@yahoogrou
>
> The data around "Market Segment:" is NOT within an HTML table, so you'd
> need to use another function. For example, to capture the market segment:
>
> =smfGetTagContent(
> http://www.moodys.
> Segment:")
>
> However, I'm confused why you would want to extract company name from the
> web page, since you needed it in the first place to create the URL? In any
> case:
>
> =smfGetTagContent(
> http://www.moodys.
> ","h1",-1,"mdcPageT
>
> On Mon, Aug 6, 2012 at 2:54 PM, Steven <stevenletzer@
>
> > I am trying to capture data from www.moodys.com For example, at:
> >
> > http://www.moodys.
> >
> > The formula RCHGetTableCell(
> > http://www.moodys.
> > Segment:") - returns Error for all of the table cells.
> >
> > If it is possible to use GetTableCell, what would the formula for
> > capturing the company name? I am working with the bond CUSIP not the comon
> > stock symbol.
> >
>
Mon Aug 6, 2012 2:56 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Have you looked at this screener?
http://www.etfscreen.com/screener.php
How do you account for volume related to creation and redemption units?
For open/last/change/volume in the table below, I would use
RCHGetYahooQuotes().
10-day average could come from RCHGetYahooHistory(), but would be slower:
=AVERAGE(RCHGetYahooHistory("SPY",,,,,,,,"V",0,,,10,1))
Or, to ignore today's data:
=AVERAGE(RCHGetYahooHistory("SPY",YEAR(C13-20),MONTH(C13-20),DAY(C13-20),YEAR(C13-1),MONTH(C13-1),DAY(C13-1),,"V",0,,,10,1))
...where C13 contains:
=RCHGetYahooQuotes("SPY","d1")
On Sun, Aug 5, 2012 at 6:42 PM, Stephan Moebius <smoebius@gmail.com > wrote:
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500 139.35
> 165,711,000 157,824,975 -4.76% xlf financials 14.82 74,085,000 80,304,841
> 8.40% eem emerging mkts 39.93 52,334,500 60,513,140 15.63% iwm
> russell2000 78.61 51,975,500 52,414,149 0.84% vxx volatility 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas 19.77 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshares.com/funds/performance/performance_pricing_faqs.html
>
>
> *Don't confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF's market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ—especially in volatile markets (see ETF Pricing Glossary<http://www.proshares.com/ETFPricingGlossary.html >).
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
http://www.etfscree
How do you account for volume related to creation and redemption units?
For open/last/change/
RCHGetYahooQuotes(
10-day average could come from RCHGetYahooHistory(
=AVERAGE(RCHGetYaho
Or, to ignore today's data:
=AVERAGE(RCHGetYaho
...where C13 contains:
=RCHGetYahooQuotes(
On Sun, Aug 5, 2012 at 6:42 PM, Stephan Moebius <smoebius@gmail.
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500 139.35
> 165,711,000 157,824,975 -4.76% xlf financials 14.82 74,085,000 80,304,841
> 8.40% eem emerging mkts 39.93 52,334,500 60,513,140 15.63% iwm
> russell2000 78.61 51,975,500 52,414,149 0.84% vxx volatility 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas 19.77 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshare
>
>
> *Don't confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF's market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ—especially in volatile markets (see ETF Pricing Glossary<http://www.proshare
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
Mon Aug 6, 2012 6:59 pm (PDT) . Posted by:
"Stephan Moebius" spikemgm
Thanks Keith,
But i haven't got to the point of code yet, the only thing i know how to do
is right click on the bottom tab and then my eye's glaze over...once i
set-up randys stuff then i can start with coding. Can you recommend a good
excel vb beginner book.
Steve
On Sun, Aug 5, 2012 at 8:20 PM, Keith M. Elis
<zarathustra_winced@yahoo.com >wrote:
> **
>
>
> Oops. Ignore my last post. This is the working code.
>
> Public Function ELIS_UTY_AverageVolume(sTicker As String, _
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarReport "Getting average volume for " & sTicker & ", " &
> lDaysBack & " periods back from " & Format(lDateNum)
>
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
>
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(sTicker, iStartYear, iStartMonth, iStartDay,
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVolume = Application.WorksheetFunction.Average(aHolder)
> End Function
>
> --- On *Sun, 8/5/12, Keith M. Elis <zarathustra_winced@yahoo.com >* wrote:
>
>
> From: Keith M. Elis <zarathustra_winced@yahoo.com >
>
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogroups.com
> Date: Sunday, August 5, 2012, 11:14 PM
>
>
>
>
> Here's a function for average volume. Examples:
>
> =ELIS_UTY_AverageVolume("SPY",,10)
>
> Returns 10 day avg volume for SPY as of the last close. Or for historical
> calcs:
>
> =ELIS_UTY_AverageVolume("IYM","8/23/2004",90)
>
> Returns 90 day average volume for IYM as of the close on 8/23/2004
>
>
>
> Public Function ELIS_UTY_AverageVolume(sTicker As String, _
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarReport "Getting average volume for " & sTicker & ", " &
> lDaysBack & " periods back from " & Format(lDateNum)
> If iPeriodsBack = -1 And sPeriod = "Null" Then
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
> End If
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(sTicker, iStartYear, iStartMonth, iStartDay,
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVolume = Application.WorksheetFunction.Average(aHolder)
> End Function
>
> --- On *Sun, 8/5/12, Stephan Moebius <smoebius@gmail.com >* wrote:
>
>
> From: Stephan Moebius <smoebius@gmail.com >
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogroups.com
> Date: Sunday, August 5, 2012, 9:42 PM
>
>
>
> Thanks,
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500
> 139.35
> 165,711,000 157,824,975 -4.76% xlf financials
> 14.82
> 74,085,000 80,304,841 8.40% eem emerging mkts 39.93
> 52,334,500 60,513,140 15.63% iwm russell2000
> 78.61
> 51,975,500 52,414,149 0.84% vxx volatility
> 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas
> 19.77
> 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshares.com/funds/performance/performance_pricing_faqs.html
>
>
> *Don�t confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF�s market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshares.com/ETFPricingGlossary.html >).
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
> On Sun, Aug 5, 2012 at 1:56 PM, Randy Harmelink <rharmelink@gmail.com >wrote:
>
> **
>
>
> This template:
>
> RCHGetTableCell-Template-Yahoo-ETF-Data.xls
>
> ...has examples of getting data from the ETF browser. But I don't know of
> any way to limit the results ahead of time to just those over 2 million in
> volume. If an ETF follows an index, wouldn't volume be irrelevant? It's not
> like the price is driven by supply and demand, or trading.
>
> Why not just use DIA instead of the Dow? Or, BarChart is pretty
> straight-forward:
>
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",2,">$DOWI")
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",3,">$DOWI")
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",4,">$DOWI")
>
>
> On Sun, Aug 5, 2012 at 1:09 PM, MoeB <smoebius@gmail.com > wrote:
>
>
> Trying to download EOD etf data from,
>
> http://finance.yahoo.com/etf/browser/tv?k=6&c=0&f=0&cs=1&ce=1441
>
> I need: Volume(3months avg) >2mil, with Volume EOD and ticker only.
>
> There are about 75 that are over 2mil avg daily, not having any luck with
> that page, also is there a way to return just those over 2mil because the
> list changes everyday: up one or two/down one or two.
>
> Also anyone find a EOD Dow l1 and C site.
>
>
>
>
>
>
>
>
>
But i haven't got to the point of code yet, the only thing i know how to do
is right click on the bottom tab and then my eye's glaze over...once i
set-up randys stuff then i can start with coding. Can you recommend a good
excel vb beginner book.
Steve
On Sun, Aug 5, 2012 at 8:20 PM, Keith M. Elis
<zarathustra_
> **
>
>
> Oops. Ignore my last post. This is the working code.
>
> Public Function ELIS_UTY_AverageVol
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarR
> lDaysBack & " periods back from " & Format(lDateNum)
>
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
>
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVol
> End Function
>
> --- On *Sun, 8/5/12, Keith M. Elis <zarathustra_
>
>
> From: Keith M. Elis <zarathustra_
>
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogrou
> Date: Sunday, August 5, 2012, 11:14 PM
>
>
>
>
> Here's a function for average volume. Examples:
>
> =ELIS_UTY_AverageVo
>
> Returns 10 day avg volume for SPY as of the last close. Or for historical
> calcs:
>
> =ELIS_UTY_AverageVo
>
> Returns 90 day average volume for IYM as of the close on 8/23/2004
>
>
>
> Public Function ELIS_UTY_AverageVol
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarR
> lDaysBack & " periods back from " & Format(lDateNum)
> If iPeriodsBack = -1 And sPeriod = "Null" Then
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
> End If
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVol
> End Function
>
> --- On *Sun, 8/5/12, Stephan Moebius <smoebius@gmail.
>
>
> From: Stephan Moebius <smoebius@gmail.
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogrou
> Date: Sunday, August 5, 2012, 9:42 PM
>
>
>
> Thanks,
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500
> 139.35
> 165,711,000 157,824,975 -4.76% xlf financials
> 14.82
> 74,085,000 80,304,841 8.40% eem emerging mkts 39.93
> 52,334,500 60,513,140 15.63% iwm russell2000
> 78.61
> 51,975,500 52,414,149 0.84% vxx volatility
> 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas
> 19.77
> 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshare
>
>
> *Don�t confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF�s market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshare
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
> On Sun, Aug 5, 2012 at 1:56 PM, Randy Harmelink <rharmelink@gmail.
>
> **
>
>
> This template:
>
> RCHGetTableCell-
>
> ...has examples of getting data from the ETF browser. But I don't know of
> any way to limit the results ahead of time to just those over 2 million in
> volume. If an ETF follows an index, wouldn't volume be irrelevant? It's not
> like the price is driven by supply and demand, or trading.
>
> Why not just use DIA instead of the Dow? Or, BarChart is pretty
> straight-forward:
>
> =RCHGetTableCell(
> =RCHGetTableCell(
> =RCHGetTableCell(
>
>
> On Sun, Aug 5, 2012 at 1:09 PM, MoeB <smoebius@gmail.
>
>
> Trying to download EOD etf data from,
>
> http://finance.
>
> I need: Volume(3months avg) >2mil, with Volume EOD and ticker only.
>
> There are about 75 that are over 2mil avg daily, not having any luck with
> that page, also is there a way to return just those over 2mil because the
> list changes everyday: up one or two/down one or two.
>
> Also anyone find a EOD Dow l1 and C site.
>
>
>
>
>
>
>
>
>
Mon Aug 6, 2012 7:30 pm (PDT) . Posted by:
"Stephan Moebius" spikemgm
Hi Randy,
I haven't used the etfscreen, I'm happy with my own.
As far as vol for C&R units, sure you have to be aware they can dump units
anytime, but if you play the most liquid that rarely comes up, the
increased vol is legitimate supply and demand. They have to track the
underlying. The only time i saw it happen is with the vxx, the nav was very
close to the vix and the vix was screaming higher but the vxx just sat
there, i think for a day or two.
Anyway, you have to be more concerned with the nav getting to far removed
from the underlying, a good example was tvix trading ~14 and the nav was
~6, guess what they came in overnight one day and boom, the next day it was
at 6 ouch! so, look at the nav, look at the volume and play for a day or
two is my modus operandi.
http://us.ishares.com/understand_etf/fundamental/etf_creation_redemption.htm
Thanks for the help, now i got to go play with my spreadsheets.
Steve
On Mon, Aug 6, 2012 at 2:56 PM, Randy Harmelink <rharmelink@gmail.com >wrote:
> **
>
>
> Have you looked at this screener?
>
> http://www.etfscreen.com/screener.php
>
> How do you account for volume related to creation and redemption units?
>
> For open/last/change/volume in the table below, I would use
> RCHGetYahooQuotes().
>
> 10-day average could come from RCHGetYahooHistory(), but would be slower:
>
> =AVERAGE(RCHGetYahooHistory("SPY",,,,,,,,"V",0,,,10,1))
>
> Or, to ignore today's data:
>
>
> =AVERAGE(RCHGetYahooHistory("SPY",YEAR(C13-20),MONTH(C13-20),DAY(C13-20),YEAR(C13-1),MONTH(C13-1),DAY(C13-1),,"V",0,,,10,1))
>
> ...where C13 contains:
>
> =RCHGetYahooQuotes("SPY","d1")
>
>
> On Sun, Aug 5, 2012 at 6:42 PM, Stephan Moebius <smoebius@gmail.com >wrote:
>
>>
>> I use volume change from avg and nav values to find trades intraday or
>> longer.
>>
>> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
>> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
>> conditional formattng built in it's alot faster to check where the markets
>> leaning then looking at 75 different indexs. I actually need 10day avg vol
>> if you know where to find that.
>>
>> What do you recommend formula wise for open and change to complete this
>> one.
>> sym name open last change avg vol volume vol % chg spy s&p500 139.35
>> 165,711,000 157,824,975 -4.76% xlf financials 14.82 74,085,000
>> 80,304,841 8.40% eem emerging mkts 39.93 52,334,500 60,513,140 15.63%
>> iwm russell2000 78.61 51,975,500 52,414,149 0.84% vxx volatility 12.16
>> 50,255,600 43,137,627 -14.16% ung nat gas 19.77 12,788,400 12,298,565
>>
>> -3.83%
>>
>> Link for anyone wanting to know more about how etf's trade. excerpt
>>
>> http://www.proshares.com/funds/performance/performance_pricing_faqs.html
>>
>>
>> *Don�t confuse market price with NAV.* ETF performance is tracked using
>> net asset value (NAV). But investors purchase and sell ETFs at market
>> price. Although an ETF�s market price, its NAV, and its intraday values
>> (real-time estimates of NAV) are typically close, these values may
>> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshares.com/ETFPricingGlossary.html >).
>> During the trading day the market price is usually close to the intraday
>> value, and at market close it is usually close to the NAV, but these values
>> may diverge.
>>
>> The result: the performance you experience, which is based on market
>> prices, can differ from stated performance, which is based on NAV. This
>> difference arises because you may have purchased or sold your ETF shares at
>> a premium or discount to the NAV.
>>
>> In this respect, ETFs are just like stocks (they trade throughout the
>> day) and unlike mutual funds (which trade once a day, at NAV). This ability
>> to buy and sell throughout the day is one of the primary benefits of ETFs.
>>
>>
>
>
>
I haven't used the etfscreen, I'm happy with my own.
As far as vol for C&R units, sure you have to be aware they can dump units
anytime, but if you play the most liquid that rarely comes up, the
increased vol is legitimate supply and demand. They have to track the
underlying. The only time i saw it happen is with the vxx, the nav was very
close to the vix and the vix was screaming higher but the vxx just sat
there, i think for a day or two.
Anyway, you have to be more concerned with the nav getting to far removed
from the underlying, a good example was tvix trading ~14 and the nav was
~6, guess what they came in overnight one day and boom, the next day it was
at 6 ouch! so, look at the nav, look at the volume and play for a day or
two is my modus operandi.
http://us.ishares.
Thanks for the help, now i got to go play with my spreadsheets.
Steve
On Mon, Aug 6, 2012 at 2:56 PM, Randy Harmelink <rharmelink@gmail.
> **
>
>
> Have you looked at this screener?
>
> http://www.etfscree
>
> How do you account for volume related to creation and redemption units?
>
> For open/last/change/
> RCHGetYahooQuotes(
>
> 10-day average could come from RCHGetYahooHistory(
>
> =AVERAGE(RCHGetYaho
>
> Or, to ignore today's data:
>
>
> =AVERAGE(RCHGetYaho
>
> ...where C13 contains:
>
> =RCHGetYahooQuotes(
>
>
> On Sun, Aug 5, 2012 at 6:42 PM, Stephan Moebius <smoebius@gmail.
>
>>
>> I use volume change from avg and nav values to find trades intraday or
>> longer.
>>
>> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
>> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
>> conditional formattng built in it's alot faster to check where the markets
>> leaning then looking at 75 different indexs. I actually need 10day avg vol
>> if you know where to find that.
>>
>> What do you recommend formula wise for open and change to complete this
>> one.
>> sym name open last change avg vol volume vol % chg spy s&p500 139.35
>> 165,711,000 157,824,975 -4.76% xlf financials 14.82 74,085,000
>> 80,304,841 8.40% eem emerging mkts 39.93 52,334,500 60,513,140 15.63%
>> iwm russell2000 78.61 51,975,500 52,414,149 0.84% vxx volatility 12.16
>> 50,255,600 43,137,627 -14.16% ung nat gas 19.77 12,788,400 12,298,565
>>
>> -3.83%
>>
>> Link for anyone wanting to know more about how etf's trade. excerpt
>>
>> http://www.proshare
>>
>>
>> *Don�t confuse market price with NAV.* ETF performance is tracked using
>> net asset value (NAV). But investors purchase and sell ETFs at market
>> price. Although an ETF�s market price, its NAV, and its intraday values
>> (real-time estimates of NAV) are typically close, these values may
>> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshare
>> During the trading day the market price is usually close to the intraday
>> value, and at market close it is usually close to the NAV, but these values
>> may diverge.
>>
>> The result: the performance you experience, which is based on market
>> prices, can differ from stated performance, which is based on NAV. This
>> difference arises because you may have purchased or sold your ETF shares at
>> a premium or discount to the NAV.
>>
>> In this respect, ETFs are just like stocks (they trade throughout the
>> day) and unlike mutual funds (which trade once a day, at NAV). This ability
>> to buy and sell throughout the day is one of the primary benefits of ETFs.
>>
>>
>
>
>
Mon Aug 6, 2012 7:56 pm (PDT) . Posted by:
"zarathustra_winced@yahoo.com" zarathustra_winced
I'm self taught, via lots of google searches. I'm pretty sure almost anyone can learn vba, though.
Sent via BlackBerry by AT&T
-----Original Message-----
From: Stephan Moebius <smoebius@gmail.com >
Sender: smf_addin@yahoogroups.com
Date: Mon, 6 Aug 2012 18:58:03
To: <smf_addin@yahoogroups.com >
Reply-To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] ETF Data
Thanks Keith,
But i haven't got to the point of code yet, the only thing i know how to do
is right click on the bottom tab and then my eye's glaze over...once i
set-up randys stuff then i can start with coding. Can you recommend a good
excel vb beginner book.
Steve
On Sun, Aug 5, 2012 at 8:20 PM, Keith M. Elis
<zarathustra_winced@yahoo.com >wrote:
> **
>
>
> Oops. Ignore my last post. This is the working code.
>
> Public Function ELIS_UTY_AverageVolume(sTicker As String, _
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarReport "Getting average volume for " & sTicker & ", " &
> lDaysBack & " periods back from " & Format(lDateNum)
>
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
>
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(sTicker, iStartYear, iStartMonth, iStartDay,
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVolume = Application.WorksheetFunction.Average(aHolder)
> End Function
>
> --- On *Sun, 8/5/12, Keith M. Elis <zarathustra_winced@yahoo.com >* wrote:
>
>
> From: Keith M. Elis <zarathustra_winced@yahoo.com >
>
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogroups.com
> Date: Sunday, August 5, 2012, 11:14 PM
>
>
>
>
> Here's a function for average volume. Examples:
>
> =ELIS_UTY_AverageVolume("SPY",,10)
>
> Returns 10 day avg volume for SPY as of the last close. Or for historical
> calcs:
>
> =ELIS_UTY_AverageVolume("IYM","8/23/2004",90)
>
> Returns 90 day average volume for IYM as of the close on 8/23/2004
>
>
>
> Public Function ELIS_UTY_AverageVolume(sTicker As String, _
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarReport "Getting average volume for " & sTicker & ", " &
> lDaysBack & " periods back from " & Format(lDateNum)
> If iPeriodsBack = -1 And sPeriod = "Null" Then
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
> End If
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(sTicker, iStartYear, iStartMonth, iStartDay,
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVolume = Application.WorksheetFunction.Average(aHolder)
> End Function
>
> --- On *Sun, 8/5/12, Stephan Moebius <smoebius@gmail.com >* wrote:
>
>
> From: Stephan Moebius <smoebius@gmail.com >
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogroups.com
> Date: Sunday, August 5, 2012, 9:42 PM
>
>
>
> Thanks,
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500
> 139.35
> 165,711,000 157,824,975 -4.76% xlf financials
> 14.82
> 74,085,000 80,304,841 8.40% eem emerging mkts 39.93
> 52,334,500 60,513,140 15.63% iwm russell2000
> 78.61
> 51,975,500 52,414,149 0.84% vxx volatility
> 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas
> 19.77
> 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshares.com/funds/performance/performance_pricing_faqs.html
>
>
> *Don�t confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF�s market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshares.com/ETFPricingGlossary.html >).
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
> On Sun, Aug 5, 2012 at 1:56 PM, Randy Harmelink <rharmelink@gmail.com >wrote:
>
> **
>
>
> This template:
>
> RCHGetTableCell-Template-Yahoo-ETF-Data.xls
>
> ...has examples of getting data from the ETF browser. But I don't know of
> any way to limit the results ahead of time to just those over 2 million in
> volume. If an ETF follows an index, wouldn't volume be irrelevant? It's not
> like the price is driven by supply and demand, or trading.
>
> Why not just use DIA instead of the Dow? Or, BarChart is pretty
> straight-forward:
>
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",2,">$DOWI")
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",3,">$DOWI")
> =RCHGetTableCell("http://www.barchart.com/stocks/indices.php ",4,">$DOWI")
>
>
> On Sun, Aug 5, 2012 at 1:09 PM, MoeB <smoebius@gmail.com > wrote:
>
>
> Trying to download EOD etf data from,
>
> http://finance.yahoo.com/etf/browser/tv?k=6&c=0&f=0&cs=1&ce=1441
>
> I need: Volume(3months avg) >2mil, with Volume EOD and ticker only.
>
> There are about 75 that are over 2mil avg daily, not having any luck with
> that page, also is there a way to return just those over 2mil because the
> list changes everyday: up one or two/down one or two.
>
> Also anyone find a EOD Dow l1 and C site.
>
>
>
>
>
>
>
>
>
Sent via BlackBerry by AT&T
-----Original Message-----
From: Stephan Moebius <smoebius@gmail.
Sender: smf_addin@yahoogrou
Date: Mon, 6 Aug 2012 18:58:03
To: <smf_addin@yahoogrou
Reply-To: smf_addin@yahoogrou
Subject: Re: [smf_addin] ETF Data
Thanks Keith,
But i haven't got to the point of code yet, the only thing i know how to do
is right click on the bottom tab and then my eye's glaze over...once i
set-up randys stuff then i can start with coding. Can you recommend a good
excel vb beginner book.
Steve
On Sun, Aug 5, 2012 at 8:20 PM, Keith M. Elis
<zarathustra_
> **
>
>
> Oops. Ignore my last post. This is the working code.
>
> Public Function ELIS_UTY_AverageVol
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarR
> lDaysBack & " periods back from " & Format(lDateNum)
>
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
>
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVol
> End Function
>
> --- On *Sun, 8/5/12, Keith M. Elis <zarathustra_
>
>
> From: Keith M. Elis <zarathustra_
>
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogrou
> Date: Sunday, August 5, 2012, 11:14 PM
>
>
>
>
> Here's a function for average volume. Examples:
>
> =ELIS_UTY_AverageVo
>
> Returns 10 day avg volume for SPY as of the last close. Or for historical
> calcs:
>
> =ELIS_UTY_AverageVo
>
> Returns 90 day average volume for IYM as of the close on 8/23/2004
>
>
>
> Public Function ELIS_UTY_AverageVol
> Optional lDateNum As Date = 0, _
> Optional lDaysBack As Long = 90)
> As Variant
> ' datenum = zero is the last average volume date available, otherwise use
> a past date for historical data
> Dim aHolder() As Variant
> Dim daStartDate As Date
> Dim iStartDay As Integer
> Dim iStartMonth As Integer
> Dim iStartYear As Integer
> Dim iEndDay As Integer
> Dim iEndMonth As Integer
> Dim iEndYear As Integer
> Dim iCounter As Integer
> ELIS_UTY_StatusBarR
> lDaysBack & " periods back from " & Format(lDateNum)
> If iPeriodsBack = -1 And sPeriod = "Null" Then
> If lDateNum = 0 Then lDateNum = Date - 1
> If lDateNum > Date Then lDateNum = Date - 1
> End If
> ReDim aHolder(1 To lDaysBack + 1)
> daStartDate = lDateNum - lDaysBack - 1
> iStartDay = day(daStartDate)
> iStartMonth = Month(daStartDate)
> iStartYear = Year(daStartDate)
> iEndDay = day(lDateNum)
> iEndMonth = Month(lDateNum)
> iEndYear = Year(lDateNum)
> aHolder = rchgetyahoohistory(
> iEndYear, iEndMonth, iEndDay, "d", "v", 0, , , lDaysBack + 1, 1)
> ELIS_UTY_AverageVol
> End Function
>
> --- On *Sun, 8/5/12, Stephan Moebius <smoebius@gmail.
>
>
> From: Stephan Moebius <smoebius@gmail.
> Subject: Re: [smf_addin] ETF Data
> To: smf_addin@yahoogrou
> Date: Sunday, August 5, 2012, 9:42 PM
>
>
>
> Thanks,
>
> I use volume change from avg and nav values to find trades intraday or
> longer.
>
> So, if you go back on aug 1st UNG (nat gas) had a 200% jump in vol to
> avg. friday below it's now back ~avg. Anyway for me, i trade etfs and with
> conditional formattng built in it's alot faster to check where the markets
> leaning then looking at 75 different indexs. I actually need 10day avg vol
> if you know where to find that.
>
> What do you recommend formula wise for open and change to complete this
> one.
> sym name open last change avg vol volume vol % chg spy s&p500
> 139.35
> 165,711,000 157,824,975 -4.76% xlf financials
> 14.82
> 74,085,000 80,304,841 8.40% eem emerging mkts 39.93
> 52,334,500 60,513,140 15.63% iwm russell2000
> 78.61
> 51,975,500 52,414,149 0.84% vxx volatility
> 12.16
> 50,255,600 43,137,627 -14.16% ung nat gas
> 19.77
> 12,788,400 12,298,565
>
> -3.83%
>
> Link for anyone wanting to know more about how etf's trade. excerpt
>
> http://www.proshare
>
>
> *Don�t confuse market price with NAV.* ETF performance is tracked using
> net asset value (NAV). But investors purchase and sell ETFs at market
> price. Although an ETF�s market price, its NAV, and its intraday values
> (real-time estimates of NAV) are typically close, these values may
> differ�especially in volatile markets (see ETF Pricing Glossary<http://www.proshare
> During the trading day the market price is usually close to the intraday
> value, and at market close it is usually close to the NAV, but these values
> may diverge.
>
> The result: the performance you experience, which is based on market
> prices, can differ from stated performance, which is based on NAV. This
> difference arises because you may have purchased or sold your ETF shares at
> a premium or discount to the NAV.
>
> In this respect, ETFs are just like stocks (they trade throughout the day)
> and unlike mutual funds (which trade once a day, at NAV). This ability to
> buy and sell throughout the day is one of the primary benefits of ETFs.
>
>
> On Sun, Aug 5, 2012 at 1:56 PM, Randy Harmelink <rharmelink@gmail.
>
> **
>
>
> This template:
>
> RCHGetTableCell-
>
> ...has examples of getting data from the ETF browser. But I don't know of
> any way to limit the results ahead of time to just those over 2 million in
> volume. If an ETF follows an index, wouldn't volume be irrelevant? It's not
> like the price is driven by supply and demand, or trading.
>
> Why not just use DIA instead of the Dow? Or, BarChart is pretty
> straight-forward:
>
> =RCHGetTableCell(
> =RCHGetTableCell(
> =RCHGetTableCell(
>
>
> On Sun, Aug 5, 2012 at 1:09 PM, MoeB <smoebius@gmail.
>
>
> Trying to download EOD etf data from,
>
> http://finance.
>
> I need: Volume(3months avg) >2mil, with Volume EOD and ticker only.
>
> There are about 75 that are over 2mil avg daily, not having any luck with
> that page, also is there a way to return just those over 2mil because the
> list changes everyday: up one or two/down one or two.
>
> Also anyone find a EOD Dow l1 and C site.
>
>
>
>
>
>
>
>
>
Tue Aug 7, 2012 12:05 am (PDT) . Posted by:
"highflight1985" highflight1985
Hello! Firstly, since this is my first post, I must say that I am very grateful that this add-in was developed. I think it will prove to be very useful; and much cheaper than Investools! :p
So, I'm a Rule #1 learner. I am, in fact, a complete novice investor. I've never traded a dollar in my life. I'm mostly doing this for the learning aspect.
Anyway, on to business. I'm attempting to calculate ROCI-TTM based on the most recent 4 Quarterly Reports. I have two versions of this. First, is the actual ROCI reported by AdvFN for each quarter. Secondly, I'm roughly calculating ROCI with the following formula (per Phil Town): Total Net Income / (Total Equity + Long-Term Debt)
With this formula used on the *Annual* Reports, it seems to be matching up with AdvFN's reported ROCI 99% of the time. However, used in the Quarterly Reports, the calculated ROCI is always significantly lower than AdvFN's reported ROCI.
For example, AAPL. AAPL's Annual ROCI numbers for the last 10 years match up perfectly with my calculated ROCI.
Here's where things go wrong: When you look at the Quarterly report, AdvFN is reporting ROCI numbers as follows (rounded) 36%, 38%, 37%, and 34%; most recent to oldest. Average TTM: 36% (calculated based on the average of these individual ROCI's).
The calculated version's numbers: 8%, 11%, 15%, 9%. Average TTM: 11%.
There's a huge difference between 36% TTM and 11% TTM. And the calculated value is always lower. I've tried different ways of calculating it: Sum up all Quarterly Net Income, sum up Equity, and sum up Debt and calculate appropriately; Average each number separately and then calculate based on the individual averages; calculate each quarter's ROCI and average all four ROCI's together. It doesn't make a difference. It works out to be the same number no matter how you slice it, and it's always significantly lower than the given Quarterly ROCI by AdvFN.
Why the disconnect? Why is it spot on for annual reports but so far off on the quarterly reports when the formula isn't changed?
Here are the keys I'm using:
Annual:
---ROIC: 7786 - 7795
---Total Net Income: 5666 - 5675
---Equity: 6556 - 6565
---Debt: 6376 - 6385
Quarterly:
---ROIC: 13186 - 13189
---Total Net Income: 8946 - 8949
---Equity: 10726 - 10729
---Debt 10366 - 10369
Any help would be greatly appreciated!
Regards,
HighFlight1985
So, I'm a Rule #1 learner. I am, in fact, a complete novice investor. I've never traded a dollar in my life. I'm mostly doing this for the learning aspect.
Anyway, on to business. I'm attempting to calculate ROCI-TTM based on the most recent 4 Quarterly Reports. I have two versions of this. First, is the actual ROCI reported by AdvFN for each quarter. Secondly, I'm roughly calculating ROCI with the following formula (per Phil Town): Total Net Income / (Total Equity + Long-Term Debt)
With this formula used on the *Annual* Reports, it seems to be matching up with AdvFN's reported ROCI 99% of the time. However, used in the Quarterly Reports, the calculated ROCI is always significantly lower than AdvFN's reported ROCI.
For example, AAPL. AAPL's Annual ROCI numbers for the last 10 years match up perfectly with my calculated ROCI.
Here's where things go wrong: When you look at the Quarterly report, AdvFN is reporting ROCI numbers as follows (rounded) 36%, 38%, 37%, and 34%; most recent to oldest. Average TTM: 36% (calculated based on the average of these individual ROCI's).
The calculated version's numbers: 8%, 11%, 15%, 9%. Average TTM: 11%.
There's a huge difference between 36% TTM and 11% TTM. And the calculated value is always lower. I've tried different ways of calculating it: Sum up all Quarterly Net Income, sum up Equity, and sum up Debt and calculate appropriately; Average each number separately and then calculate based on the individual averages; calculate each quarter's ROCI and average all four ROCI's together. It doesn't make a difference. It works out to be the same number no matter how you slice it, and it's always significantly lower than the given Quarterly ROCI by AdvFN.
Why the disconnect? Why is it spot on for annual reports but so far off on the quarterly reports when the formula isn't changed?
Here are the keys I'm using:
Annual:
---ROIC: 7786 - 7795
---Total Net Income: 5666 - 5675
---Equity: 6556 - 6565
---Debt: 6376 - 6385
Quarterly:
---ROIC: 13186 - 13189
---Total Net Income: 8946 - 8949
---Equity: 10726 - 10729
---Debt 10366 - 10369
Any help would be greatly appreciated!
Regards,
HighFlight1985
Tue Aug 7, 2012 12:17 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
How could you NOT change the formula? For the quarterly, you need to
accumulate quarterly data (or adjust for year-to-date reported number) to
come up with a match.
I would suggest picking a fiscal year-end, then seeing how the numbers flow
to it from the quarterly values. That should tell you how your quarterly
formula has to change to match the annual. After all, the quarterly and
annual should match for the same year-end fiscal period, right?
On Mon, Aug 6, 2012 at 10:58 PM, highflight1985 <highflight1985@gmail.com >wrote:
>
> Why the disconnect? Why is it spot on for annual reports but so far off on
> the quarterly reports when the formula isn't changed?
>
accumulate quarterly data (or adjust for year-to-date reported number) to
come up with a match.
I would suggest picking a fiscal year-end, then seeing how the numbers flow
to it from the quarterly values. That should tell you how your quarterly
formula has to change to match the annual. After all, the quarterly and
annual should match for the same year-end fiscal period, right?
On Mon, Aug 6, 2012 at 10:58 PM, highflight1985 <highflight1985@
>
> Why the disconnect? Why is it spot on for annual reports but so far off on
> the quarterly reports when the formula isn't changed?
>
Tidak ada komentar:
Posting Komentar