8 Messages
Digest #3755
Messages
Tue Jul 26, 2016 2:10 pm (PDT) . Posted by:
dwstelsel
Using OTM# and ITM# no longer seems to work as a entry for PStrike within =smfGetYahooOptionQuote()
If a specific strike price is entered as PStrike, for example, $110, the formula will work.
Is there a solution to know what the bid is on OTM2 is for a specific stock?
I played around with =smfGetOptionStrikes(), but could not figure a way to just get OTM2.
Thank you,
David
If a specific strike price is entered as PStrike, for example, $110, the formula will work.
Is there a solution to know what the bid is on OTM2 is for a specific stock?
I played around with =smfGetOptionStrike
Thank you,
David
Tue Jul 26, 2016 6:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm more or less phasing out the OTM# and ITM# processing, because it's
been unreliable due of data presentation and it's been a pain to update
when sources change. But, yes, the smfGetOptionStrikes() function should be
able to get it for you.
This formula returns ten items:
=smfGetOptionStrikes("MMM","7/29/2016","C","Y",,10,1)
The ITM and OTM prices are synchronized around the center, so the first
five would be ITM and the last five would be OTM.
So if you want OTM2, you'd just do:
=INDEX(smfGetOptionStrikes("MMM","7/29/2016","C","Y",,10,1),7)
Note that the formula can also create your option ticker symbol to use for
smfGetOptionQuotes().
On Tue, Jul 26, 2016 at 2:10 PM, dwstelsel@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using OTM# and ITM# no longer seems to work as a entry for PStrike within
> =smfGetYahooOptionQuote()
>
> If a specific strike price is entered as PStrike, for example, $110, the
> formula will work.
>
> Is there a solution to know what the bid is on OTM2 is for a specific
> stock?
>
> I played around with =smfGetOptionStrikes(), but could not figure a way to
> just get OTM2.
>
been unreliable due of data presentation and it's been a pain to update
when sources change. But, yes, the smfGetOptionStrikes() function should be
able to get it for you.
This formula returns ten items:
=smfGetOptionStrikes("MMM","7/29/2016","C","Y",,10,1)
The ITM and OTM prices are synchronized around the center, so the first
five would be ITM and the last five would be OTM.
So if you want OTM2, you'd just do:
=INDEX(smfGetOptionStrikes("MMM","7/29/2016","C","Y",,10,1),7)
Note that the formula can also create your option ticker symbol to use for
smfGetOptionQuotes().
On Tue, Jul 26, 2016 at 2:10 PM, dwstelsel@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> Using OTM# and ITM# no longer seems to work as a entry for PStrike within
> =smfGetYahooOptionQ
>
> If a specific strike price is entered as PStrike, for example, $110, the
> formula will work.
>
> Is there a solution to know what the bid is on OTM2 is for a specific
> stock?
>
> I played around with =smfGetOptionStrike
> just get OTM2.
>
Tue Jul 26, 2016 2:13 pm (PDT) . Posted by:
"Paul Kent" 3bdc8da296b2813f51b77ed22f7c44bb
I've been using YLoader to download historical quotes for over 5 years. It is a one time $25 fee. You can select different quote sources including Quotemedia. I normally submit 15,900 symbols (OTC, NASDAQ, NYSE, AMEX) and am able to retrieve CSV files for all but 350 of them.
http://www.yloader.com/doc/index.html?page=source%2Finstallation.html
Buzz
To: smf_addin@yahoogroups.com
From: smf_addin@yahoogroups.com
Date: Mon, 25 Jul 2016 13:43:24 -0700
Subject: Re: [smf_addin] Yahoo and Verizon (or whoever)
The easiest way to allow easier access to any data is the use of customizable CSV files. Not only is it easier for the person accessing the data, but it should allow a more efficient way for the provider to convey the data.
For example, look at the Yahoo current quotes CSV files, or the previously free FinViz CSV files. MorningStar has done some things, but could have allowed for more customized access.
On Mon, Jul 25, 2016 at 1:08 PM, earladamy@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I failed to mention adjusted close in my post; however adjusted close information is critical to any kind of modeling or backtesting and that is what I use.
I've been doing more research on Quandl Quandl Financial and Economic Data Quandl Financial and Economic Data View on www.quandl.com Preview by Yahoo
this morning since that is the only source I know of which provides long term historical adjusted close. Access to the WIKI quote database is free, includes adjusted close, but is limited to 3000 company stocks. The Quote Media database, which also includes adjusted close and historical data back to 1998 is $49/month or $440/year, includes all listed stocks including ETFs but does not include mutual funds.
Quandl has an Excel API via a downloadable XLAM file. The ribbon style interface is clean and it can handle and date align multiple symbols in a single call. There is no VBA interface; however I have corresponded with Tammer Kamel, the founder in years past and he is very amenable to improvements which make the data more widely available. Have you/would you consider interfacing the SMF modules to Quandl?
http://www.yloader.com/doc/index.html?page=source%2Finstallation.html
Buzz
To: smf_addin@yahoogroups.com
From: smf_addin@yahoogroups.com
Date: Mon, 25 Jul 2016 13:43:24 -0700
Subject: Re: [smf_addin] Yahoo and Verizon (or whoever)
The easiest way to allow easier access to any data is the use of customizable CSV files. Not only is it easier for the person accessing the data, but it should allow a more efficient way for the provider to convey the data.
For example, look at the Yahoo current quotes CSV files, or the previously free FinViz CSV files. MorningStar has done some things, but could have allowed for more customized access.
On Mon, Jul 25, 2016 at 1:08 PM, earladamy@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
I failed to mention adjusted close in my post; however adjusted close information is critical to any kind of modeling or backtesting and that is what I use.
I've been doing more research on Quandl Quandl Financial and Economic Data Quandl Financial and Economic Data View on www.quandl.com Preview by Yahoo
this morning since that is the only source I know of which provides long term historical adjusted close. Access to the WIKI quote database is free, includes adjusted close, but is limited to 3000 company stocks. The Quote Media database, which also includes adjusted close and historical data back to 1998 is $49/month or $440/year, includes all listed stocks including ETFs but does not include mutual funds.
Quandl has an Excel API via a downloadable XLAM file. The ribbon style interface is clean and it can handle and date align multiple symbols in a single call. There is no VBA interface; however I have corresponded with Tammer Kamel, the founder in years past and he is very amenable to improvements which make the data more widely available. Have you/would you consider interfacing the SMF modules to Quandl?
Tue Jul 26, 2016 4:28 pm (PDT) . Posted by:
"Scott Hutchens" shhutchens6
I managed to get through the script error messages, do you happen to know what the download capacity limit of ibd metrics are? Before I have downloaded metrics like Zacks rank 200 tickers at a time and combining the lists later on. I don't have the etables subscription so I just manually download the lists and reference them with within my model but was just trying to save the fact of having to manually go on their website because it has so much going in the form of advertisements and moving objects it can be slow sometimes.
Is there a way to automate getting the lists from the stock lists page?
For the composite ranking I have been copying and pasting 50 tickers at a time into the "my stock lists" and exporting and saving the files 1-50, 51-100 etc. and souring the information from a master spreadsheet of those lists, which takes me about 40 min sometimes.
Thank you for your input! Ibd seems to continue to be a pain. I have contacted them in the past to see if I could get a master spreadsheet for their universe of stocks and metrics and offered to pay for it but they didn't seem t want to do that.
On Monday, July 25, 2016 8:08 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Your supposition is correct -- the original web page they send only has a list of 5 companies. Then, some script processing checks the security level and dynamically adds the other 45 items if you're a subscriber. Since their change late last year, I've had to change my eTable processing, and been grabbing the table from the eTables print option:
=RCHGetHTMLTable("http://research.investors.com/etables/IBD50IndexPrint.aspx?tabView=IBD100&columnsort1=ibd100rank&columnsorttype1=DESC&columnsort2=&columnsorttype2=DESC&search=&filter=","Company")
An alternative I played with was opening the XLS version. I had a VBA routine that would be triggered by a change in a cell, then download the workbook from IBD, copy the worksheet to my sheet, then close the opened workbook.
How are you planning to get the metrics for 1300 stocks? Both the add-in and IBD have download limits less than that.
Late last year, when they changed things up, this was my path to getting logged in:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/29668
On Mon, Jul 25, 2016 at 1:48 PM, Scott Hutchens shhutchens6@... wrote:
I am trying to get various stock lists from IBD such at the IBD 50 and CANSLIM Select. I am a subscriber to IBD and tried the use the RCHHTMLTable and RCHGetTableCell functions but I seem to only get the same table with the same five tickers, here is the function, =RCHGetHTMLTable("http://research.investors.com/stock-lists/ibd-50/","symbol",1). I think this might be a preview of the IBD 50 that excel is pulling from IBD without being signed in. In the past I have tried to access IBD through excel and was able to create the security cookie and used the ibd Template a few times but that was a couple years ago. Recently, I haven't been able to get create the security certificate due to the dreaded script error loop and having to use the task manager to exit excel. I have researched this topic a few times in the past few weeks but haven't been able to get excel and IBD to work together.
Basically, I want to automate and expedite my ibd experience, which entails getting the stocks lists and the composite ranking metric for 1300 stocks into excel. Any help on this subject would be greatly appreciated. And thanks again Randy for making this amazing tool I use all the time.
#yiv3264392734 #yiv3264392734 -- #yiv3264392734ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv3264392734 #yiv3264392734ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv3264392734 #yiv3264392734ygrp-mkp #yiv3264392734hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv3264392734 #yiv3264392734ygrp-mkp #yiv3264392734ads {margin-bottom:10px;}#yiv3264392734 #yiv3264392734ygrp-mkp .yiv3264392734ad {padding:0 0;}#yiv3264392734 #yiv3264392734ygrp-mkp .yiv3264392734ad p {margin:0;}#yiv3264392734 #yiv3264392734ygrp-mkp .yiv3264392734ad a {color:#0000ff;text-decoration:none;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ygrp-lc {font-family:Arial;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ygrp-lc #yiv3264392734hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ygrp-lc .yiv3264392734ad {margin-bottom:10px;padding:0 0;}#yiv3264392734 #yiv3264392734actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv3264392734 #yiv3264392734activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv3264392734 #yiv3264392734activity span {font-weight:700;}#yiv3264392734 #yiv3264392734activity span:first-child {text-transform:uppercase;}#yiv3264392734 #yiv3264392734activity span a {color:#5085b6;text-decoration:none;}#yiv3264392734 #yiv3264392734activity span span {color:#ff7900;}#yiv3264392734 #yiv3264392734activity span .yiv3264392734underline {text-decoration:underline;}#yiv3264392734 .yiv3264392734attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv3264392734 .yiv3264392734attach div a {text-decoration:none;}#yiv3264392734 .yiv3264392734attach img {border:none;padding-right:5px;}#yiv3264392734 .yiv3264392734attach label {display:block;margin-bottom:5px;}#yiv3264392734 .yiv3264392734attach label a {text-decoration:none;}#yiv3264392734 blockquote {margin:0 0 0 4px;}#yiv3264392734 .yiv3264392734bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv3264392734 .yiv3264392734bold a {text-decoration:none;}#yiv3264392734 dd.yiv3264392734last p a {font-family:Verdana;font-weight:700;}#yiv3264392734 dd.yiv3264392734last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv3264392734 dd.yiv3264392734last p span.yiv3264392734yshortcuts {margin-right:0;}#yiv3264392734 div.yiv3264392734attach-table div div a {text-decoration:none;}#yiv3264392734 div.yiv3264392734attach-table {width:400px;}#yiv3264392734 div.yiv3264392734file-title a, #yiv3264392734 div.yiv3264392734file-title a:active, #yiv3264392734 div.yiv3264392734file-title a:hover, #yiv3264392734 div.yiv3264392734file-title a:visited {text-decoration:none;}#yiv3264392734 div.yiv3264392734photo-title a, #yiv3264392734 div.yiv3264392734photo-title a:active, #yiv3264392734 div.yiv3264392734photo-title a:hover, #yiv3264392734 div.yiv3264392734photo-title a:visited {text-decoration:none;}#yiv3264392734 div#yiv3264392734ygrp-mlmsg #yiv3264392734ygrp-msg p a span.yiv3264392734yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv3264392734 .yiv3264392734green {color:#628c2a;}#yiv3264392734 .yiv3264392734MsoNormal {margin:0 0 0 0;}#yiv3264392734 o {font-size:0;}#yiv3264392734 #yiv3264392734photos div {float:left;width:72px;}#yiv3264392734 #yiv3264392734photos div div {border:1px solid #666666;min-height:62px;overflow:hidden;width:62px;}#yiv3264392734 #yiv3264392734photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv3264392734 #yiv3264392734reco-category {font-size:77%;}#yiv3264392734 #yiv3264392734reco-desc {font-size:77%;}#yiv3264392734 .yiv3264392734replbq {margin:4px;}#yiv3264392734 #yiv3264392734ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv3264392734 #yiv3264392734ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv3264392734 #yiv3264392734ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv3264392734 #yiv3264392734ygrp-mlmsg select, #yiv3264392734 input, #yiv3264392734 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv3264392734 #yiv3264392734ygrp-mlmsg pre, #yiv3264392734 code {font:115% monospace;}#yiv3264392734 #yiv3264392734ygrp-mlmsg * {line-height:1.22em;}#yiv3264392734 #yiv3264392734ygrp-mlmsg #yiv3264392734logo {padding-bottom:10px;}#yiv3264392734 #yiv3264392734ygrp-msg p a {font-family:Verdana;}#yiv3264392734 #yiv3264392734ygrp-msg p#yiv3264392734attach-count span {color:#1E66AE;font-weight:700;}#yiv3264392734 #yiv3264392734ygrp-reco #yiv3264392734reco-head {color:#ff7900;font-weight:700;}#yiv3264392734 #yiv3264392734ygrp-reco {margin-bottom:20px;padding:0px;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ov li a {font-size:130%;text-decoration:none;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv3264392734 #yiv3264392734ygrp-sponsor #yiv3264392734ov ul {margin:0;padding:0 0 0 8px;}#yiv3264392734 #yiv3264392734ygrp-text {font-family:Georgia;}#yiv3264392734 #yiv3264392734ygrp-text p {margin:0 0 1em 0;}#yiv3264392734 #yiv3264392734ygrp-text tt {font-size:120%;}#yiv3264392734 #yiv3264392734ygrp-vital ul li:last-child {border-right:none !important;}#yiv3264392734
Is there a way to automate getting the lists from the stock lists page?
For the composite ranking I have been copying and pasting 50 tickers at a time into the "my stock lists" and exporting and saving the files 1-50, 51-100 etc. and souring the information from a master spreadsheet of those lists, which takes me about 40 min sometimes.
Thank you for your input! Ibd seems to continue to be a pain. I have contacted them in the past to see if I could get a master spreadsheet for their universe of stocks and metrics and offered to pay for it but they didn't seem t want to do that.
On Monday, July 25, 2016 8:08 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
Your supposition is correct -- the original web page they send only has a list of 5 companies. Then, some script processing checks the security level and dynamically adds the other 45 items if you're a subscriber. Since their change late last year, I've had to change my eTable processing, and been grabbing the table from the eTables print option:
=RCHGetHTMLTable("http://research.investors.com/etables/IBD50IndexPrint.aspx?tabView=IBD100&columnsort1=ibd100rank&columnsorttype1=DESC&columnsort2=&columnsorttype2=DESC&search=&filter=","Company")
An alternative I played with was opening the XLS version. I had a VBA routine that would be triggered by a change in a cell, then download the workbook from IBD, copy the worksheet to my sheet, then close the opened workbook.
How are you planning to get the metrics for 1300 stocks? Both the add-in and IBD have download limits less than that.
Late last year, when they changed things up, this was my path to getting logged in:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/29668
On Mon, Jul 25, 2016 at 1:48 PM, Scott Hutchens shhutchens6@... wrote:
I am trying to get various stock lists from IBD such at the IBD 50 and CANSLIM Select. I am a subscriber to IBD and tried the use the RCHHTMLTable and RCHGetTableCell functions but I seem to only get the same table with the same five tickers, here is the function, =RCHGetHTMLTable("http://research.investors.com/stock-lists/ibd-50/","
Basically, I want to automate and expedite my ibd experience, which entails getting the stocks lists and the composite ranking metric for 1300 stocks into excel. Any help on this subject would be greatly appreciated. And thanks again Randy for making this amazing tool I use all the time.
#yiv3264392734 #yiv3264392734 -- #yiv3264392734ygrp-
Tue Jul 26, 2016 6:05 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Tue, Jul 26, 2016 at 4:25 PM, Scott Hutchens shhutchens6@
...wrote:
>
> I managed to get through the script error messages, do you happen to know
> what the download capacity limit of ibd metrics are? Before I have
> downloaded metrics like Zacks rank 200 tickers at a time and combining the
> lists later on. I don't have the etables subscription so I just manually
> download the lists and reference them with within my model but was just
> trying to save the fact of having to manually go on their website because
> it has so much going in the form of advertisements and moving objects it
> can be slow sometimes.
>
From their terms of service:
------------------------------
USE LIMITATIONS
Some IBD features are limited to the following views and/or retrievals per
24-hour period for each User:
IBD Stock Checkup feature is limited to 300 views and/or retrievals;
IBD Charts feature is limited to 1500 views/and/or retrievals; and
IBD Quotes feature is limited to 1500 views and/or retrievals.
You may not participate in coordinated efforts or group activities with
others in order to exceed these use limitations. Failure to comply with
these limitations will result in cessation of access and use privileges and
may result in legal action against the offending individuals and/or
organizations at the discretion of IBD.
------------------------------
*Is there a way to automate getting the lists from the stock lists page?*
>
------------------------------
There might be. They do a JSON call, which returns the data in an XML-type
format. From what I've been able to figure out, this general format MIGHT
work:
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
",D$60,$B61,,,,,1)
...where $B61 contains the counter of the company (i.e. 1 to 50 for the IBD
50 list) and D$60 contains the "name" of the data element you want for that
ranking. Data element names that can be used (not all are available on all
JSON files):
Rank
Symbol
CompanyName
CompRating
EPSRank
RelSt
GrpStr
Smr
AccDis
SponRating
Price
PriceClose
PriceChange
PricePerChange
VolPerChange
DailyVol
WeekHigh52
PerOffHigh
PERatio
DivYield
LastQtrSalesPerChg
LastQtrEpsPerChg
ConsecQtrEpsGrt15
CurQtrEpsEstPerChg
CurYrEpsEstPerChg
PretaxMargin
ROE
MgmtOwnsPer
QuoteUrl
StockCheckupUrl
LeaderboardUrl
EPSPriorQtr
QtrsFundIncrease
------------------------------
So if you just needed the 50 ticker symbols, you could do:
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",1,,,,,1)
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",2,,,,,1)
*...*
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",50,,,,,1)
------------------------------
A few other URLs I checked for:
http://research.investors.com/Services/SiteAjaxService.asmx/GetCanslimSelect?sortcolumn1=comprating&sortOrder1=desc&sortcolumn2=Symbol&sortOrder2=ASC
http://research.investors.com/Services/SiteAjaxService.asmx/GetWeeklyReview?sortcolumn1=indgrprank&sortOrder1=asc&sortcolumn2=symbol&sortOrder2=ASC
------------------------------
> For the composite ranking I have been copying and pasting 50 tickers at a
> time into the "my stock lists" and exporting and saving the files 1-50,
> 51-100 etc. and souring the information from a master spreadsheet of those
> lists, which takes me about 40 min sometimes.
>
------------------------------
I'd suggest you look at the smfUpdateDownloadTable templates here:
http://ogres-crypt.com/SMF/Works-In-Progress/
One advantage of the smfUpdateDownloadTable process is that it stores
values into your workbook, instead of having numerous formulas that always
need to recalculate when you open the worksheet.
What I would suggest is to add a column that saves the current date and
time. Then you have a time stamp for when a row was last updated. You can
update piecemeal. For example, if you hightlight ten rows of the table
before running the macro, the macro will only update those ten rows of data.
------------------------------
...wrote:
>
> I managed to get through the script error messages, do you happen to know
> what the download capacity limit of ibd metrics are? Before I have
> downloaded metrics like Zacks rank 200 tickers at a time and combining the
> lists later on. I don't have the etables subscription so I just manually
> download the lists and reference them with within my model but was just
> trying to save the fact of having to manually go on their website because
> it has so much going in the form of advertisements and moving objects it
> can be slow sometimes.
>
From their terms of service:
------------------------------
USE LIMITATIONS
Some IBD features are limited to the following views and/or retrievals per
24-hour period for each User:
IBD Stock Checkup feature is limited to 300 views and/or retrievals;
IBD Charts feature is limited to 1500 views/and/or retrievals; and
IBD Quotes feature is limited to 1500 views and/or retrievals.
You may not participate in coordinated efforts or group activities with
others in order to exceed these use limitations. Failure to comply with
these limitations will result in cessation of access and use privileges and
may result in legal action against the offending individuals and/or
organizations at the discretion of IBD.
------------------------------
*Is there a way to automate getting the lists from the stock lists page?*
>
------------------------------
There might be. They do a JSON call, which returns the data in an XML-type
format. From what I've been able to figure out, this general format MIGHT
work:
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
",D$60,$B61,,,,,1)
...where $B61 contains the counter of the company (i.e. 1 to 50 for the IBD
50 list) and D$60 contains the "name" of the data element you want for that
ranking. Data element names that can be used (not all are available on all
JSON files):
Rank
Symbol
CompanyName
CompRating
EPSRank
RelSt
GrpStr
Smr
AccDis
SponRating
Price
PriceClose
PriceChange
PricePerChange
VolPerChange
DailyVol
WeekHigh52
PerOffHigh
PERatio
DivYield
LastQtrSalesPerChg
LastQtrEpsPerChg
ConsecQtrEpsGrt15
CurQtrEpsEstPerChg
CurYrEpsEstPerChg
PretaxMargin
ROE
MgmtOwnsPer
QuoteUrl
StockCheckupUrl
LeaderboardUrl
EPSPriorQtr
QtrsFundIncrease
------------------------------
So if you just needed the 50 ticker symbols, you could do:
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",1,,,,,1)
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",2,,,,,1)
*...*
=smfGetTagContent("
http://research.investors.com/Services/SiteAjaxService.asmx/GetIBD50?sortcolumn1=ibd100rank&sortOrder1=ASC&sortcolumn2=&sortOrder2=ASC
","Symbol",50,,,,,1)
------------------------------
A few other URLs I checked for:
http://research.investors.com/Services/SiteAjaxService.asmx/GetCanslimSelect?sortcolumn1=comprating&sortOrder1=desc&sortcolumn2=Symbol&sortOrder2=ASC
http://research.investors.com/Services/SiteAjaxService.asmx/GetWeeklyReview?sortcolumn1=indgrprank&sortOrder1=asc&sortcolumn2=symbol&sortOrder2=ASC
------------------------------
> For the composite ranking I have been copying and pasting 50 tickers at a
> time into the "my stock lists" and exporting and saving the files 1-50,
> 51-100 etc. and souring the information from a master spreadsheet of those
> lists, which takes me about 40 min sometimes.
>
------------------------------
I'd suggest you look at the smfUpdateDownloadTable templates here:
http://ogres-crypt.com/SMF/Works-In-Progress/
One advantage of the smfUpdateDownloadTa
values into your workbook, instead of having numerous formulas that always
need to recalculate when you open the worksheet.
What I would suggest is to add a column that saves the current date and
time. Then you have a time stamp for when a row was last updated. You can
update piecemeal. For example, if you hightlight ten rows of the table
before running the macro, the macro will only update those ten rows of data.
------------
Tue Jul 26, 2016 8:22 pm (PDT) . Posted by:
gbullr
Hi Randy.
Trying to get Top Institutional holders and Mutual fund holders found here:
http://finance.yahoo.com/quote/MSFT/holders?ltr=1 http://finance.yahoo.com/quote/MSFT/holders?ltr=1
with following cell in a7 for top institutional holders. a4 is the link above.
=rchgethtmltable(A4,"Top Institutional Holders",-1,"Top Mutual Fund Holders",1)
Changing the -1, 1 above does notthing . No matter what I put in there formula output is Name.
Thanks very much for your help in advance.
Best.
Trying to get Top Institutional holders and Mutual fund holders found here:
http://finance.yahoo.com/quote/MSFT/holders?ltr=1 http://finance.yahoo.com/quote/MSFT/holders?ltr=1
with following cell in a7 for top institutional holders. a4 is the link above.
=rchgethtmltable(
Changing the -1, 1 above does notthing . No matter what I put in there formula output is Name.
Thanks very much for your help in advance.
Best.
Tue Jul 26, 2016 9:52 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Try either of:
=RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
Institutional Holders",1,"",2)
=RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
Institutional Holders",1,"Top Mutual Fund Holders",1)
"Top Institutional Holders" is actually OUTSIDE the table, so the
directional item(s) need to go to the NEXT table(s).
The "Name" result you were getting is from the previous table. It sounds
like you entered the formula into a single cell. You need to array-enter it
over a range if you want all the data in the table. An EXCEL function can
only return data into cells it's given permission to do so.
On Tue, Jul 26, 2016 at 8:22 PM, gaston.bullrich@
...wrote:
> Trying to get Top Institutional holders and Mutual fund holders found
> here:
>
> http://finance.yahoo.com/quote/MSFT/holders?ltr=1
>
> with following cell in a7 for top institutional holders. a4 is the link
> above.
>
> =rchgethtmltable(A4,"Top Institutional Holders",-1,"Top Mutual Fund
> Holders",1)
>
> Changing the -1, 1 above does notthing . No matter what I put in there
> formula output is Name.
>
>
=RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
Institutional Holders",1,"",2)
=RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
Institutional Holders",1,"Top Mutual Fund Holders",1)
"Top Institutional Holders" is actually OUTSIDE the table, so the
directional item(s) need to go to the NEXT table(s).
The "Name" result you were getting is from the previous table. It sounds
like you entered the formula into a single cell. You need to array-enter it
over a range if you want all the data in the table. An EXCEL function can
only return data into cells it's given permission to do so.
On Tue, Jul 26, 2016 at 8:22 PM, gaston.bullrich@
...wrote:
> Trying to get Top Institutional holders and Mutual fund holders found
> here:
>
> http://finance.yahoo.com/quote/MSFT/holders?ltr=1
>
> with following cell in a7 for top institutional holders. a4 is the link
> above.
>
> =rchgethtmltable(
> Holders"
>
> Changing the -1, 1 above does notthing . No matter what I put in there
> formula output is Name.
>
>
Tue Jul 26, 2016 10:01 pm (PDT) . Posted by:
"Gaston Bullrich" gbullr
Thanks Randy. Will try tomorrow AM.
All the best.
On Wed, Jul 27, 2016 at 12:52 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Try either of:
>
> =RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
> Institutional Holders",1,"",2)
> =RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
> Institutional Holders",1,"Top Mutual Fund Holders",1)
>
> "Top Institutional Holders" is actually OUTSIDE the table, so the
> directional item(s) need to go to the NEXT table(s).
>
> The "Name" result you were getting is from the previous table. It sounds
> like you entered the formula into a single cell. You need to array-enter it
> over a range if you want all the data in the table. An EXCEL function can
> only return data into cells it's given permission to do so.
>
>
> On Tue, Jul 26, 2016 at 8:22 PM, gaston.bullrich@
> ...wrote:
>
>> Trying to get Top Institutional holders and Mutual fund holders found
>> here:
>>
>> http://finance.yahoo.com/quote/MSFT/holders?ltr=1
>>
>> with following cell in a7 for top institutional holders. a4 is the link
>> above.
>>
>> =rchgethtmltable(A4,"Top Institutional Holders",-1,"Top Mutual Fund
>> Holders",1)
>>
>> Changing the -1, 1 above does notthing . No matter what I put in there
>> formula output is Name.
>>
>>
>
>
--
Gaston Bullrich
20 East 67th Street Apt 2F
New York, NY 10065
Cel. 917 517 9907
All the best.
On Wed, Jul 27, 2016 at 12:52 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Try either of:
>
> =RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
> Institutional Holders",1,"",2)
> =RCHGetHTMLTable("http://finance.yahoo.com/quote/MSFT/holders","Top
> Institutional Holders",1,"Top Mutual Fund Holders",1)
>
> "Top Institutional Holders" is actually OUTSIDE the table, so the
> directional item(s) need to go to the NEXT table(s).
>
> The "Name" result you were getting is from the previous table. It sounds
> like you entered the formula into a single cell. You need to array-enter it
> over a range if you want all the data in the table. An EXCEL function can
> only return data into cells it's given permission to do so.
>
>
> On Tue, Jul 26, 2016 at 8:22 PM, gaston.bullrich@
> ...wrote:
>
>> Trying to get Top Institutional holders and Mutual fund holders found
>> here:
>>
>> http://finance.yahoo.com/quote/MSFT/holders?ltr=1
>>
>> with following cell in a7 for top institutional holders. a4 is the link
>> above.
>>
>> =rchgethtmltable(
>> Holders"
>>
>> Changing the -1, 1 above does notthing . No matter what I put in there
>> formula output is Name.
>>
>>
>
>
--
Gaston Bullrich
20 East 67th Street Apt 2F
New York, NY 10065
Cel. 917 517 9907
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar