[smf_addin] Digest Number 4278

EXCEL Stock Market Functions Add-in Group

4 Messages

Digest #4278

1a

Re: Unable to grab data from nasdaq.com by jgkiii

2a

Re: Automation Help by "erin wallace" pigletmc

3a

Access Tiingo? by sparkyjlg

3b

Re: Access Tiingo? by "Randy Harmelink" rharmelink

Messages

1a

Re: Unable to grab data from nasdaq.com

Fri Dec 29, 2017 9:03 am (PST) . Posted by:

jgkiii

Thank you Randy, saved me from asking a dumb question.

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

2a

Re: Automation Help

Fri Dec 29, 2017 10:00 am (PST) . Posted by:

"erin wallace" pigletmc

Thank you so much Randy!

--------------------------------------------
On Thu, 12/28/17, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:

Subject: Re: [smf_addin] Automation Help
To: smf_addin@yahoogroups.com
Date: Thursday, December 28, 2017, 7:19 PM


 









Interesting. The problem appears to
be that you can't use an upper case ticker symbol in the
URL. That is, this:
=RCHGetWebData("https://www.benzinga.com/stock/biib")

...returns the source code of a web
page, while this:
=RCHGetWebData("https://www.benzinga.com/stock/BIIB")

...does not. The browser redirects
the latter URL to be the former, but apparently
Microsoft's XMLHTTP routine is not handling the
redirection gracefully.
Easiest fix it to use EXCEL's 
LOWER() function when creating the URL. For example, if I
use this in cell D12, it works:
=MAX(RCHGetTableCell("https://www.benzinga.com/stock/"&LOWER(D11)&"",5,">DATE",,,,3,"</table",,"--"),RCHGetTableCell("https://www.benzinga.com/stock/"&LOWER(D11)&"",5,">DATE",,,,2,"</table",,"--"),RCHGetTableCell("https://www.benzinga.com/stock/"&LOWER(D11)&"",5,">DATE",,,,1,"</table",,"--"))

On Thu, Dec 28, 2017 at
1:25 PM, erin wallace pigletmc@.... wrote:









Can
you help me out with the attached file?  I can't get
the yellowed cells to populate.












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

#yiv0196088982 #yiv0196088982ygrp-mkp hr {
border:1px solid #d8d8d8;}

#yiv0196088982 #yiv0196088982ygrp-mkp #yiv0196088982hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}

#yiv0196088982 #yiv0196088982ygrp-mkp #yiv0196088982ads {
margin-bottom:10px;}

#yiv0196088982 #yiv0196088982ygrp-mkp .yiv0196088982ad {
padding:0 0;}

#yiv0196088982 #yiv0196088982ygrp-mkp .yiv0196088982ad p {
margin:0;}

#yiv0196088982 #yiv0196088982ygrp-mkp .yiv0196088982ad a {
color:#0000ff;text-decoration:none;}
#yiv0196088982 #yiv0196088982ygrp-sponsor
#yiv0196088982ygrp-lc {
font-family:Arial;}

#yiv0196088982 #yiv0196088982ygrp-sponsor
#yiv0196088982ygrp-lc #yiv0196088982hd {
margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}

#yiv0196088982 #yiv0196088982ygrp-sponsor
#yiv0196088982ygrp-lc .yiv0196088982ad {
margin-bottom:10px;padding:0 0;}

#yiv0196088982 #yiv0196088982actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

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

#yiv0196088982 #yiv0196088982activity span {
font-weight:700;}

#yiv0196088982 #yiv0196088982activity span:first-child {
text-transform:uppercase;}

#yiv0196088982 #yiv0196088982activity span a {
color:#5085b6;text-decoration:none;}

#yiv0196088982 #yiv0196088982activity span span {
color:#ff7900;}

#yiv0196088982 #yiv0196088982activity span
.yiv0196088982underline {
text-decoration:underline;}

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

#yiv0196088982 .yiv0196088982attach div a {
text-decoration:none;}

#yiv0196088982 .yiv0196088982attach img {
border:none;padding-right:5px;}

#yiv0196088982 .yiv0196088982attach label {
display:block;margin-bottom:5px;}

#yiv0196088982 .yiv0196088982attach label a {
text-decoration:none;}

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

