15 New Messages
Digest #2607
Messages
Tue May 7, 2013 2:42 am (PDT) . Posted by:
"jcarlosd" jcarlosd
Your solution is to extract columns 10, 16 and 17, moved 9 columns to the right because of the missing </TR>.
I'll do that, but I was trying to find a way to construct the table even if there is a closing tag missing. If the owner of that web page fixes the problem and puts the </TR> back, I will have to change again my code using columns 1, 7 and 8 :-(
Well, no problem. I'll notice that when happen.
Thanks for this great tool and for your dedication to support the forum.
J. Carlos
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> One workaround I can offer is to get the data by column:
>
> =INDEX(RCHGetHTMLTable("
> http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
> ","",1,"",1,,4000,30),,10)
> =INDEX(RCHGetHTMLTable("
> http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
> ","",1,"",1,,4000,30),,16)
> =INDEX(RCHGetHTMLTable("
> http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
> ","",1,"",1,,4000,30),,17)
>
> Otherwise, I think you would need to get it cell by cell.
>
> Or extract it from your full array that is returned.
>
> On Mon, May 6, 2013 at 4:29 AM, jcarlosd <jcdelrio@...> wrote:
>
> > I have a similar problem to this previous post:
> > http://finance.groups.yahoo.com/group/smf_addin/message/12251 that was
> > not resolved.
> >
> > The table I am trying to read is missing a </TR> in the first row, so all
> > data is moved 8 columns to the right.
> >
> > Is there any way to avoid that displacement?
> >
> > URL is http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm
> >
> > VBA code I am using is:
> > strURL = "http://www.bolsasymercados.es/mab/esp/mercado/accfix_c.htm "
> > strArray = RCHGetHTMLTable(strURL, ">Nombre", -1, "", 1, , 4000, 9)
> >
> > (can be easily adapted to excel cell formulas)
> >
>
I'll do that, but I was trying to find a way to construct the table even if there is a closing tag missing. If the owner of that web page fixes the problem and puts the </TR> back, I will have to change again my code using columns 1, 7 and 8 :-(
Well, no problem. I'll notice that when happen.
Thanks for this great tool and for your dedication to support the forum.
J. Carlos
--- In smf_addin@yahoogrou
>
> One workaround I can offer is to get the data by column:
>
> =INDEX(RCHGetHTMLTa
> http://www.bolsasym
> ","
> =INDEX(RCHGetHTMLTa
> http://www.bolsasym
> ","
> =INDEX(RCHGetHTMLTa
> http://www.bolsasym
> ","
>
> Otherwise, I think you would need to get it cell by cell.
>
> Or extract it from your full array that is returned.
>
> On Mon, May 6, 2013 at 4:29 AM, jcarlosd <jcdelrio@
>
> > I have a similar problem to this previous post:
> > http://finance.
> > not resolved.
> >
> > The table I am trying to read is missing a </TR> in the first row, so all
> > data is moved 8 columns to the right.
> >
> > Is there any way to avoid that displacement?
> >
> > URL is http://www.bolsasym
> >
> > VBA code I am using is:
> > strURL = "http://www.bolsasym
> > strArray = RCHGetHTMLTable(
> >
> > (can be easily adapted to excel cell formulas)
> >
>
Tue May 7, 2013 3:04 am (PDT) . Posted by:
"mjk6288" mjk6288
Hi,
I'm trying to pull fx futures options data from the CME site (eg. http://www.cmegroup.com/trading/fx/g10/euro-fx_quotes_globex_options.html?exchange=XCME&foi=OPT&venue=G&productCd=6EM3&underlyingContract=6E&floorContractCd=ECM3&expMonth=201306&prodid=#prodType=EUR ).
I've tried using the RCHGetHTMLTable() and RCHGetWebData() but I can't seem to get it to work.
Does anyone have any suggestions on how this might be done?
Thanks!
I'm trying to pull fx futures options data from the CME site (eg. http://www.cmegroup
I've tried using the RCHGetHTMLTable(
Does anyone have any suggestions on how this might be done?
Thanks!
Tue May 7, 2013 3:12 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
As soon as I went to the page, I saw "Loading" for the area you want to
extract data from. That's a pretty straight-forward sign that the web page
is being generated. Unfortunately, that means the data can't be retrieved
by the add-in, since the add-in pulls data from the originally delivered
source code of the web page.
On Tue, May 7, 2013 at 2:02 AM, mjk6288 <matthew@majamke.com > wrote:
>
> I'm trying to pull fx futures options data from the CME site (eg.
> http://www.cmegroup.com/trading/fx/g10/euro-fx_quotes_globex_options.html?exchange=XCME&foi=OPT&venue=G&productCd=6EM3&underlyingContract=6E&floorContractCd=ECM3&expMonth=201306&prodid=#prodType=EUR
> ).
>
> I've tried using the RCHGetHTMLTable() and RCHGetWebData() but I can't
> seem to get it to work.
>
> Does anyone have any suggestions on how this might be done?
>
extract data from. That's a pretty straight-forward sign that the web page
is being generated. Unfortunately, that means the data can't be retrieved
by the add-in, since the add-in pulls data from the originally delivered
source code of the web page.
On Tue, May 7, 2013 at 2:02 AM, mjk6288 <matthew@majamke.
>
> I'm trying to pull fx futures options data from the CME site (eg.
> http://www.cmegroup
> ).
>
> I've tried using the RCHGetHTMLTable(
> seem to get it to work.
>
> Does anyone have any suggestions on how this might be done?
>
Tue May 7, 2013 6:06 am (PDT) . Posted by:
"antonvanas1989" antonvanas1989
Posted By: rharmelink rharmelink Send Email
Fri May 3, 2013 11:40 pm |
Yahoo adjusts both for dividends and for splits. I would think you'd want prices adjusted only by splits for your calculations. But that's just an opinion.
You could do your own adjustments, only for splits. See:
http://finance.groups.yahoo.com/group/smf_addin/message/14400
This was the answer on the other topic i had, sorry i had posted this question by mail and online, my bad.
It is true that my sheet can only get 2012 information if it is set to public by advfn. But i use those data to make a graphic, and use those data also for showing the past preformance. So lets say you want toknow the average P/E ratio of Coca Cola of the last 5-10 year. i got them. But if the prices from yahoo! get diluted year by year, there is no real use for those calculations, because they not represent the "orginal" data.
My formula's used:
Low: =IFERROR(smfPricesBetween(YHTicker;L3;L4;"06");"--")
High: =IFERROR(smfPricesBetween(YHTicker;L3;L4;"04");"--")
Close: =IFERROR(smfPricesByDates(YHTicker;L4);"0,00")
Adj. Close: =IFERROR(RCHGetYahooHistory(YHTicker;YEAR(L3);MONTH(L3);DAY(L3);YEAR(L4);MONTH(L4);DAY(L4);;"C";0);"--")
Where L3 = 01-01-2012 and L4 = 12-31-2012
So i need the "real" period low/high for the P/E(low/high)
Those period prices are for past preformance calculations. So you get a view of the past from the company.
Fri May 3, 2013 11:40 pm |
Yahoo adjusts both for dividends and for splits. I would think you'd want prices adjusted only by splits for your calculations. But that's just an opinion.
You could do your own adjustments, only for splits. See:
http://finance.
This was the answer on the other topic i had, sorry i had posted this question by mail and online, my bad.
It is true that my sheet can only get 2012 information if it is set to public by advfn. But i use those data to make a graphic, and use those data also for showing the past preformance. So lets say you want toknow the average P/E ratio of Coca Cola of the last 5-10 year. i got them. But if the prices from yahoo! get diluted year by year, there is no real use for those calculations, because they not represent the "orginal" data.
My formula'
Low: =IFERROR(smfPricesB
High: =IFERROR(smfPricesB
Close: =IFERROR(smfPricesB
Adj. Close: =IFERROR(RCHGetYaho
Where L3 = 01-01-2012 and L4 = 12-31-2012
So i need the "real" period low/high for the P/E(low/high)
Those period prices are for past preformance calculations. So you get a view of the past from the company.
Tue May 7, 2013 6:46 am (PDT) . Posted by:
"tora_toba" tora_toba
I didn't realize that about it looking through the 20 smf-elements files... thanks! I've been maintaining it with every new release. I'll have to fix that.
I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet that uses the RCHGetElementNumber function so I can swap out element numbers quickly using the same formula (I pull from different sources depending on how fast they update their data). So do you have a suggestion on the entry I could use for element.txt file so that RCHGetElementNumber works in the same manner?
Thanks!
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> First off, I wouldn't suggest putting your own element definitions into the
> smf-elements-2.txt file. If I make changes to element definitions in that
> file, you won't be able to replace the file with my updated file without
> wiping out all of your definitions. The add-in looks for files
> smf-elements-1.txt through smf-elements-20.txt when loading element
> definitions, so you should just pick a number that isn't already being used
> for your definitions. Then they won't be wiped out or lost if you apply any
> updates for the add-in. And -- be sure to keep a backup. :)
>
> As for the other issue, these work fine for me:
>
> =RCHGetTableCell("http://finance.yahoo.com/q/bs?s=aapl&ql=1 ",1,"Total
> Current Assets")
> =RCHGetTableCell("http://finance.yahoo.com/q/bs?s=aapl&ql=1 ",1,"Total
> Assets")
>
> On Mon, May 6, 2013 at 2:37 PM, CT Phan <ctphan02@...> wrote:
>
> > I'm trying to add element numbers for Yahoo quarterly balance sheet info
> > (e.g. http://finance.yahoo.com/q/bs?s=aapl&ql=1 ) by editing the
> > smf-elements-2.txt file. Most of the elements that I've added work fine
> > except I'm having a problem with the data elements that are in bold, such
> > as "Total Current Assets" or "Total Assets". I suspect it has to do with
> > the <strong> tags for those lines in the page. I've looked through your
> > code for a few minutes and couldn't think of how to pass in parameters via
> > the text file to accommodate for this, so I figured asking you would be
> > easier than trying to edit your code.
> >
>
I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet that uses the RCHGetElementNumber function so I can swap out element numbers quickly using the same formula (I pull from different sources depending on how fast they update their data). So do you have a suggestion on the entry I could use for element.txt file so that RCHGetElementNumber works in the same manner?
Thanks!
--- In smf_addin@yahoogrou
>
> First off, I wouldn't suggest putting your own element definitions into the
> smf-elements-
> file, you won't be able to replace the file with my updated file without
> wiping out all of your definitions. The add-in looks for files
> smf-elements-
> definitions, so you should just pick a number that isn't already being used
> for your definitions. Then they won't be wiped out or lost if you apply any
> updates for the add-in. And -- be sure to keep a backup. :)
>
> As for the other issue, these work fine for me:
>
> =RCHGetTableCell(
> Current Assets"
> =RCHGetTableCell(
> Assets"
>
> On Mon, May 6, 2013 at 2:37 PM, CT Phan <ctphan02@
>
> > I'm trying to add element numbers for Yahoo quarterly balance sheet info
> > (e.g. http://finance.
> > smf-elements-
> > except I'm having a problem with the data elements that are in bold, such
> > as "Total Current Assets" or "Total Assets"
> > the <strong> tags for those lines in the page. I've looked through your
> > code for a few minutes and couldn't think of how to pass in parameters via
> > the text file to accommodate for this, so I figured asking you would be
> > easier than trying to edit your code.
> >
>
Tue May 7, 2013 8:03 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You should just be able to do something like this:
17006;Yahoo;Market Capitalization;=RCHGetTableCell("
http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">Market Cap")
Instead of swapping out the element number, why not just offset them by
something like 500. Then you can have a cell that either contains a 0 or
500, and your formulas would be like this is the workbook:
=RCHGetElementNumber($A13,$B2+$A$1)
...where $A13 contains the ticker symbol, cell $B2 contains the "base"
element number, and cell $A$1 either contains a 0 or 500. So $B2+500 would
get you the alternative element number.
On Tue, May 7, 2013 at 6:46 AM, tora_toba <ctphan02@hotmail.com > wrote:
> I didn't realize that about it looking through the 20 smf-elements
> files... thanks! I've been maintaining it with every new release. I'll
> have to fix that.
>
> I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet
> that uses the RCHGetElementNumber function so I can swap out element
> numbers quickly using the same formula (I pull from different sources
> depending on how fast they update their data). So do you have a suggestion
> on the entry I could use for element.txt file so that RCHGetElementNumber
> works in the same manner?
>
17006;Yahoo;
http://finance.
Instead of swapping out the element number, why not just offset them by
something like 500. Then you can have a cell that either contains a 0 or
500, and your formulas would be like this is the workbook:
=RCHGetElementNumbe
...where $A13 contains the ticker symbol, cell $B2 contains the "base"
element number, and cell $A$1 either contains a 0 or 500. So $B2+500 would
get you the alternative element number.
On Tue, May 7, 2013 at 6:46 AM, tora_toba <ctphan02@hotmail.
> I didn't realize that about it looking through the 20 smf-elements
> files... thanks! I've been maintaining it with every new release. I'll
> have to fix that.
>
> I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet
> that uses the RCHGetElementNumber function so I can swap out element
> numbers quickly using the same formula (I pull from different sources
> depending on how fast they update their data). So do you have a suggestion
> on the entry I could use for element.txt file so that RCHGetElementNumber
> works in the same manner?
>
Tue May 7, 2013 9:34 am (PDT) . Posted by:
"tora_toba" tora_toba
I tried putting:
1648;YahooQBS;Quarterly Balance Sheet -- Total Assets -- FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">TOTAL ASSETS")
in the element text file but am still getting an Error returned. Do I need to put spaces or 0's ("; ; ;0;0...") for the other parameters of the function?
When I say swapping out the element number, I am referencing another cell with the element number that changes, similar to how you are suggesting.
Thanks.
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> You should just be able to do something like this:
>
> 17006;Yahoo;Market Capitalization;=RCHGetTableCell("
> http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">Market Cap")
>
> Instead of swapping out the element number, why not just offset them by
> something like 500. Then you can have a cell that either contains a 0 or
> 500, and your formulas would be like this is the workbook:
>
> =RCHGetElementNumber($A13,$B2+$A$1)
>
> ...where $A13 contains the ticker symbol, cell $B2 contains the "base"
> element number, and cell $A$1 either contains a 0 or 500. So $B2+500 would
> get you the alternative element number.
>
> On Tue, May 7, 2013 at 6:46 AM, tora_toba <ctphan02@...> wrote:
>
> > I didn't realize that about it looking through the 20 smf-elements
> > files... thanks! I've been maintaining it with every new release. I'll
> > have to fix that.
> >
> > I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet
> > that uses the RCHGetElementNumber function so I can swap out element
> > numbers quickly using the same formula (I pull from different sources
> > depending on how fast they update their data). So do you have a suggestion
> > on the entry I could use for element.txt file so that RCHGetElementNumber
> > works in the same manner?
> >
>
1648;YahooQBS;
in the element text file but am still getting an Error returned. Do I need to put spaces or 0's ("; ; ;0;0..."
When I say swapping out the element number, I am referencing another cell with the element number that changes, similar to how you are suggesting.
Thanks.
--- In smf_addin@yahoogrou
>
> You should just be able to do something like this:
>
> 17006;Yahoo;
> http://finance.
>
> Instead of swapping out the element number, why not just offset them by
> something like 500. Then you can have a cell that either contains a 0 or
> 500, and your formulas would be like this is the workbook:
>
> =RCHGetElementNumbe
>
> ...where $A13 contains the ticker symbol, cell $B2 contains the "base"
> element number, and cell $A$1 either contains a 0 or 500. So $B2+500 would
> get you the alternative element number.
>
> On Tue, May 7, 2013 at 6:46 AM, tora_toba <ctphan02@
>
> > I didn't realize that about it looking through the 20 smf-elements
> > files... thanks! I've been maintaining it with every new release. I'll
> > have to fix that.
> >
> > I do see that the RCHGetTableCell works, but I'm using it in a spreadsheet
> > that uses the RCHGetElementNumber function so I can swap out element
> > numbers quickly using the same formula (I pull from different sources
> > depending on how fast they update their data). So do you have a suggestion
> > on the entry I could use for element.txt file so that RCHGetElementNumber
> > works in the same manner?
> >
>
Tue May 7, 2013 1:25 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Did you run the smfForceRecalculation macro after changing the definition?
The definitions are otherwise only loaded once, the first time an element
number is referenced. Running the macro will force the add-in to reload all
the element definitions. In fact, that was the initial reason I wrote the
macro, so that I could test changes in element definitions.
When you use the "=" sign as the start of that last parameter, no further
parameters are needed. They would be irrelevant anyway.
On Tue, May 7, 2013 at 9:34 AM, tora_toba <ctphan02@hotmail.com > wrote:
> I tried putting:
> 1648;YahooQBS;Quarterly Balance Sheet -- Total Assets --
> FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">TOTAL
> ASSETS")
>
> in the element text file but am still getting an Error returned. Do I
> need to put spaces or 0's ("; ; ;0;0...") for the other parameters of the
> function?
>
> When I say swapping out the element number, I am referencing another cell
> with the element number that changes, similar to how you are suggesting.
>
The definitions are otherwise only loaded once, the first time an element
number is referenced. Running the macro will force the add-in to reload all
the element definitions. In fact, that was the initial reason I wrote the
macro, so that I could test changes in element definitions.
When you use the "=" sign as the start of that last parameter, no further
parameters are needed. They would be irrelevant anyway.
On Tue, May 7, 2013 at 9:34 AM, tora_toba <ctphan02@hotmail.
> I tried putting:
> 1648;YahooQBS;
> FQ1;=RCHGetTableCel
> ASSETS"
>
> in the element text file but am still getting an Error returned. Do I
> need to put spaces or 0's ("; ; ;0;0..."
> function?
>
> When I say swapping out the element number, I am referencing another cell
> with the element number that changes, similar to how you are suggesting.
>
Tue May 7, 2013 9:15 pm (PDT) . Posted by:
"tora_toba" tora_toba
Yes, I did several times. I also restarted excel as well in case it needed it to recognize the changes in the element text file. I'm thinking it's not adding the "&ql=1" after the ticker in the URL when replacing the "~~~~~" in the string. At least when I looked through your code, I couldn't see how it would add it offhand.
Ok... now that I've said that, I just got it to work by changing the line in the element file to this:
1648;YahooQBS;Quarterly Balance Sheet -- Total Assets -- FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~ "&"&ql=1",1,"TOTAL ASSETS")
I also took out the "1" after the "=" and it still seemed to work, so I assume the "1" passed in after the comma does that?
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Did you run the smfForceRecalculation macro after changing the definition?
> The definitions are otherwise only loaded once, the first time an element
> number is referenced. Running the macro will force the add-in to reload all
> the element definitions. In fact, that was the initial reason I wrote the
> macro, so that I could test changes in element definitions.
>
> When you use the "=" sign as the start of that last parameter, no further
> parameters are needed. They would be irrelevant anyway.
>
> On Tue, May 7, 2013 at 9:34 AM, tora_toba <ctphan02@...> wrote:
>
> > I tried putting:
> > 1648;YahooQBS;Quarterly Balance Sheet -- Total Assets --
> > FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">TOTAL
> > ASSETS")
> >
> > in the element text file but am still getting an Error returned. Do I
> > need to put spaces or 0's ("; ; ;0;0...") for the other parameters of the
> > function?
> >
> > When I say swapping out the element number, I am referencing another cell
> > with the element number that changes, similar to how you are suggesting.
> >
>
Ok... now that I've said that, I just got it to work by changing the line in the element file to this:
1648;YahooQBS;
I also took out the "1" after the "=" and it still seemed to work, so I assume the "1" passed in after the comma does that?
--- In smf_addin@yahoogrou
>
> Did you run the smfForceRecalculati
> The definitions are otherwise only loaded once, the first time an element
> number is referenced. Running the macro will force the add-in to reload all
> the element definitions. In fact, that was the initial reason I wrote the
> macro, so that I could test changes in element definitions.
>
> When you use the "=" sign as the start of that last parameter, no further
> parameters are needed. They would be irrelevant anyway.
>
> On Tue, May 7, 2013 at 9:34 AM, tora_toba <ctphan02@
>
> > I tried putting:
> > 1648;YahooQBS;
> > FQ1;=RCHGetTableCel
> > ASSETS"
> >
> > in the element text file but am still getting an Error returned. Do I
> > need to put spaces or 0's ("; ; ;0;0..."
> > function?
> >
> > When I say swapping out the element number, I am referencing another cell
> > with the element number that changes, similar to how you are suggesting.
> >
>
Tue May 7, 2013 10:38 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The "&ql=1" is irrelevant. What mattered is that you changed the base URL.
Original:
1648;YahooQBS;Quarterly Balance Sheet -- Total Assets --
FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~ ",1,">TOTAL
ASSETS")
Note it is getting the "Key Statistics" page (i.e. "ks"). Your version
below has "bs" for the balance sheet. That's why you're no longer getting
an error.
If you want to test a formula in your workbook, you could do:
=smfEval(SUBSTITUTE(C4,"~~~~~",C3))
...where C4 contains a ticker symbol and C3 contains your element
definition formula without the "=", such as:
RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~ ",1,"TOTAL ASSETS")
On Tue, May 7, 2013 at 9:15 PM, tora_toba <ctphan02@hotmail.com > wrote:
> Yes, I did several times. I also restarted excel as well in case it
> needed it to recognize the changes in the element text file. I'm thinking
> it's not adding the "&ql=1" after the ticker in the URL when replacing the
> "~~~~~" in the string. At least when I looked through your code, I
> couldn't see how it would add it offhand.
>
> Ok... now that I've said that, I just got it to work by changing the line
> in the element file to this:
>
> 1648;YahooQBS;Quarterly Balance Sheet -- Total Assets --
> FQ1;=RCHGetTableCell("http://finance.yahoo.com/q/bs?s=~~~~~ "&"&ql=1",1,"TOTAL
> ASSETS")
>
> I also took out the "1" after the "=" and it still seemed to work, so I
> assume the "1" passed in after the comma does that?
>
Original:
1648;YahooQBS;
FQ1;=RCHGetTableCel
ASSETS"
Note it is getting the "Key Statistics" page (i.e. "ks"
below has "bs" for the balance sheet. That's why you're no longer getting
an error.
If you want to test a formula in your workbook, you could do:
=smfEval(SUBSTITUTE
...where C4 contains a ticker symbol and C3 contains your element
definition formula without the "="
RCHGetTableCell(
On Tue, May 7, 2013 at 9:15 PM, tora_toba <ctphan02@hotmail.
> Yes, I did several times. I also restarted excel as well in case it
> needed it to recognize the changes in the element text file. I'm thinking
> it's not adding the "&ql=1" after the ticker in the URL when replacing the
> "~~~~~" in the string. At least when I looked through your code, I
> couldn't see how it would add it offhand.
>
> Ok... now that I've said that, I just got it to work by changing the line
> in the element file to this:
>
> 1648;YahooQBS;
> FQ1;=RCHGetTableCel
> ASSETS"
>
> I also took out the "1" after the "=" and it still seemed to work, so I
> assume the "1" passed in after the comma does that?
>
Tue May 7, 2013 10:21 am (PDT) . Posted by:
"scott_mastbrook" scott_mastbrook
Thanks for the quick response. I'm still struggling to the get implied vol. I tried with an Excel Web query process as you suggested. The website apparently has multiple re-directions:
1) 888options re-directs to optionseducation.org
2) then I select quotes, and then
3) it opens a new tab in ivolatility.com address is:
http://oic.ivolatility.com/oic_adv_options.j;jsessionid=ae7Bg2px19B9?R=1&ticker=SPY
When I create an Excel Web query, I open the page in the query dialog box, select the big table, hit import, then it says:
oic_adv_options.j;jsessionid=ae7Bg2px19B9?R=1&ticker=SPY
and gives an error that says "Unable to open URL, The Internet site reports that the item you requested could not be found. (HTTP 1.0/ 404)". Am I doing something wrong here?
I also tried the smfForceRecalculation macro. Still just get "Error" in the cell.
Alternatively, I tried using the smfGetOptionQuotes function, and tried to get data item "6", but got an error as well, formula:
smfGetOptionQuotes("SPY Jun 2013 $160 Call","zba6",5)
returned:
SPY130622C00160000 4.11 4.12 Unrecognized item ID: 6
Any other thoughts on how to get implied volatility of a specific option?
Thanks,
Scott
--- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@...> wrote:
>
> Hmm. I just tried both after getting an SPY quotes page first, and they
> worked fine for me.
>
> Try getting a quote page on SPY with the EXCEL Web Query process instead of
> with IE. Then do an smfForceRecalculation to get a new copy of the web page
> its extracting data from.
>
> However, I will note I have had issues with them from time to time.
>
> On Sun, May 5, 2013 at 6:32 PM, scott_mastbrook
> <scott_mastbrook@...>wrote:
>
> > Randy,
> > I'm having trouble with this function. I tried entering the two
> > statements below and am just getting "Error" in the cell. Per the
> > directions, I have logged into the site on Internet Explorer, but still get
> > "Error". How do I get this function to work?
> >
> > Thanks,
> > Scott
> >
> > --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> > >
> > > Actually, those two formulas don't even need the 2nd and 3rd parameters.
> > > They can just be:
> > >
> > > =smfGet888OptionQuote("QQQ",,,,"HV10L")
> > > =smfGet888OptionQuote("QQQ",,,,"HV10LD")
> >
>
1) 888options re-directs to optionseducation.
2) then I select quotes, and then
3) it opens a new tab in ivolatility.
http://oic.ivolatil
When I create an Excel Web query, I open the page in the query dialog box, select the big table, hit import, then it says:
oic_adv_options.
and gives an error that says "Unable to open URL, The Internet site reports that the item you requested could not be found. (HTTP 1.0/ 404)". Am I doing something wrong here?
I also tried the smfForceRecalculati
Alternatively, I tried using the smfGetOptionQuotes function, and tried to get data item "6"
smfGetOptionQuotes(
returned:
SPY130622C00160000 4.11 4.12 Unrecognized item ID: 6
Any other thoughts on how to get implied volatility of a specific option?
Thanks,
Scott
--- In smf_addin@yahoogrou
>
> Hmm. I just tried both after getting an SPY quotes page first, and they
> worked fine for me.
>
> Try getting a quote page on SPY with the EXCEL Web Query process instead of
> with IE. Then do an smfForceRecalculati
> its extracting data from.
>
> However, I will note I have had issues with them from time to time.
>
> On Sun, May 5, 2013 at 6:32 PM, scott_mastbrook
> <scott_mastbrook
>
> > Randy,
> > I'm having trouble with this function. I tried entering the two
> > statements below and am just getting "Error" in the cell. Per the
> > directions, I have logged into the site on Internet Explorer, but still get
> > "Error"
> >
> > Thanks,
> > Scott
> >
> > --- In smf_addin@yahoogrou
> > >
> > > Actually, those two formulas don't even need the 2nd and 3rd parameters.
> > > They can just be:
> > >
> > > =smfGet888OptionQuo
> > > =smfGet888OptionQuo
> >
>
Tue May 7, 2013 10:38 am (PDT) . Posted by:
"scott_mastbrook" scott_mastbrook
I think I just solved the issue... I'm using the following and it is returning correctly (although slowly):
=smfGetOptionQuotes("SPY Jun 2013 $160 Call","zba6",1,"OX2")
returns:
Ticker Symbol Bid Price Ask Price Implied Volatility
SPY^^^130622C00160000 4.13 4.15 11.5
still not sure why I can't get the smfGet888OptionQuote function to work though. any thoughts on my issue there?
--- In smf_addin@yahoogroups.com , "scott_mastbrook" <scott_mastbrook@...> wrote:
>
> Thanks for the quick response. I'm still struggling to the get implied vol. I tried with an Excel Web query process as you suggested. The website apparently has multiple re-directions:
> 1) 888options re-directs to optionseducation.org
> 2) then I select quotes, and then
> 3) it opens a new tab in ivolatility.com address is:
> http://oic.ivolatility.com/oic_adv_options.j;jsessionid=ae7Bg2px19B9?R=1&ticker=SPY
>
> When I create an Excel Web query, I open the page in the query dialog box, select the big table, hit import, then it says:
> oic_adv_options.j;jsessionid=ae7Bg2px19B9?R=1&ticker=SPY
> and gives an error that says "Unable to open URL, The Internet site reports that the item you requested could not be found. (HTTP 1.0/ 404)". Am I doing something wrong here?
>
> I also tried the smfForceRecalculation macro. Still just get "Error" in the cell.
>
> Alternatively, I tried using the smfGetOptionQuotes function, and tried to get data item "6", but got an error as well, formula:
> smfGetOptionQuotes("SPY Jun 2013 $160 Call","zba6",5)
> returned:
> SPY130622C00160000 4.11 4.12 Unrecognized item ID: 6
>
> Any other thoughts on how to get implied volatility of a specific option?
>
> Thanks,
> Scott
>
>
>
>
> --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> >
> > Hmm. I just tried both after getting an SPY quotes page first, and they
> > worked fine for me.
> >
> > Try getting a quote page on SPY with the EXCEL Web Query process instead of
> > with IE. Then do an smfForceRecalculation to get a new copy of the web page
> > its extracting data from.
> >
> > However, I will note I have had issues with them from time to time.
> >
> > On Sun, May 5, 2013 at 6:32 PM, scott_mastbrook
> > <scott_mastbrook@>wrote:
> >
> > > Randy,
> > > I'm having trouble with this function. I tried entering the two
> > > statements below and am just getting "Error" in the cell. Per the
> > > directions, I have logged into the site on Internet Explorer, but still get
> > > "Error". How do I get this function to work?
> > >
> > > Thanks,
> > > Scott
> > >
> > > --- In smf_addin@yahoogroups.com , Randy Harmelink <rharmelink@> wrote:
> > > >
> > > > Actually, those two formulas don't even need the 2nd and 3rd parameters.
> > > > They can just be:
> > > >
> > > > =smfGet888OptionQuote("QQQ",,,,"HV10L")
> > > > =smfGet888OptionQuote("QQQ",,,,"HV10LD")
> > >
> >
>
=smfGetOptionQuotes
returns:
Ticker Symbol Bid Price Ask Price Implied Volatility
SPY^^^130622C001600
still not sure why I can't get the smfGet888OptionQuot
--- In smf_addin@yahoogrou
>
> Thanks for the quick response. I'm still struggling to the get implied vol. I tried with an Excel Web query process as you suggested. The website apparently has multiple re-directions:
> 1) 888options re-directs to optionseducation.
> 2) then I select quotes, and then
> 3) it opens a new tab in ivolatility.
> http://oic.ivolatil
>
> When I create an Excel Web query, I open the page in the query dialog box, select the big table, hit import, then it says:
> oic_adv_options.
> and gives an error that says "Unable to open URL, The Internet site reports that the item you requested could not be found. (HTTP 1.0/ 404)". Am I doing something wrong here?
>
> I also tried the smfForceRecalculati
>
> Alternatively, I tried using the smfGetOptionQuotes function, and tried to get data item "6"
> smfGetOptionQuotes(
> returned:
> SPY130622C00160000 4.11 4.12 Unrecognized item ID: 6
>
> Any other thoughts on how to get implied volatility of a specific option?
>
> Thanks,
> Scott
>
>
>
>
> --- In smf_addin@yahoogrou
> >
> > Hmm. I just tried both after getting an SPY quotes page first, and they
> > worked fine for me.
> >
> > Try getting a quote page on SPY with the EXCEL Web Query process instead of
> > with IE. Then do an smfForceRecalculati
> > its extracting data from.
> >
> > However, I will note I have had issues with them from time to time.
> >
> > On Sun, May 5, 2013 at 6:32 PM, scott_mastbrook
> > <scott_mastbrook
> >
> > > Randy,
> > > I'm having trouble with this function. I tried entering the two
> > > statements below and am just getting "Error" in the cell. Per the
> > > directions, I have logged into the site on Internet Explorer, but still get
> > > "Error"
> > >
> > > Thanks,
> > > Scott
> > >
> > > --- In smf_addin@yahoogrou
> > > >
> > > > Actually, those two formulas don't even need the 2nd and 3rd parameters.
> > > > They can just be:
> > > >
> > > > =smfGet888OptionQuo
> > > > =smfGet888OptionQuo
> > >
> >
>
Tue May 7, 2013 1:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Not a clue. Here's what I did:
1. Entered this formula and got the expected "Error" value returned:
=smfGet888OptionQuote("QQQ",,,,"HV10L")
2. Hit alt+d+d+w to open up the EXCEL Web Query and entered this web
address:
http://www.optionseducation.org/quotes.html?quote=SPY
...which is the ultimate result of all the redirection, as you noted.
So, basically, just a shortcut way to get to that final result instead of
navigating the other web pages to get there. Note that I cancel out of the
Web Query dialog at this point, instead of trying to import something. All
I needed to do was visit that web page to have the EXCEL IE object create
the necessary security cookie for further accesses to the iVolatility
website.
3. Ran the smfForceRecalculation macro. The formula recalculated with
0.047.
On Tue, May 7, 2013 at 10:38 AM, scott_mastbrook
<scott_mastbrook@yahoo.com >wrote:
>
> still not sure why I can't get the smfGet888OptionQuote function to work
> though. any thoughts on my issue there?
>
1. Entered this formula and got the expected "Error" value returned:
=smfGet888OptionQuo
2. Hit alt+d+d+w to open up the EXCEL Web Query and entered this web
address:
http://www.optionse
...which is the ultimate result of all the redirection, as you noted.
So, basically, just a shortcut way to get to that final result instead of
navigating the other web pages to get there. Note that I cancel out of the
Web Query dialog at this point, instead of trying to import something. All
I needed to do was visit that web page to have the EXCEL IE object create
the necessary security cookie for further accesses to the iVolatility
website.
3. Ran the smfForceRecalculati
0.047.
On Tue, May 7, 2013 at 10:38 AM, scott_mastbrook
<scott_mastbrook@
>
> still not sure why I can't get the smfGet888OptionQuot
> though. any thoughts on my issue there?
>
Wed May 8, 2013 12:35 am (PDT) . Posted by:
"weekeewawa" weekeewawa
hello randy,
can i know what is the code to extract the ''full description'' of for example
http://www.reuters.com/finance/stocks/companyProfile?symbol=GOOG.O
thanks!
can i know what is the code to extract the ''full description&#
http://www.reuters.
thanks!
Wed May 8, 2013 1:30 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Try:
=SUBSTITUTE(SUBSTITUTE(smfGetTagContent("
http://www.reuters.com/finance/stocks/companyProfile?symbol=GOOG.O ","div",1,"Full
Description"),"</p>",CHAR(13)),"<p>",CHAR(10))
On Wed, May 8, 2013 at 12:35 AM, weekeewawa <weekeewawa@yahoo.com > wrote:
>
> can i know what is the code to extract the ''full description'' of for
> example
>
> http://www.reuters.com/finance/stocks/companyProfile?symbol=GOOG.O
>
=SUBSTITUTE(
http://www.reuters.
Description"
On Wed, May 8, 2013 at 12:35 AM, weekeewawa <weekeewawa@yahoo.
>
> can i know what is the code to extract the ''full description&#
> example
>
> http://www.reuters.
>
Tidak ada komentar:
Posting Komentar