15 New Messages
Digest #2617
6b
Re: Anyone know the best way to capture index data, constituents, se by "Randy Harmelink" rharmelink
Messages
Tue May 14, 2013 2:02 am (PDT) . Posted by:
"ds5ec" ds5ec
Hello, I'm trying to use smfGetOptionQuotes to get the price of a PUT. The call works: =smfGetOptionQuotes("IWM 5/18 2013 $97.00 Call","b",,"OX") - gives 0.68 - but trying to get the Put price
=smfGetOptionQuotes("IWM 5/18 2013 $97.00 Put","b",,"OX") gives 'Invalid Put/Call indicator (must be a c): P'
I've used C and P in place of Call and Put but the result is the same.
I may not be using these alternatives but have tried:
=smfGetYahooOptionQuote("ORCL","P",DATE(2013,5,18),34,"b") - gives 'N/A'
=smfGetYahooOptionQuote("CSCO","C",DATE(2013,5,18),21,"b") - gives 'N/A'
=smfGetOptionQuotes("ORCL 5/18 2013 $105.00 Put", "b",1) gives 'Bid Price'
Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices is working.
Thanks for your help, David
=smfGetOptionQuotes
I've used C and P in place of Call and Put but the result is the same.
I may not be using these alternatives but have tried:
=smfGetYahooOptionQ
=smfGetYahooOptionQ
=smfGetOptionQuotes
Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices is working.
Thanks for your help, David
Tue May 14, 2013 2:19 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The "OX" data source only works for calls (as noted in the documentation).
It was implemented because OptionsXPress does have the Greeks available for
calls. If you don't need Greeks, just use the "OX2" data source.
The two Yahoo functions return "N/A" because that's why Yahoo has posted
right now.
Not sure about the "Bid Price" being returned, but since the strike price
is an invalid one, I'm not sure it matters much?
On Tue, May 14, 2013 at 2:02 AM, ds5ec <ds5ec@yahoo.com > wrote:
> Hello, I'm trying to use smfGetOptionQuotes to get the price of a PUT. The
> call works: =smfGetOptionQuotes("IWM 5/18 2013 $97.00 Call","b",,"OX") -
> gives 0.68 - but trying to get the Put price
> =smfGetOptionQuotes("IWM 5/18 2013 $97.00 Put","b",,"OX") gives 'Invalid
> Put/Call indicator (must be a c): P'
>
> I've used C and P in place of Call and Put but the result is the same.
>
> I may not be using these alternatives but have tried:
>
> =smfGetYahooOptionQuote("ORCL","P",DATE(2013,5,18),34,"b") - gives 'N/A'
> =smfGetYahooOptionQuote("CSCO","C",DATE(2013,5,18),21,"b") - gives 'N/A'
>
> =smfGetOptionQuotes("ORCL 5/18 2013 $105.00 Put", "b",1) gives 'Bid Price'
>
> Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices
> is working.
>
It was implemented because OptionsXPress does have the Greeks available for
calls. If you don't need Greeks, just use the "OX2" data source.
The two Yahoo functions return "N/A" because that's why Yahoo has posted
right now.
Not sure about the "Bid Price" being returned, but since the strike price
is an invalid one, I'm not sure it matters much?
On Tue, May 14, 2013 at 2:02 AM, ds5ec <ds5ec@yahoo.
> Hello, I'm trying to use smfGetOptionQuotes to get the price of a PUT. The
> call works: =smfGetOptionQuotes
> gives 0.68 - but trying to get the Put price
> =smfGetOptionQuotes
> Put/Call indicator (must be a c): P'
>
> I've used C and P in place of Call and Put but the result is the same.
>
> I may not be using these alternatives but have tried:
>
> =smfGetYahooOptionQ
> =smfGetYahooOptionQ
>
> =smfGetOptionQuotes
>
> Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices
> is working.
>
Tue May 14, 2013 4:42 am (PDT) . Posted by:
"ds5ec" ds5ec
Thanks - OX2 provides the Put price I was looking for. Apolgoies for not noticing that OX is only for calls.
I corrected the strike price and now get a 'N/A', which, as you say, is what Yahoo posted. Is that equivalent to Yahoo saying they are offline - just wondering the reason why they provide N/A.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> The "OX" data source only works for calls (as noted in the documentation).
> It was implemented because OptionsXPress does have the Greeks available for
> calls. If you don't need Greeks, just use the "OX2" data source.
>
> The two Yahoo functions return "N/A" because that's why Yahoo has posted
> right now.
>
> Not sure about the "Bid Price" being returned, but since the strike price
> is an invalid one, I'm not sure it matters much?
>
> On Tue, May 14, 2013 at 2:02 AM, ds5ec <ds5ec@...> wrote:
>
> > Hello, I'm trying to use smfGetOptionQuotes to get the price of a PUT. The
> > call works: =smfGetOptionQuotes("IWM 5/18 2013 $97.00 Call","b",,"OX") -
> > gives 0.68 - but trying to get the Put price
> > =smfGetOptionQuotes("IWM 5/18 2013 $97.00 Put","b",,"OX") gives 'Invalid
> > Put/Call indicator (must be a c): P'
> >
> > I've used C and P in place of Call and Put but the result is the same.
> >
> > I may not be using these alternatives but have tried:
> >
> > =smfGetYahooOptionQuote("ORCL","P",DATE(2013,5,18),34,"b") - gives 'N/A'
> > =smfGetYahooOptionQuote("CSCO","C",DATE(2013,5,18),21,"b") - gives 'N/A'
> >
> > =smfGetOptionQuotes("ORCL 5/18 2013 $105.00 Put", "b",1) gives 'Bid Price'
> >
> > Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices
> > is working.
> >
>
I corrected the strike price and now get a 'N/A'
--- In smf_addin@yahoogrou
>
> The "OX" data source only works for calls (as noted in the documentation)
> It was implemented because OptionsXPress does have the Greeks available for
> calls. If you don't need Greeks, just use the "OX2" data source.
>
> The two Yahoo functions return "N/A" because that's why Yahoo has posted
> right now.
>
> Not sure about the "Bid Price" being returned, but since the strike price
> is an invalid one, I'm not sure it matters much?
>
> On Tue, May 14, 2013 at 2:02 AM, ds5ec <ds5ec@..
>
> > Hello, I'm trying to use smfGetOptionQuotes to get the price of a PUT. The
> > call works: =smfGetOptionQuotes
> > gives 0.68 - but trying to get the Put price
> > =smfGetOptionQuotes
> > Put/Call indicator (must be a c): P'
> >
> > I've used C and P in place of Call and Put but the result is the same.
> >
> > I may not be using these alternatives but have tried:
> >
> > =smfGetYahooOptionQ
> > =smfGetYahooOptionQ
> >
> > =smfGetOptionQuotes
> >
> > Using smfGetOptionQuotes elsewhere in the spreadsheet to get call prices
> > is working.
> >
>
Tue May 14, 2013 8:09 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I think Yahoo only provides the bid/ask when the market is open...
I know I always see "N/A" at night.
On Tue, May 14, 2013 at 4:42 AM, ds5ec <ds5ec@yahoo.com > wrote:
> Thanks - OX2 provides the Put price I was looking for. Apolgoies for not
> noticing that OX is only for calls.
>
> I corrected the strike price and now get a 'N/A', which, as you say, is
> what Yahoo posted. Is that equivalent to Yahoo saying they are offline -
> just wondering the reason why they provide N/A.
>
I know I always see "N/A" at night.
On Tue, May 14, 2013 at 4:42 AM, ds5ec <ds5ec@yahoo.
> Thanks - OX2 provides the Put price I was looking for. Apolgoies for not
> noticing that OX is only for calls.
>
> I corrected the strike price and now get a 'N/A'
> what Yahoo posted. Is that equivalent to Yahoo saying they are offline -
> just wondering the reason why they provide N/A.
>
Tue May 14, 2013 2:08 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Well, when I try to use the file, there is nothing there. So it's probably
a file that gets generated at the time you click on a link, rather than one
that always exists...
What page are you getting the file reference from?
On Tue, May 14, 2013 at 1:57 AM, bagzigingahouse
<bagzigingahouse@gmail.com >wrote:
>
> i am trying to get the csv file from ivolatility.com and im not sure what
> im doing wrong. Could you maybe check why is this not working?
>
> =smfGetCSVFile("http://www.ivolatility.com/favourites.csv?fav_g=52182 ")
>
> I know ivol is kind of special case, so if this cant be done, is there a
> way to get the entire table from them some other way?
>
a file that gets generated at the time you click on a link, rather than one
that always exists...
What page are you getting the file reference from?
On Tue, May 14, 2013 at 1:57 AM, bagzigingahouse
<bagzigingahouse@
>
> i am trying to get the csv file from ivolatility.
> im doing wrong. Could you maybe check why is this not working?
>
> =smfGetCSVFile(
>
> I know ivol is kind of special case, so if this cant be done, is there a
> way to get the entire table from them some other way?
>
Tue May 14, 2013 5:19 am (PDT) . Posted by:
"bagzigingahouse" bagzigingahouse
Same here, but when i click the download link it downloads the file.
Here is the page, it requires login tho...
http://www.ivolatility.com/view_favourites.j?smid=.2.6.2
Thanks
Igor
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Well, when I try to use the file, there is nothing there. So it's probably
> a file that gets generated at the time you click on a link, rather than one
> that always exists...
>
> What page are you getting the file reference from?
>
> On Tue, May 14, 2013 at 1:57 AM, bagzigingahouse
> <bagzigingahouse@...>wrote:
>
> >
> > i am trying to get the csv file from ivolatility.com and im not sure what
> > im doing wrong. Could you maybe check why is this not working?
> >
> > =smfGetCSVFile("http://www.ivolatility.com/favourites.csv?fav_g=52182 ")
> >
> > I know ivol is kind of special case, so if this cant be done, is there a
> > way to get the entire table from them some other way?
> >
>
Here is the page, it requires login tho...
http://www.ivolatil
Thanks
Igor
--- In smf_addin@yahoogrou
>
> Well, when I try to use the file, there is nothing there. So it's probably
> a file that gets generated at the time you click on a link, rather than one
> that always exists...
>
> What page are you getting the file reference from?
>
> On Tue, May 14, 2013 at 1:57 AM, bagzigingahouse
> <bagzigingahouse
>
> >
> > i am trying to get the csv file from ivolatility.
> > im doing wrong. Could you maybe check why is this not working?
> >
> > =smfGetCSVFile(
> >
> > I know ivol is kind of special case, so if this cant be done, is there a
> > way to get the entire table from them some other way?
> >
>
Tue May 14, 2013 8:18 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Then it must generate the file at the time you click the download link. If
so, the add-in can't get it, because it doesn't interact with web pages. It
just extracts data from the retrieved source code of a web page.
On Tue, May 14, 2013 at 5:18 AM, bagzigingahouse
<bagzigingahouse@gmail.com >wrote:
> Same here, but when i click the download link it downloads the file.
>
> Here is the page, it requires login tho...
>
> http://www.ivolatility.com/view_favourites.j?smid=.2.6.2
>
>
so, the add-in can't get it, because it doesn't interact with web pages. It
just extracts data from the retrieved source code of a web page.
On Tue, May 14, 2013 at 5:18 AM, bagzigingahouse
<bagzigingahouse@
> Same here, but when i click the download link it downloads the file.
>
> Here is the page, it requires login tho...
>
> http://www.ivolatil
>
>
Tue May 14, 2013 5:16 am (PDT) . Posted by:
"dan-carroll" dan-carroll
Randy,
I hate to go over this (since you've done it twice here) but I'm traveling in the UK and have just come across this issue.
I created the smf-AdvFN-Prefix.txt file that you suggest, with "uk" as the only entry, restarted Excel, but it didn't work - just got "Error".
I updated to the very latest version of the Add-in (the works-in-progress folder) and restarted - still no luck
Tried SMFForceRecalculation macro - nothing
Everything was fine when I was in the US yesterday.
Is there anything else you could suggest I try?
I am using Windows 7 with Office 2007
And of course - thank you for all of your work, and for a most amazingly useful tool!
--- In smf_addin@yahoogroups.com , "acetylator" <gorbunov@...> wrote:
>
> Dear Randy,
>
> thank you for your reply. Your solution WORKS!
> Notice for newbies (like me :-) - after applying this solution,
> you MUST completely close all Excel files and reopen them again.
>
> Thank you for your help!
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > Try using "de" in this workaround I added on a recent release of the add-in:
> >
> > http://finance.groups.yahoo.com/group/smf_addin/message/21069
> >
> > On Sat, May 11, 2013 at 9:40 AM, acetylator <gorbunov@> wrote:
> >
> > >
> > > I am trying to get item 5396 (Annual Income Statement -- EBITDA -- FY1)
> > > from AdvFN and I keep getting error. in fact, I am unable to get any
> > > numbers from AdvFN at all.
> > > For example, "=RCHGetElementNumber("MO",5396)" just returns "Error" in the
> > > Excel cell.
> > > I use Excel 2010, latest plugin version (dloaded 2 days ago) and no
> > > firewall or other issues. I am from Germany. I am able to connect to
> > > advfn.com, however, I am always redirected to "de.advfn.com" and I am
> > > unable to browse on "www.adwfn.com" - I mean, it redirects me
> > > automatically. Could it be the source of the problem? What can I do about
> > > it? Is there a possibility to come with an universal solution for non-US
> > > users?
> > >
> >
>
I hate to go over this (since you've done it twice here) but I'm traveling in the UK and have just come across this issue.
I created the smf-AdvFN-Prefix.
I updated to the very latest version of the Add-in (the works-in-progress folder) and restarted - still no luck
Tried SMFForceRecalculati
Everything was fine when I was in the US yesterday.
Is there anything else you could suggest I try?
I am using Windows 7 with Office 2007
And of course - thank you for all of your work, and for a most amazingly useful tool!
--- In smf_addin@yahoogrou
>
> Dear Randy,
>
> thank you for your reply. Your solution WORKS!
> Notice for newbies (like me :-) - after applying this solution,
> you MUST completely close all Excel files and reopen them again.
>
> Thank you for your help!
>
>
> --- In smf_addin@yahoogrou
> >
> > Try using "de" in this workaround I added on a recent release of the add-in:
> >
> > http://finance.
> >
> > On Sat, May 11, 2013 at 9:40 AM, acetylator <gorbunov@
> >
> > >
> > > I am trying to get item 5396 (Annual Income Statement -- EBITDA -- FY1)
> > > from AdvFN and I keep getting error. in fact, I am unable to get any
> > > numbers from AdvFN at all.
> > > For example, "=RCHGetElemen
> > > Excel cell.
> > > I use Excel 2010, latest plugin version (dloaded 2 days ago) and no
> > > firewall or other issues. I am from Germany. I am able to connect to
> > > advfn.com, however, I am always redirected to "de.advfn.
> > > unable to browse on "www.adwfn.
> > > automatically. Could it be the source of the problem? What can I do about
> > > it? Is there a possibility to come with an universal solution for non-US
> > > users?
> > >
> >
>
Tue May 14, 2013 8:15 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Are you using the same computer? If not, you may need to apply the fix from
the LINKS area of the group.
Have you verified you get redirected?
On Tue, May 14, 2013 at 5:16 AM, dan-carroll <dan-carroll@yahoo.com > wrote:
>
> I hate to go over this (since you've done it twice here) but I'm traveling
> in the UK and have just come across this issue.
>
> I created the smf-AdvFN-Prefix.txt file that you suggest, with "uk" as the
> only entry, restarted Excel, but it didn't work - just got "Error".
> I updated to the very latest version of the Add-in (the works-in-progress
> folder) and restarted - still no luck
> Tried SMFForceRecalculation macro - nothing
>
> Everything was fine when I was in the US yesterday.
>
> Is there anything else you could suggest I try?
>
> I am using Windows 7 with Office 2007
>
> And of course - thank you for all of your work, and for a most amazingly
> useful tool!
>
the LINKS area of the group.
Have you verified you get redirected?
On Tue, May 14, 2013 at 5:16 AM, dan-carroll <dan-carroll@
>
> I hate to go over this (since you've done it twice here) but I'm traveling
> in the UK and have just come across this issue.
>
> I created the smf-AdvFN-Prefix.
> only entry, restarted Excel, but it didn't work - just got "Error"
> I updated to the very latest version of the Add-in (the works-in-progress
> folder) and restarted - still no luck
> Tried SMFForceRecalculati
>
> Everything was fine when I was in the US yesterday.
>
> Is there anything else you could suggest I try?
>
> I am using Windows 7 with Office 2007
>
> And of course - thank you for all of your work, and for a most amazingly
> useful tool!
>
Tue May 14, 2013 5:54 am (PDT) . Posted by:
"jeff.wortman" jeff.wortman
This tool is great! Many thanks! I've been using the tool for 3 + years and I am getting ready to upgrade my PC. I want to share spreadsheets with a friend that is using Excel 2010 on Windows 7. Are there any differences that I will have to deal with if I go with Excel 2013 or am I better off installing Excel 2010? Will there be any issues sharing files between a 2013 and a 2010 version of Excel.
Thanks
Jeff
Thanks
Jeff
Tue May 14, 2013 8:19 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I can't answer that myself, since I only have EXCEL 2007 on Vista...
Offhand, I would hope the most likely issue would be the location of the
add-in.
On Tue, May 14, 2013 at 5:54 AM, jeff.wortman <jeff.wortman@yahoo.com >wrote:
> This tool is great! Many thanks! I've been using the tool for 3 + years
> and I am getting ready to upgrade my PC. I want to share spreadsheets with
> a friend that is using Excel 2010 on Windows 7. Are there any differences
> that I will have to deal with if I go with Excel 2013 or am I better off
> installing Excel 2010? Will there be any issues sharing files between a
> 2013 and a 2010 version of Excel.
>
Offhand, I would hope the most likely issue would be the location of the
add-in.
On Tue, May 14, 2013 at 5:54 AM, jeff.wortman <jeff.wortman@
> This tool is great! Many thanks! I've been using the tool for 3 + years
> and I am getting ready to upgrade my PC. I want to share spreadsheets with
> a friend that is using Excel 2010 on Windows 7. Are there any differences
> that I will have to deal with if I go with Excel 2013 or am I better off
> installing Excel 2010? Will there be any issues sharing files between a
> 2013 and a 2010 version of Excel.
>
Tue May 14, 2013 6:45 am (PDT) . Posted by:
"njouellette83" njouellette83
I used to run these formulas however now they only show up as "undefined&quo
=RCHGetElementNumbe
=RCHGetElementNumbe
=RCHGetElementNumbe
Tue May 14, 2013 8:21 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Typically, "undefined" means you don't have the element definition files in
the same folder as the add-in file. Those are the smf-elements-*.txt files.
BTW, your formulas below are invalid, as KO is a cell reference and not a
ticker symbol literal.
On Tue, May 14, 2013 at 6:45 AM, njouellette83 <nouellette83@gmail.com >wrote:
>
> I used to run these formulas however now they only show up as "undefined"
> any assistance. It is for the BVPS, and the DPS from years 1 through 6. I
> didn't add all the elements but they are all not functioning.
>
> =RCHGetElementNumber(KO,5901)
> =RCHGetElementNumber(KO,969)
> =RCHGetElementNumber(KO,7691)
>
the same folder as the add-in file. Those are the smf-elements-
BTW, your formulas below are invalid, as KO is a cell reference and not a
ticker symbol literal.
On Tue, May 14, 2013 at 6:45 AM, njouellette83 <nouellette83@
>
> I used to run these formulas however now they only show up as "undefined&quo
> any assistance. It is for the BVPS, and the DPS from years 1 through 6. I
> didn't add all the elements but they are all not functioning.
>
> =RCHGetElementNumbe
> =RCHGetElementNumbe
> =RCHGetElementNumbe
>
Tue May 14, 2013 8:20 am (PDT) . Posted by:
"b.kopacz" b.kopacz
I am trying to build a spreadsheet that would highlight the constituents of an index (S&P 500, for example) that would also show % weighting or MV, and the respective sector.
I know that index data is hard to come by so I thought I would try this route.
BK
I know that index data is hard to come by so I thought I would try this route.
BK
Tue May 14, 2013 8:25 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The easiest way I've found is to just grab the holdings CSV file from
Morningstar for the ETFs based on the indexes. For example:
=smfGetCSVFile("
http://portfolios.morningstar.com/fund/holdingsExport?exportType=details&t=SPY
")
That file does have weighting and sector on it.
On Tue, May 14, 2013 at 8:20 AM, b.kopacz <bkopacz@gmail.com > wrote:
> I am trying to build a spreadsheet that would highlight the constituents
> of an index (S&P 500, for example) that would also show % weighting or MV,
> and the respective sector.
>
> I know that index data is hard to come by so I thought I would try this
> route.
>
Morningstar for the ETFs based on the indexes. For example:
=smfGetCSVFile(
http://portfolios.
")
That file does have weighting and sector on it.
On Tue, May 14, 2013 at 8:20 AM, b.kopacz <bkopacz@gmail.
> I am trying to build a spreadsheet that would highlight the constituents
> of an index (S&P 500, for example) that would also show % weighting or MV,
> and the respective sector.
>
> I know that index data is hard to come by so I thought I would try this
> route.
>
Tidak ada komentar:
Posting Komentar