#yiv0196088982 .yiv0196088982bold {
font-family:Arial;font-size:13px;font-weight:700;}

#yiv0196088982 .yiv0196088982bold a {
text-decoration:none;}

#yiv0196088982 dd.yiv0196088982last p a {
font-family:Verdana;font-weight:700;}

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

#yiv0196088982 dd.yiv0196088982last p
span.yiv0196088982yshortcuts {
margin-right:0;}

#yiv0196088982 div.yiv0196088982attach-table div div a {
text-decoration:none;}

#yiv0196088982 div.yiv0196088982attach-table {
width:400px;}

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

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

#yiv0196088982 div#yiv0196088982ygrp-mlmsg
#yiv0196088982ygrp-msg p a span.yiv0196088982yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#yiv0196088982 .yiv0196088982green {
color:#628c2a;}

#yiv0196088982 .yiv0196088982MsoNormal {
margin:0 0 0 0;}

#yiv0196088982 o {
font-size:0;}

#yiv0196088982 #yiv0196088982photos div {
float:left;width:72px;}

#yiv0196088982 #yiv0196088982photos div div {
border:1px solid
#666666;min-height:62px;overflow:hidden;width:62px;}

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

#yiv0196088982 #yiv0196088982reco-category {
font-size:77%;}

#yiv0196088982 #yiv0196088982reco-desc {
font-size:77%;}

#yiv0196088982 .yiv0196088982replbq {
margin:4px;}

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

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

#yiv0196088982 #yiv0196088982ygrp-mlmsg table {
font-size:inherit;font:100%;}

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

#yiv0196088982 #yiv0196088982ygrp-mlmsg pre, #yiv0196088982
code {
font:115% monospace;}

#yiv0196088982 #yiv0196088982ygrp-mlmsg * {
line-height:1.22em;}

#yiv0196088982 #yiv0196088982ygrp-mlmsg #yiv0196088982logo {
padding-bottom:10px;}


#yiv0196088982 #yiv0196088982ygrp-msg p a {
font-family:Verdana;}

#yiv0196088982 #yiv0196088982ygrp-msg
p#yiv0196088982attach-count span {
color:#1E66AE;font-weight:700;}

#yiv0196088982 #yiv0196088982ygrp-reco
#yiv0196088982reco-head {
color:#ff7900;font-weight:700;}

#yiv0196088982 #yiv0196088982ygrp-reco {
margin-bottom:20px;padding:0px;}

#yiv0196088982 #yiv0196088982ygrp-sponsor #yiv0196088982ov
li a {
font-size:130%;text-decoration:none;}

#yiv0196088982 #yiv0196088982ygrp-sponsor #yiv0196088982ov
li {
font-size:77%;list-style-type:square;padding:6px 0;}

#yiv0196088982 #yiv0196088982ygrp-sponsor #yiv0196088982ov
ul {
margin:0;padding:0 0 0 8px;}

#yiv0196088982 #yiv0196088982ygrp-text {
font-family:Georgia;}

#yiv0196088982 #yiv0196088982ygrp-text p {
margin:0 0 1em 0;}

#yiv0196088982 #yiv0196088982ygrp-text tt {
font-size:120%;}

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

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

3a

Access Tiingo?

Fri Dec 29, 2017 12:30 pm (PST) . Posted by:

sparkyjlg

Hello


