Kamis, 23 April 2015

[smf_addin] Digest Number 3389

9 Messages

Digest #3389

Messages

Wed Apr 22, 2015 9:06 am (PDT) . Posted by:

austinmlazar

Gentleman,


I have hit a road block in the midst of a very exciting project I was working on, using the RCHGetElementNumber () function I have implemented over 43 important metrics that automatically generate for every ticker entered, I have made 506 rows with the plan to reweight the S&P dynamically with an Expected Alpha based weighting system. After about 145 tickers that worked seamlessly and uploaded all 43 data points within 45 seconds almost every time. Below a certain point it starts to give me error outputs for every data point and I know this isn't right because it worked seamlessly with any random ticker I would put it. I also tried to recreate another sheet thinking maybe the worksheet was overloaded but do I have to create an entirely new workbook for every increment of 145 or are there other fixes to this?
Thanks,
Austin

Wed Apr 22, 2015 9:49 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The add-in is intended for ad hoc data retrieval. The way it extracts data
is to save a copy of the web page, then to extract any data elements from
that saved web page (as opposed to retrieving the web page once for each
data item that comes from the web page). However, it only has 1000 slots
for storing web pages. After those 1000 slots are full, all requests for
data from additional web pages will error out.

I really didn't want people to start building databases of information,
simply because they could. That would be unfair to the free data services,
and with even people doing it might encourage them to code their web pages
so that something like the add-in couldn't access them.

However, having said that, I think the smfUpdateDownloadTable process might
be the best way for you to go. That is a macro that fills in a
2-dimensional table -- tickers by data items -- with values instead of
formulas that recalculate every time the workbook is opened. Updating is
controlled by you -- whenever you run the macro.

