[smf_addin] Digest Number 3449

EXCEL Stock Market Functions Add-in Group

6 Messages

Digest #3449

1a

Updating Open Interest by rgearyiii

1b

Re: Updating Open Interest by "Randy Harmelink" rharmelink

2a

US Corporate BBB bonds yield by "Jose Jacob" pepecan47

2b

Re: US Corporate BBB bonds yield by "Randy Harmelink" rharmelink

2c

Re: US Corporate BBB bonds yield by "Jose Jacob" pepecan47

3a

Re: ERROR from smfGetOptionQuotes function [1 Attachment] by alberto.ratti

Messages

1a

Updating Open Interest

Fri Jul 3, 2015 6:34 am (PDT) . Posted by:

rgearyiii

I have a daily subscription that provides end-of-day quotes on all million or so OPRA options, with symbols conveniently in the same form as used by Yahoo! (e.g., "A150717C00040000"). As with all option quotes, they include start-of-day open interest rather than end-of-day open interest, which is not updated until early the next morning. Thus I'm looking for a good way to update my open interest each morning.

I'm aware that I can get open interest for all options per underlying ticker symbol with URLs like www.theocc.com/webapps/series-search?symbolType=U&symbol=A http://www.theocc.com/webapps/series-search?symbolType=U&symbol=A , which would require over four thousand web queries, which would be quite efficient.

It also seems that I ought to be able to get open interest using formulas like

=RCHGetYahooQuotes("A150717C00040000","o1") https://finance.yahoo.com/q?s=A150717C00040000&ql=1
but this results in "N/A" no matter what symbol I enter, even though open interest is available at https://finance.yahoo.com/q?s=A150717C00040000&ql=1 https://finance.yahoo.com/q?s=A150717C00040000&ql=1

Are there any other good methods of updating open interest that I should consider?

Thanks

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

1b

Re: Updating Open Interest

Fri Jul 3, 2015 8:27 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The OCC file sounds like the best way to go. Even if you could get it from
Yahoo, the CSV file used by RCHGetYahooQuotes() is limited to 400 requests
at a time. You're getting much more than that with the OCC file.

Yahoo used to have an API for option quotes, but it doesn't appear to be
working any longer.

Google has an options API you could use, but it would be harder to use than
the OCC file:

http://www.google.com/finance/option_chain?q=AAPL&output=json

On Fri, Jul 3, 2015 at 2:45 AM, rgearyiii@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I have a daily subscription that provides end-of-day quotes on all million
> or so OPRA options, with symbols conveniently in the same form as used by
> Yahoo! (e.g., "A150717C00040000"). As with all option quotes, they include
> start-of-day open interest rather than end-of-day open interest, which is
> not updated until early the next morning. Thus I'm looking for a good way
> to update my open interest each morning.
>
> I'm aware that I can get open interest for all options per underlying
> ticker symbol with URLs like
> www.theocc.com/webapps/series-search?symbolType=U&symbol=A , which would
> require over four thousand web queries, which would be quite efficient.
>
> It also seems that I ought to be able to get open interest using formulas
> like
>
> =RCHGetYahooQuotes("A150717C00040000","o1")
> <https://finance.yahoo.com/q?s=A150717C00040000&ql=1>
>
>
> but this results in "N/A" no matter what symbol I enter, even though open
> interest is available at
> https://finance.yahoo.com/q?s=A150717C00040000&ql=1
>
> Are there any other good methods of updating open interest that I should
> consider?
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (2) . Top ^

2a

US Corporate BBB bonds yield

Fri Jul 3, 2015 8:50 am (PDT) . Posted by:

"Jose Jacob" pepecan47

I need some help. I am trying to get the US Corporate BBB bonds yield from:

https://ycharts.com/indicators/us_corporate_bbb_effective_yield

using the following formula:

=RCHGetTableCell("https://ycharts.com/indicators/us_corporate_bbb_effective_yield",1,"US Corporate BBB Effective Yield Summary","Last Value:")

I get :

"US Durable Goods Inventories"

I am lost.

Regards,
Jose L. Jacob

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2b

Re: US Corporate BBB bonds yield

Fri Jul 3, 2015 10:00 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

RCHGetTableCell() won't work for the extraction, because the web page
doesn't present the data within an HTML table. So you ended up getting the
first table cell of the next table, further down the page.

Try:

=smfConvertData(smfStrExtr(smfGetTagContent("
https://ycharts.com/indicators/us_corporate_bbb_effective_yield","li",-1,"Last
Value:"),"</strong>","~"))

On Fri, Jul 3, 2015 at 8:50 AM, Jose Jacob pepecan47@... wrote:

> I need some help. I am trying to get the US Corporate BBB bonds yield from:
>
> https://ycharts.com/indicators/us_corporate_bbb_effective_yield
>
> using the following formula:
>
> =RCHGetTableCell("
> https://ycharts.com/indicators/us_corporate_bbb_effective_yield",1,"US
> Corporate BBB Effective Yield Summary","Last Value:")
>
> I get :
>
> "US Durable Goods Inventories"
>
> I am lost.
>

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

2c

Re: US Corporate BBB bonds yield

Fri Jul 3, 2015 10:14 am (PDT) . Posted by:

"Jose Jacob" pepecan47

Thanks a lot!!
Regards,
Jose L. Jacob

--------------------------------------------
On Fri, 7/3/15, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

Subject: Re: [smf_addin] US Corporate BBB bonds yield
To: smf_addin@yahoogroups.com
Received: Friday, July 3, 2015, 1:00 PM


 









RCHGetTableCell() won't work
for the extraction, because the web page doesn't present
the data within an HTML table. So you ended up getting the
first table cell of the next table, further down the
page.

Try:

=smfConvertData(smfStrExtr(smfGetTagContent("https://ycharts.com/indicators/us_corporate_bbb_effective_yield","li",-1,"Last
Value:"),"</strong>","~"))

On Fri,
Jul 3, 2015 at 8:50 AM, Jose Jacob pepecan47@... wrote:
I need some help. I am trying to get the US
Corporate BBB bonds yield from:



https://ycharts.com/indicators/us_corporate_bbb_effective_yield



using the following formula:



=RCHGetTableCell("https://ycharts.com/indicators/us_corporate_bbb_effective_yield",1,"US
Corporate BBB Effective Yield Summary","Last
Value:")



I get :



"US Durable Goods Inventories"



I am lost.












#yiv7297887795 #yiv7297887795 --
#yiv7297887795ygrp-mkp {
border:1px solid #d8d8d8;font-family:Arial;margin:10px
0;padding:0 10px;}

#yiv7297887795 #yiv7297887795ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv7297887795 #yiv7297887795ygrp-mkp #yiv7297887795hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv7297887795 #yiv7297887795ygrp-mkp #yiv7297887795ads {
margin-bottom:10px;}

#yiv7297887795 #yiv7297887795ygrp-mkp .yiv7297887795ad {
padding:0 0;}

#yiv7297887795 #yiv7297887795ygrp-mkp .yiv7297887795ad p {
margin:0;}

#yiv7297887795 #yiv7297887795ygrp-mkp .yiv7297887795ad a {
color:#0000ff;text-decoration:none;}
#yiv7297887795 #yiv7297887795ygrp-sponsor
#yiv7297887795ygrp-lc {
font-family:Arial;}

#yiv7297887795 #yiv7297887795ygrp-sponsor
#yiv7297887795ygrp-lc #yiv7297887795hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv7297887795 #yiv7297887795ygrp-sponsor
#yiv7297887795ygrp-lc .yiv7297887795ad {
margin-bottom:10px;padding:0 0;}

#yiv7297887795 #yiv7297887795actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#yiv7297887795 #yiv7297887795activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#yiv7297887795 #yiv7297887795activity span {
font-weight:700;}

#yiv7297887795 #yiv7297887795activity span:first-child {
text-transform:uppercase;}

#yiv7297887795 #yiv7297887795activity span a {
color:#5085b6;text-decoration:none;}

#yiv7297887795 #yiv7297887795activity span span {
color:#ff7900;}

#yiv7297887795 #yiv7297887795activity span
.yiv7297887795underline {
text-decoration:underline;}

#yiv7297887795 .yiv7297887795attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}

#yiv7297887795 .yiv7297887795attach div a {
text-decoration:none;}

#yiv7297887795 .yiv7297887795attach img {
border:none;padding-right:5px;}

#yiv7297887795 .yiv7297887795attach label {
display:block;margin-bottom:5px;}

#yiv7297887795 .yiv7297887795attach label a {
text-decoration:none;}

#yiv7297887795 blockquote {
margin:0 0 0 4px;}

#yiv7297887795 .yiv7297887795bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv7297887795 .yiv7297887795bold a {
text-decoration:none;}

#yiv7297887795 dd.yiv7297887795last p a {
font-family:Verdana;font-weight:700;}

#yiv7297887795 dd.yiv7297887795last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#yiv7297887795 dd.yiv7297887795last p
span.yiv7297887795yshortcuts {
margin-right:0;}

#yiv7297887795 div.yiv7297887795attach-table div div a {
text-decoration:none;}

#yiv7297887795 div.yiv7297887795attach-table {
width:400px;}

#yiv7297887795 div.yiv7297887795file-title a, #yiv7297887795
div.yiv7297887795file-title a:active, #yiv7297887795
div.yiv7297887795file-title a:hover, #yiv7297887795
div.yiv7297887795file-title a:visited {
text-decoration:none;}

#yiv7297887795 div.yiv7297887795photo-title a,
#yiv7297887795 div.yiv7297887795photo-title a:active,
#yiv7297887795 div.yiv7297887795photo-title a:hover,
#yiv7297887795 div.yiv7297887795photo-title a:visited {
text-decoration:none;}

#yiv7297887795 div#yiv7297887795ygrp-mlmsg
#yiv7297887795ygrp-msg p a span.yiv7297887795yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv7297887795 .yiv7297887795green {
color:#628c2a;}

#yiv7297887795 .yiv7297887795MsoNormal {
margin:0 0 0 0;}

#yiv7297887795 o {
font-size:0;}

#yiv7297887795 #yiv7297887795photos div {
float:left;width:72px;}

#yiv7297887795 #yiv7297887795photos div div {
border:1px solid
#666666;height:62px;overflow:hidden;width:62px;}

#yiv7297887795 #yiv7297887795photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#yiv7297887795 #yiv7297887795reco-category {
font-size:77%;}

#yiv7297887795 #yiv7297887795reco-desc {
font-size:77%;}

#yiv7297887795 .yiv7297887795replbq {
margin:4px;}

#yiv7297887795 #yiv7297887795ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean,
sans-serif;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg table {
font-size:inherit;font:100%;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg select,
#yiv7297887795 input, #yiv7297887795 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg pre, #yiv7297887795
code {
font:115% monospace;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg * {
line-height:1.22em;}

#yiv7297887795 #yiv7297887795ygrp-mlmsg #yiv7297887795logo {
padding-bottom:10px;}


#yiv7297887795 #yiv7297887795ygrp-msg p a {
font-family:Verdana;}

#yiv7297887795 #yiv7297887795ygrp-msg
p#yiv7297887795attach-count span {
color:#1E66AE;font-weight:700;}

#yiv7297887795 #yiv7297887795ygrp-reco
#yiv7297887795reco-head {
color:#ff7900;font-weight:700;}

#yiv7297887795 #yiv7297887795ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv7297887795 #yiv7297887795ygrp-sponsor #yiv7297887795ov
li a {
font-size:130%;text-decoration:none;}

#yiv7297887795 #yiv7297887795ygrp-sponsor #yiv7297887795ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv7297887795 #yiv7297887795ygrp-sponsor #yiv7297887795ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv7297887795 #yiv7297887795ygrp-text {
font-family:Georgia;}

#yiv7297887795 #yiv7297887795ygrp-text p {
margin:0 0 1em 0;}

#yiv7297887795 #yiv7297887795ygrp-text tt {
font-size:120%;}

#yiv7297887795 #yiv7297887795ygrp-vital ul li:last-child {
border-right:none !important;
}
#yiv7297887795

Reply to sender . Reply to group . Reply via Web Post . All Messages (3) . Top ^

3a

Re: ERROR from smfGetOptionQuotes function [1 Attachment]

Fri Jul 3, 2015 9:18 am (PDT) . Posted by:

alberto.ratti

The beta version of the add-in works perfectly.

Reply to sender . Reply to group . Reply via Web Post . All Messages (5) . Top ^

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Visit Your Group

• New Members 20

• Privacy • Unsubscribe • Terms of Use