Wondering if SMFGETYAHOOHISTORY could be setup to access the Tiingo API (or if it already does and I've just missed it)?


I'm finding that the Tiingo data is low cost, responsive to correcting errors and making a very concerted effort to develop processes to promptly and accurately reflect splits & dividends.


There is a freebee Ultimatestock downloader spreadsheet available but it only stacks historical dates across columns rather than rows as far as I know.


Thank you

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

3b

Re: Access Tiingo?

Fri Dec 29, 2017 4:06 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

Easy enough to do on your own. That's not to say it might not be
advantageous to have a separate function for Tiingo, or have functions like
smfPricesByDates() or smfPricesBetween() be able to use Tiingo as their
data source. Especially with Yahoo's failure to post recent dividends of
many equities (e.g. SPY, FOOLX).

But, as long as you've logged in to Tiingo with EXCEL, currently this:

=smfGetCSVFile("
https://api.tiingo.com/tiingo/daily/SPY/prices?startDate=2017-12-1&endDate=2017-12-31&format=csv&resampleFreq=daily
")

....returns this (note the dividend on 12./15, which is missing on Yahoo):

*date* *close* *high* *low* *open* *volume* *adjClose* *adjHigh* *adjLow*
*adjOpen* *adjVolume* *divCash* *splitFactor*
2017-12-01 $264.46 $265.31 $260.76 $264.76 164390902 $263.13 $263.97 $259.44
$263.42 164390902 0.0000 1.0000
2017-12-04 $264.14 $266.80 $264.08 $266.31 94040560 $262.81 $265.45 $262.75
$264.97 94040560 0.0000 1.0000
2017-12-05 $263.19 $265.15 $263.04 $264.43 77994544 $261.86 $263.81 $261.71
$263.10 77994544 0.0000 1.0000
2017-12-06 $263.24 $263.73 $262.71 $262.87 75898566 $261.91 $262.40 $261.38
$261.54 75898566 0.0000 1.0000
2017-12-07 $264.07 $264.43 $262.94 $263.09 77218569 $262.74 $263.10 $261.61
$261.76 77218569 0.0000 1.0000
2017-12-08 $265.51 $265.52 $264.03 $264.99 76563873 $264.17 $264.18 $262.70
$263.65 76563873 0.0000 1.0000
2017-12-11 $266.31 $266.38 $265.48 $265.58 83077533 $264.97 $265.04 $264.14
$264.24 83077533 0.0000 1.0000
2017-12-12 $266.78 $267.32 $266.35 $266.57 85195750 $265.43 $265.97 $265.00
$265.23 85195750 0.0000 1.0000
2017-12-13 $266.75 $267.56 $266.65 $267.06 102905409 $265.40 $266.21 $265.30
$265.71 102905409 0.0000 1.0000
2017-12-14 $265.66 $267.22 $265.60 $267.08 100666692 $264.32 $265.87 $264.26
$265.73 100666692 0.0000 1.0000
2017-12-15 $266.51 $267.04 $265.39 $265.50 144610290 $266.51 $267.04 $265.39
$265.50 144610290 1.3513 1.0000
2017-12-18 $268.20 $268.60 $267.98 $268.06 83653572 $268.20 $268.60 $267.98
$268.06 83653572 0.0000 1.0000
2017-12-19 $267.17 $268.53 $267.09 $268.49 82382876 $267.17 $268.53 $267.09
$268.49 82382876 0.0000 1.0000
2017-12-20 $267.03 $268.33 $266.69 $268.26 76751500 $267.03 $268.33 $266.69
$268.26 76751500 0.0000 1.0000
2017-12-21 $267.58 $268.39 $267.30 $267.67 67032339 $267.58 $268.39 $267.30
$267.67 67032339 0.0000 1.0000
2017-12-22 $267.51 $267.64 $266.90 $267.61 78720873 $267.51 $267.64 $266.90
$267.61 78720873 0.0000 1.0000
2017-12-26 $267.19 $267.44 $266.89 $267.05 45244433 $267.19 $267.44 $266.89
$267.05 45244433 0.0000 1.0000
2017-12-27 $267.32 $267.73 $267.01 $267.38 57750986 $267.32 $267.73 $267.01
$267.38 57750986 0.0000 1.0000
2017-12-28 $267.87 $267.92 $267.45 $267.89 45116139 $267.87 $267.92 $267.45
$267.89 45116139 0.0000 1.0000
2017-12-29 $266.86 $268.55 $266.64 $268.53 89479349 $266.86 $268.55 $266.64
$268.53 89479349 0.0000 1.0000

On Fri, Dec 29, 2017 at 1:21 PM, 1jkgriffin@
​...
wrote:

> Wondering if SMFGETYAHOOHISTORY could be setup to access the Tiingo API
> (or if it already does and I've just missed it)?
>
> I'm finding that the Tiingo data is low cost, responsive to correcting
> errors and making a very concerted effort to develop processes to promptly
> and accurately reflect splits & dividends.
>
> There is a freebee Ultimatestock downloader spreadsheet available but it
> only stacks historical dates across columns rather than rows as far as I
> know.
>
>
>

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

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

Visit Your Group

• New Members 11

• Privacy • Unsubscribe • Terms of Use