If you use the beta version of the add-in, you can even tell it which
ranges to update -- entire rows (i.e. tickers), entire columns (i.e.
specific data items, or even some range within the table. I sometimes have
a column with a "Timestamp" so I know when I last updated the row.

Some information on that macro can be found on the "Tips and FAQS" web page
on the add-in web site. Templates are in the FILES area of the Yahoo group,
including the template I always start from, because it has everything
already set up for the macro:

smfUpdateDownloadTable-Sample.xls

One thing not documented on the "Tips and FAQs" web page is that there is
also a "referback" method. That is, using "~~~n~~~" (where "n" is 1, 2, 3,
or whatever) says to use the value from that many columns to the left. So,
"~~~1~~~" says to replace the data item from the previous column where
"~~~1~~~" is. One technique I've used there is to have something like:

TEXT(COLUMNS($E2:Q2)-1,"~~~0~~~")

...where I want to use the value of whatever is in column E in my formula
in column Q. Otherwise I needed to hardcode a "~~~12~~~" in the formula.
However, if I inserted a new column, I need to make changes to all
hardcoded formulas that use column E. By using the "COLUMNS()" function,
the referral stays intact if columns are inserted.

So, for example, a formula for column Q of the table might look like:

="(""~~~1~~~""+""~~~2~~~"")/"""&TEXT(COLUMNS($E2:Q2)-1,"~~~0~~~")&"""-1"

I've even evolved the process to the point of assigning the referbacks to
defined names, so I can do something like:

="IF(ISNUMBER("&cBid2&"),(MIN("&cStrike2&","&cPrice&")+"&cBid2&")/"&cPrice&"-1,""--"")"

...where cBid2, cStrike2, and cPrice all look like that TEXT() formula
above. The defined names make it a lot easier to understand what the
formula is referring to. :)

On Wed, Apr 22, 2015 at 8:58 AM, austinmlazar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> I have hit a road block in the midst of a very exciting project I was
> working on, using the RCHGetElementNumber () function I have implemented
> over 43 important metrics that automatically generate for every ticker
> entered, I have made 506 rows with the plan to reweight the S&P dynamically
> with an Expected Alpha based weighting system. After about 145 tickers that
> worked seamlessly and uploaded all 43 data points within 45 seconds almost
> every time. Below a certain point it starts to give me error outputs for
> every data point and I know this isn't right because it worked seamlessly
> with any random ticker I would put it. I also tried to recreate another
> sheet thinking maybe the worksheet was overloaded but do I have to create
> an entirely new workbook for every increment of 145 or are there other
> fixes to this?
>
>

Wed Apr 22, 2015 2:51 pm (PDT) . Posted by:

"Ron Spruell" hashky

Randy -
Thanks!  I had to change the 10 to 20 to make it work for me.I want to get Average Maturity from this pagehttp://www.cefconnect.com/Details/Summary.aspx?Ticker=ACG

I have tried = RCHGetTableCell("http://www.cefconnect.com/Details/Summary.aspx?ticker="&$B8,1,"Portfolio Characteristics","Average Maturity:")and=smfGetTagContent("http://www.cefconnect.com/Details/Summary.aspx?Ticker="&$B7,"td",0,"Portfolio Characteristics","Average Maturity")
I can't get either one of these to work..

From: "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Tuesday, April 21, 2015 4:32 PM
Subject: Re: [smf_addin] CEF Morningstar gold, silver, bronze ratings

  This extracts a "Bronze" rating for BLE:

=PROPER(smfStrExtr(RCHGetWebData("http://performance.morningstar.com/funds/cef/total-returns.action?t="&C6,"r_star",50,10),"'r_","_"))

Just needed a web page that doesn't get a redirection to one that requires an exchange on it (makes it harder to generate an appropriate URL).

On Tue, Apr 21, 2015 at 2:07 PM, Ron Spruell hashky@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

Never mind.  I found your 4/16 note."=smfConvertData(smfStrExtr(RCHGetWebData("http://financials.morningstar.com/company-profile/c.action?t="&B2,"r_star"),"r_star","&#39;"))"which works.
I still don't know where to get the gold, silver, and bronze rating..

#yiv4436541768 -- #yiv4436541768ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv4436541768 #yiv4436541768ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv4436541768 #yiv4436541768ygrp-mkp #yiv4436541768hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv4436541768 #yiv4436541768ygrp-mkp #yiv4436541768ads {margin-bottom:10px;}#yiv4436541768 #yiv4436541768ygrp-mkp .yiv4436541768ad {padding:0 0;}#yiv4436541768 #yiv4436541768ygrp-mkp .yiv4436541768ad p {margin:0;}#yiv4436541768 #yiv4436541768ygrp-mkp .yiv4436541768ad a {color:#0000ff;text-decoration:none;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ygrp-lc {font-family:Arial;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ygrp-lc #yiv4436541768hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ygrp-lc .yiv4436541768ad {margin-bottom:10px;padding:0 0;}#yiv4436541768 #yiv4436541768actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv4436541768 #yiv4436541768activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv4436541768 #yiv4436541768activity span {font-weight:700;}#yiv4436541768 #yiv4436541768activity span:first-child {text-transform:uppercase;}#yiv4436541768 #yiv4436541768activity span a {color:#5085b6;text-decoration:none;}#yiv4436541768 #yiv4436541768activity span span {color:#ff7900;}#yiv4436541768 #yiv4436541768activity span .yiv4436541768underline {text-decoration:underline;}#yiv4436541768 .yiv4436541768attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv4436541768 .yiv4436541768attach div a {text-decoration:none;}#yiv4436541768 .yiv4436541768attach img {border:none;padding-right:5px;}#yiv4436541768 .yiv4436541768attach label {display:block;margin-bottom:5px;}#yiv4436541768 .yiv4436541768attach label a {text-decoration:none;}#yiv4436541768 blockquote {margin:0 0 0 4px;}#yiv4436541768 .yiv4436541768bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv4436541768 .yiv4436541768bold a {text-decoration:none;}#yiv4436541768 dd.yiv4436541768last p a {font-family:Verdana;font-weight:700;}#yiv4436541768 dd.yiv4436541768last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv4436541768 dd.yiv4436541768last p span.yiv4436541768yshortcuts {margin-right:0;}#yiv4436541768 div.yiv4436541768attach-table div div a {text-decoration:none;}#yiv4436541768 div.yiv4436541768attach-table {width:400px;}#yiv4436541768 div.yiv4436541768file-title a, #yiv4436541768 div.yiv4436541768file-title a:active, #yiv4436541768 div.yiv4436541768file-title a:hover, #yiv4436541768 div.yiv4436541768file-title a:visited {text-decoration:none;}#yiv4436541768 div.yiv4436541768photo-title a, #yiv4436541768 div.yiv4436541768photo-title a:active, #yiv4436541768 div.yiv4436541768photo-title a:hover, #yiv4436541768 div.yiv4436541768photo-title a:visited {text-decoration:none;}#yiv4436541768 div#yiv4436541768ygrp-mlmsg #yiv4436541768ygrp-msg p a span.yiv4436541768yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv4436541768 .yiv4436541768green {color:#628c2a;}#yiv4436541768 .yiv4436541768MsoNormal {margin:0 0 0 0;}#yiv4436541768 o {font-size:0;}#yiv4436541768 #yiv4436541768photos div {float:left;width:72px;}#yiv4436541768 #yiv4436541768photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv4436541768 #yiv4436541768photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv4436541768 #yiv4436541768reco-category {font-size:77%;}#yiv4436541768 #yiv4436541768reco-desc {font-size:77%;}#yiv4436541768 .yiv4436541768replbq {margin:4px;}#yiv4436541768 #yiv4436541768ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv4436541768 #yiv4436541768ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv4436541768 #yiv4436541768ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv4436541768 #yiv4436541768ygrp-mlmsg select, #yiv4436541768 input, #yiv4436541768 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv4436541768 #yiv4436541768ygrp-mlmsg pre, #yiv4436541768 code {font:115% monospace;}#yiv4436541768 #yiv4436541768ygrp-mlmsg * {line-height:1.22em;}#yiv4436541768 #yiv4436541768ygrp-mlmsg #yiv4436541768logo {padding-bottom:10px;}#yiv4436541768 #yiv4436541768ygrp-msg p a {font-family:Verdana;}#yiv4436541768 #yiv4436541768ygrp-msg p#yiv4436541768attach-count span {color:#1E66AE;font-weight:700;}#yiv4436541768 #yiv4436541768ygrp-reco #yiv4436541768reco-head {color:#ff7900;font-weight:700;}#yiv4436541768 #yiv4436541768ygrp-reco {margin-bottom:20px;padding:0px;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ov li a {font-size:130%;text-decoration:none;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv4436541768 #yiv4436541768ygrp-sponsor #yiv4436541768ov ul {margin:0;padding:0 0 0 8px;}#yiv4436541768 #yiv4436541768ygrp-text {font-family:Georgia;}#yiv4436541768 #yiv4436541768ygrp-text p {margin:0 0 1em 0;}#yiv4436541768 #yiv4436541768ygrp-text tt {font-size:120%;}#yiv4436541768 #yiv4436541768ygrp-vital ul li:last-child {border-right:none !important;}#yiv4436541768

Wed Apr 22, 2015 4:21 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your first formula worked for me. Both of these worked for me:

=RCHGetTableCell("http://www.cefconnect.com/Details/Summary.aspx?ticker="&$B8,1,"Average
Maturity:")

=smfGetTagContent("http://www.cefconnect.com/Details/Summary.aspx?Ticker="&$B7,"td",1,"Average
Maturity")

On Wed, Apr 22, 2015 at 2:48 PM, Ron Spruell hashky@... wrote:

>
> Thanks! I had to change the 10 to 20 to make it work for me.
> I want to get Average Maturity from this page
> http://www.cefconnect.com/Details/Summary.aspx?Ticker=ACG
>
> I have tried
> = RCHGetTableCell("http://www.cefconnect.com/Details/Summary.aspx?ticker="&$B8,1,"Portfolio
> Characteristics","Average Maturity:")
> and
> =smfGetTagContent("http://www.cefconnect.com/Details/Summary.aspx?Ticker="&$B7,"td",0,"Portfolio
> Characteristics&quot;,"Average Maturity")
>
> I can't get either one of these to work..
>

Wed Apr 22, 2015 3:08 pm (PDT) . Posted by:

"Roger Oesterling" roesterling

Why does the SMF formula =RCHGetYahooQuotes result in N/A for Yahoo index
symbol ^DJI, when the other index symbols work just fine?

Value

Symbol

l1

N/A

^DJI

Dow Jones Industrial Avg

2107.96

^GSPC

S&P 500

5035.17

^IXIC

Nasdaq

1265.48

^RUT

Russel 2000

What symbol should I use for Dow Jones Industrial Avg Index?

Thanks,

Roger Oesterling

Wed Apr 22, 2015 4:35 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Because several years ago, Dow Jones decided to restrict licensing of their
indexes data, and decided that Yahoo could no longer provide any Dow Jones
indexes data in CSV files. So neither RCHGetYahooQuotes() or
RCHGetYahooHistory() will work for any index that is owned by the Dow Jones
company.

https://help.yahoo.com/kb/finance/download-jones-index-data-sln2332.html

When I do indexes, I just use the ETFs based on them...

A work-around:

=smfConvertData(smfGetTagContent("
http://finance.yahoo.com/marketupdate/overview","span",-1,"_^dji"))

On Wed, Apr 22, 2015 at 3:08 PM, 'Roger Oesterling&#39; roesterling@... wrote:

>
> Why does the SMF formula =RCHGetYahooQuotes result in N/A for Yahoo index
> symbol ^DJI, when the other index symbols work just fine?
>
>
>
> Value
>
> Symbol
>
> l1
>
> N/A
>
> ^DJI
>
> Dow Jones Industrial Avg
>
> 2107.96
>
> ^GSPC
>
> S&P 500
>
> 5035.17
>
> ^IXIC
>
> Nasdaq
>
> 1265.48
>
> ^RUT
>
> Russel 2000
>
>
>
> What symbol should I use for Dow Jones Industrial Avg Index?
>
>
>

Wed Apr 22, 2015 5:49 pm (PDT) . Posted by:

spudtatro

Thanks Randy for the quick reply. Works great.

Wed Apr 22, 2015 9:59 pm (PDT) . Posted by:

yvrflyguy

Hi there,


Does anybody have any experience with retrieving the data from the .xls file link at the bottom of a nasdaq.com historical price & volume page?


For example:


iPath S&P 500 VIX Short Term Futures ETN (VXX) Historical Prices & Data - NASDAQ.com http://www.nasdaq.com/symbol/vxx/historical



http://www.nasdaq.com/symbol/vxx/historical

iPath S&P 500 VIX Short Term Futures ETN (VXX) Historica... http://www.nasdaq.com/symbol/vxx/historical VXX historical prices, VXX historical data,iPath S&P 500 VIX Short Term Futures ETN historical prices, historical stock prices, historical prices, historica...



View on www.nasdaq.com http://www.nasdaq.com/symbol/vxx/historical
Preview by Yahoo




I'd like to retrieve the data in a similar way to using the smfGetCSVFile() function, but the link is javascript and I can't find a suitable link reference in the page code to do this.


Any suggestions?


Thanks,


Peter

Wed Apr 22, 2015 11:42 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but I don't see a way to do it with the add-in. It doesn't really
grab a file that's sitting on the Internet anywhere. It appears to use JSON
to create the file on the fly.

You would probably need VBA code that interacts with the IE object to do
the task.

On Wed, Apr 22, 2015 at 9:59 PM, a330pete@hotmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Does anybody have any experience with retrieving the data from the .xls
> file link at the bottom of a nasdaq.com historical price & volume page?
>
> For example:
>
> iPath S&P 500 VIX Short Term Futures ETN (VXX) Historical Prices & Data -
> NASDAQ.com <http://www.nasdaq.com/symbol/vxx/historical>
> [image: image] <http://www.nasdaq.com/symbol/vxx/historical>
> iPath S&P 500 VIX Short Term Futures ETN (VXX) Historica...
> <http://www.nasdaq.com/symbol/vxx/historical>
> VXX historical prices, VXX historical data,iPath S&P 500 VIX Short Term
> Futures ETN historical prices, historical stock prices, historical prices,
> historica...
> View on www.nasdaq.com
> <http://www.nasdaq.com/symbol/vxx/historical>
> Preview by Yahoo
>
>
> I'd like to retrieve the data in a similar way to using the
> smfGetCSVFile() function, but the link is javascript and I can't find a
> suitable link reference in the page code to do this.
>
> Any suggestions?
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar