12 Messages
Digest #3795
Messages
Fri Sep 9, 2016 6:45 am (PDT) . Posted by:
colinmmoors
Hi,
I'm having an issue where one or more of the csv files I pull down from yahoo is not complete.
I have a formula to check if the latest date matches for all files pulled down, and if they don't, I force recalc manually.
Wondering if I can use the formula to force recalc?
Also - any solution for csv files missing latest data?
thanks,
Colin
I'm having an issue where one or more of the csv files I pull down from yahoo is not complete.
I have a formula to check if the latest date matches for all files pulled down, and if they don't, I force recalc manually.
Wondering if I can use the formula to force recalc?
Also - any solution for csv files missing latest data?
thanks,
Colin
Fri Sep 9, 2016 7:06 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'd need a specific example before I could give any help...
For example, I know someone had some issues with Yahoo historical quotes
the other day, with the latest day missing. Sometimes this can happen
because Yahoo hasn't done their updates yet. Even worse, it can happen
seemingly random because only one server hasn't been updated -- so the
error only occurs if you happen to get your data from that server instead
of one of the many others.
You could certainly write some kind of VBA routine that checks the data and
redoes a request of some type if it's not what you expect...
On Fri, Sep 9, 2016 at 6:45 AM, colinmmoors@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm having an issue where one or more of the csv files I pull down from
> yahoo is not complete.
> I have a formula to check if the latest date matches for all files pulled
> down, and if they don't, I force recalc manually.
> Wondering if I can use the formula to force recalc?
> Also - any solution for csv files missing latest data?
>
For example, I know someone had some issues with Yahoo historical quotes
the other day, with the latest day missing. Sometimes this can happen
because Yahoo hasn't done their updates yet. Even worse, it can happen
seemingly random because only one server hasn't been updated -- so the
error only occurs if you happen to get your data from that server instead
of one of the many others.
You could certainly write some kind of VBA routine that checks the data and
redoes a request of some type if it's not what you expect...
On Fri, Sep 9, 2016 at 6:45 AM, colinmmoors@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I'm having an issue where one or more of the csv files I pull down from
> yahoo is not complete.
> I have a formula to check if the latest date matches for all files pulled
> down, and if they don't, I force recalc manually.
> Wondering if I can use the formula to force recalc?
> Also - any solution for csv files missing latest data?
>
Fri Sep 9, 2016 7:19 am (PDT) . Posted by:
colinmmoors
It must be that one yahoo server is not updating the last few days; I'm pulling historical .csv files for tvix, uvxy, spy, vxx, etc
Every time I force recalc, one or more is missing the latest data.
I tried pulling .csv history from google, but it doesn't have adjusted price; any other places to pull from?
thanks,
Colin
Every time I force recalc, one or more is missing the latest data.
I tried pulling .csv history from google, but it doesn't have adjusted price; any other places to pull from?
thanks,
Colin
Fri Sep 9, 2016 8:09 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I recently looked at the various free CSV download services -- NASDAQ,
Google, MorningStar, Yahoo -- and Yahoo was the only one doing things the
way I want them done, such as dividend adjusting.
On Fri, Sep 9, 2016 at 7:19 AM, colinmmoors@
...wrote:
>
> It must be that one yahoo server is not updating the last few days; I'm
> pulling historical .csv files for tvix, uvxy, spy, vxx, etc
> Every time I force recalc, one or more is missing the latest data.
> I tried pulling .csv history from google, but it doesn't have adjusted
> price; any other places to pull from?
>
Google, MorningStar, Yahoo -- and Yahoo was the only one doing things the
way I want them done, such as dividend adjusting.
On Fri, Sep 9, 2016 at 7:19 AM, colinmmoors@
...wrote:
>
> It must be that one yahoo server is not updating the last few days; I'm
> pulling historical .csv files for tvix, uvxy, spy, vxx, etc
> Every time I force recalc, one or more is missing the latest data.
> I tried pulling .csv history from google, but it doesn't have adjusted
> price; any other places to pull from?
>
Fri Sep 9, 2016 8:24 pm (PDT) . Posted by:
kitiany
That is what I implemented in VBA -- resubmitting a request if the latest date is old. I see some servers are still giving 9/1 as the most current date, but if you request again, more often than not, it will give you the latest. I wonder what they are doing in Yahoo. I have never seen this before in the last 8 years.
Fri Sep 9, 2016 11:54 am (PDT) . Posted by:
climbermel
Is there a way to use the tools here to screen for options with a hate rate of premium? I know this sounds vague, but I have just started on this idea and thought I'd check if there were already some example that could save me time.
Mel
Mel
Fri Sep 9, 2016 3:34 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
That's not really something the add-in would be a good tool for. You'd need
to find a source of the data, then use the add-in to extract the data from
there.
For example, Barchart.com has a high volatility list:
http://www.barchart.com/options/highvol
If you register and create an IE cookie for EXCEL, you can use the "export
data" option using the add-in. For example:
=smfGetCSVFile(B2)
...where cell B2 contains the URL (it's too long a string to put directly
into a formula):
http://www.barchart.com/option-center/getExcel.php?type=stocks&page=Highest%20Implied%20Volatility&dir=desc&sort=volatility&filter_conditions=a%3A3%3A%7Bi%3A0%3Ba%3A3%3A%7Bi%3A0%3Bs%3A10%3A%22volatility%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A1%3A%220%22%3B%7Di%3A1%3Ba%3A3%3A%7Bi%3A0%3Bs%3A6%3A%22volume%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A3%3A%22500%22%3B%7Di%3A2%3Ba%3A3%3A%7Bi%3A0%3Bs%3A13%3A%22open_interest%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A3%3A%22100%22%3B%7D%7D&f=base_symbol,type,strike,expiration_date,bid,ask,last,volume,open_interest,volatility,timestamp&fn=Symbol,Type,Strike,Expiration,Bid,Ask,Last,Volume,Open%20Interest,Volatility,Time
The first few lines it returns:
Symbol Type Strike Expiration Bid Ask Last Volume Open Interest Volatility
Time
SRPT Put 10 09/16/16 0.15 0.3 0.15 1271 19521 4.85 09/09/16
SRPT Put 8 09/16/16 0.05 0.1 0.05 518 14631 4.78 09/09/16
SRPT Put 9 09/16/16 0.1 0.15 0.15 561 2474 4.74 09/09/16
VHC Put 4 09/16/16 1.65 2 1.76 540 800 4.73 09/09/16
SRPT Put 12 09/16/16 0.25 0.35 0.25 509 14028 4.32 09/09/16
I'm sure there are other screeners and scanners online, but it's not an
area of interest to me so I'm not familiar with the online offerings.
On Fri, Sep 9, 2016 at 11:54 AM, climbermel@
...wrote:
>
> Is there a way to use the tools here to screen for options with a hate
> rate of premium? I know this sounds vague, but I have just started on this
> idea and thought I'd check if there were already some example that could
> save me time.
>
>
to find a source of the data, then use the add-in to extract the data from
there.
For example, Barchart.com has a high volatility list:
http://www.barchart.com/options/highvol
If you register and create an IE cookie for EXCEL, you can use the "export
data" option using the add-in. For example:
=smfGetCSVFile(B2)
...where cell B2 contains the URL (it's too long a string to put directly
into a formula):
http://www.barchart.com/option-center/getExcel.php?type=stocks&page=Highest%20Implied%20Volatility&dir=desc&sort=volatility&filter_conditions=a%3A3%3A%7Bi%3A0%3Ba%3A3%3A%7Bi%3A0%3Bs%3A10%3A%22volatility%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A1%3A%220%22%3B%7Di%3A1%3Ba%3A3%3A%7Bi%3A0%3Bs%3A6%3A%22volume%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A3%3A%22500%22%3B%7Di%3A2%3Ba%3A3%3A%7Bi%3A0%3Bs%3A13%3A%22open_interest%22%3Bi%3A1%3Bs%3A1%3A%22%3E%22%3Bi%3A2%3Bs%3A3%3A%22100%22%3B%7D%7D&f=base_symbol,type,strike,expiration_date,bid,ask,last,volume,open_interest,volatility,timestamp&fn=Symbol,Type,Strike,Expiration,Bid,Ask,Last,Volume,Open%20Interest,Volatility,Time
The first few lines it returns:
Symbol Type Strike Expiration Bid Ask Last Volume Open Interest Volatility
Time
SRPT Put 10 09/16/16 0.15 0.3 0.15 1271 19521 4.85 09/09/16
SRPT Put 8 09/16/16 0.05 0.1 0.05 518 14631 4.78 09/09/16
SRPT Put 9 09/16/16 0.1 0.15 0.15 561 2474 4.74 09/09/16
VHC Put 4 09/16/16 1.65 2 1.76 540 800 4.73 09/09/16
SRPT Put 12 09/16/16 0.25 0.35 0.25 509 14028 4.32 09/09/16
I'm sure there are other screeners and scanners online, but it's not an
area of interest to me so I'm not familiar with the online offerings.
On Fri, Sep 9, 2016 at 11:54 AM, climbermel@
...wrote:
>
> Is there a way to use the tools here to screen for options with a hate
> rate of premium? I know this sounds vague, but I have just started on this
> idea and thought I'd check if there were already some example that could
> save me time.
>
>
Fri Sep 9, 2016 12:06 pm (PDT) . Posted by:
samil_beret
Hi Randy
I am using RCHGetHTMLTable to download option prices from Yahoo in a VBA routine as below:
StockTicker is in cell B2.
=RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2," http://finance.yahoo.com/q/op?s=%22&B2,%22>Call
Options",-1,"",4)
When I do this, it pulls data from various options expiring at different dates. I would like to specify a specific expiration date and pull data only for that date - such as 10/21/2016.
How do I modify the statement below to accomplish that?
=RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2," http://finance.yahoo.com/q/op?s=%22&B2,%22>Call
Options",-1,"",4)
Thanks for your help.
S. Beret
I am using RCHGetHTMLTable to download option prices from Yahoo in a VBA routine as below:
StockTicker is in cell B2.
=RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2," http://finance.yahoo.com/q/op?s=%22&B2,%22>Call
Options",-1,"",4)
When I do this, it pulls data from various options expiring at different dates. I would like to specify a specific expiration date and pull data only for that date - such as 10/21/2016.
How do I modify the statement below to accomplish that?
=RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2," http://finance.yahoo.com/q/op?s=%22&B2,%22>Call
Options"
Thanks for your help.
S. Beret
Fri Sep 9, 2016 2:56 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You're using the old URLs. I'm not sure how much longer Yahoo will continue
to populate them. In fact, if you use:
http://finance.yahoo.com/q/op?s=MMM
...in your browser, it should be redirecting you here:
http://finance.yahoo.com/quote/MMM/options
Before the Yahoo web site update recently, you could only ask for option
web pages by month, which meant you could be looking at a mixture of
weekly, monthly, and quarterly expiration dates -- all of the expiration
dates in that month (a holdover from when weeklies didn't exist). However
the new option web pages do allow asking for specific expiration dates,
such as:
http://finance.yahoo.com/quote/MMM/options?date=1475798400
That number being used for a date is the # of days since UNIX was created,
calculated as:
=86400*(B3-date(1970,1,1))
...where B3 contains the expiration date as an EXCEL serial date.
So, with the new website, combining everything together:
=RCHGetHTMLTable("http://finance.yahoo.com/quote/
"&B7&"/options?date="&86400*(C7-DATE(1970,1,1)),
">Implied Volatility",-1,"",2)
...where B7 contains the ticker symbol and C7 contains the expiration date.
On Fri, Sep 9, 2016 at 12:06 PM, sberet@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I am using RCHGetHTMLTable to download option prices from Yahoo in a VBA
> routine as below:
>
> StockTicker is in cell B2.
>
> =RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2,">Call
> Options",-1,"",4)
>
> When I do this, it pulls data from various options expiring at different
> dates. I would like to specify a specific expiration date and pull data
> only for that date - such as 10/21/2016.
>
> How do I modify the statement below to accomplish that?
>
> =RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2,">Call
> Options",-1,"",4)
>
>
to populate them. In fact, if you use:
http://finance.yahoo.com/q/op?s=MMM
...in your browser, it should be redirecting you here:
http://finance.yahoo.com/quote/MMM/options
Before the Yahoo web site update recently, you could only ask for option
web pages by month, which meant you could be looking at a mixture of
weekly, monthly, and quarterly expiration dates -- all of the expiration
dates in that month (a holdover from when weeklies didn't exist). However
the new option web pages do allow asking for specific expiration dates,
such as:
http://finance.yahoo.com/quote/MMM/options?date=1475798400
That number being used for a date is the # of days since UNIX was created,
calculated as:
=86400*(B3-date(1970,1,1))
...where B3 contains the expiration date as an EXCEL serial date.
So, with the new website, combining everything together:
=RCHGetHTMLTable("http://finance.yahoo.com/quote/
"&B7&"/options?date="&86400*(C7-DATE(1970,1,1)),
">Implied Volatility",-1,"",2)
...where B7 contains the ticker symbol and C7 contains the expiration date.
On Fri, Sep 9, 2016 at 12:06 PM, sberet@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> I am using RCHGetHTMLTable to download option prices from Yahoo in a VBA
> routine as below:
>
> StockTicker is in cell B2.
>
> =RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2,">Call
> Options",-1,"",4)
>
> When I do this, it pulls data from various options expiring at different
> dates. I would like to specify a specific expiration date and pull data
> only for that date - such as 10/21/2016.
>
> How do I modify the statement below to accomplish that?
>
> =RCHGetHTMLTable("http://finance.yahoo.com/q/op?s="&B2,"
> Options"
>
>
Fri Sep 9, 2016 12:55 pm (PDT) . Posted by:
shhutchens6
Hi Randy,
I'm trying to get the standard deviations of the individual stocks within a portfolio. I haven't spent a lot of time working with the yahoo history side of the addin and didn't know if there was a simple formula that has been created to get the st. dev. of equities, either since inception or 1, 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks. is there a simpler way than manually calculating historical returns, thanks again.
-Scott
I'm trying to get the standard deviations of the individual stocks within a portfolio. I haven't spent a lot of time working with the yahoo history side of the addin and didn't know if there was a simple formula that has been created to get the st. dev. of equities, either since inception or 1, 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks. is there a simpler way than manually calculating historical returns, thanks again.
-Scott
Fri Sep 9, 2016 3:21 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't know of an online source for such data. You could try some Google
searches and see if you can find something. I didn't have much luck.
On Fri, Sep 9, 2016 at 12:55 PM, shhutchens6@
...wrote:
> I'm trying to get the standard deviations of the individual stocks within
> a portfolio. I haven't spent a lot of time working with the yahoo history
> side of the addin and didn't know if there was a simple formula that has
> been created to get the st. dev. of equities, either since inception or 1,
> 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks.
> is there a simpler way than manually calculating historical returns, thanks
> again.
>
>
searches and see if you can find something. I didn't have much luck.
On Fri, Sep 9, 2016 at 12:55 PM, shhutchens6@
...wrote:
> I'm trying to get the standard deviations of the individual stocks within
> a portfolio. I haven't spent a lot of time working with the yahoo history
> side of the addin and didn't know if there was a simple formula that has
> been created to get the st. dev. of equities, either since inception or 1,
> 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks.
> is there a simpler way than manually calculating historical returns, thanks
> again.
>
>
Fri Sep 9, 2016 6:06 pm (PDT) . Posted by:
climbermel
I'm curious, what are you wanting the Std Dev of? I gather since you mention portfolio, you want the standard deviation of the ARoR. It's not real hard to calculate, all you need is the data points that you want to base it on. I'm sure you could grab the needed data from Yahoo and then just apply the formula.
Mel
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
I don't know of an online source for such data. You could try some Google searches and see if you can find something. I didn't have much luck.
On Fri, Sep 9, 2016 at 12:55 PM, shhutchens6@ ...wrote:
I'm trying to get the standard deviations of the individual stocks within a portfolio. I haven't spent a lot of time working with the yahoo history side of the addin and didn't know if there was a simple formula that has been created to get the st. dev. of equities, either since inception or 1, 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks. is there a simpler way than manually calculating historical returns, thanks again.
Mel
---In smf_addin@yahoogroups.com, <rharmelink@
I don't know of an online source for such data. You could try some Google searches and see if you can find something. I didn't have much luck.
On Fri, Sep 9, 2016 at 12:55 PM, shhutchens6@ ...wrote:
I'm trying to get the standard deviations of the individual stocks within a portfolio. I haven't spent a lot of time working with the yahoo history side of the addin and didn't know if there was a simple formula that has been created to get the st. dev. of equities, either since inception or 1, 3, 5, 10, etc.. I noticed that yahoo has st. dev. for funds but not stocks. is there a simpler way than manually calculating historical returns, thanks again.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar