5 New Messages
Digest #2305
| |
Messages
Sun Aug 5, 2012 1:11 pm (PDT) . Posted by:
"MoeB" spikemgm
Hi Randy,
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.
Thanks
Steve
Sun Aug 5, 2012 1:56 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
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.
>
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.
>
Sun Aug 5, 2012 6:42 pm (PDT) . Posted by:
"Stephan Moebius" spikemgm
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.
>>
>
>
>
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.
>>
>
>
>
Sun Aug 5, 2012 8:16 pm (PDT) . Posted by:
"Keith M. Elis" zarathustra_winced
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). 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.
=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(
iStartDay, 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). 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.
Sun Aug 5, 2012 8:21 pm (PDT) . Posted by:
"Keith M. Elis" zarathustra_winced
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). 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.
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(
iStartDay, 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(
iStartDay, 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). 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.
Tidak ada komentar:
Posting Komentar