15 Messages
Digest #4014
Messages
Fri May 19, 2017 10:31 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Possible, but not quick or easy...best solution would probably be to write
your own VBA routine, and have it omit that row.
Unless I'm misunderstanding the question...?
On Fri, May 19, 2017 at 4:24 AM, halstian1@
...wrote:
>
> Is it possible to use the Google workaround
> =smfGetCSVFile("https://www.google.com/finance/historical?q=
> "&B6&"&startdate="&TEXT(C6,"mmm+dd,+yyyy")&"&enddate="&
> TEXT(D6,"mmm+dd,+yyyy")&"&output=csv";)
>
> but to exclude the header table?
>
>
your own VBA routine, and have it omit that row.
Unless I'm misunderstanding the question...?
On Fri, May 19, 2017 at 4:24 AM, halstian1@
...wrote:
>
> Is it possible to use the Google workaround
> =smfGetCSVFile("https://www.google.com/finance/historical?q=
> "&B6&"
> TEXT(D6,"
>
> but to exclude the header table?
>
>
Fri May 19, 2017 10:31 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
In general, I've found OptionsXPress to be the most reliable, but it is
also one of the slower websites. I use Yahoo myself, and don't generally
have problems. JNJ is looking OK this morning?
*Source:* *OX* *Yahoo* *Google*
*Ticker:* *JNJ* *JNJ* *JNJ*
*Expirations:* 2017-05-19 2017-05-19 2017-06-16
2017-05-26 2017-05-26 2018-01-19
2017-06-02 2017-06-02
2017-06-09 2017-06-09
2017-06-16 2017-06-16
2017-06-23 2017-06-23
2017-06-30 2017-06-30
2017-07-21 2017-07-21
2017-09-15 2017-09-15
2017-10-20 2017-10-20
2018-01-19 2018-01-19
2018-06-15 2018-06-15
2019-01-18 2019-01-18
On Fri, May 19, 2017 at 7:38 AM, raig002@
...wrote:
>
> I am using =smfGetOptionExpirations() function to retrieve option
> expiration dates,
> and I am having trouble to retrieve the correct exp. dates, for exmple for
> JNJ
> I get the wrong exp. date, in this case, I get 6/16/2017(June,16 2017),
> instead of 5/19/2017 (May 19, 2017), which is the correct one.
> I am trying to automate options but I am having hard time retrieving free
> data from Yahoo, because
> it is not reliable or wrong data.
>
> Is there a different function to retrieve option exp. dates?
>
> Somebody knows where can I get real time data for options, and retrieve to
> EXCEL
> similar to SMF add-in?. I can pay for monthly subscription.
>
also one of the slower websites. I use Yahoo myself, and don't generally
have problems. JNJ is looking OK this morning?
*Source:* *OX* *Yahoo* *Google*
*Ticker:* *JNJ* *JNJ* *JNJ*
*Expirations:
2017-05-26 2017-05-26 2018-01-19
2017-06-02 2017-06-02
2017-06-09 2017-06-09
2017-06-16 2017-06-16
2017-06-23 2017-06-23
2017-06-30 2017-06-30
2017-07-21 2017-07-21
2017-09-15 2017-09-15
2017-10-20 2017-10-20
2018-01-19 2018-01-19
2018-06-15 2018-06-15
2019-01-18 2019-01-18
On Fri, May 19, 2017 at 7:38 AM, raig002@
...wrote:
>
> I am using =smfGetOptionExpira
> expiration dates,
> and I am having trouble to retrieve the correct exp. dates, for exmple for
> JNJ
> I get the wrong exp. date, in this case, I get 6/16/2017(June,
> instead of 5/19/2017 (May 19, 2017), which is the correct one.
> I am trying to automate options but I am having hard time retrieving free
> data from Yahoo, because
> it is not reliable or wrong data.
>
> Is there a different function to retrieve option exp. dates?
>
> Somebody knows where can I get real time data for options, and retrieve to
> EXCEL
> similar to SMF add-in?. I can pay for monthly subscription.
>
Fri May 19, 2017 10:38 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
The parameters have changed, so you can't just rename the function and go...
On Fri, May 19, 2017 at 7:49 AM, navelhunt@
...wrote:
>
> Maybe i missed something, but the function in the XLA add in is called
> RCHgetYahooHistory and you refer to smfgetYahooHistory. I know its being
> rewritten, but where can i get the smf function or should i just rename the
> " rch" into "smf"? thanks
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
The parameters have changed, so you can't just rename the function and go...
On Fri, May 19, 2017 at 7:49 AM, navelhunt@
...wrote:
>
> Maybe i missed something, but the function in the XLA add in is called
> RCHgetYahooHistory and you refer to smfgetYahooHistory. I know its being
> rewritten, but where can i get the smf function or should i just rename the
> " rch" into "smf"
>
Fri May 19, 2017 10:59 am (PDT) . Posted by:
"Michael Thomas" thomas91112
When you updated, where did you find the new SMF Add-In file to download? Don't see this latest version on the ogres-crypt.com/SMF website.Thanks
From: "mthmu3uhchaa4cu3fj53vppa7f2ab7cx7srhngvp@yahoo.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Friday, May 19, 2017 8:54 AM
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
Everything seems to be working fine based on your latest upload:
My old calls were:
RchGetYahooHistory(C1,YEAR(A1),MONTH(A1),DAY(A1),YEAR(A1),MONTH(A1),DAY(A1),"d","A",0)
The new calls are:
smfGetYahooHistory(C1,A1,A1,"d","c",0)
Of course, execution is much slower due to the increased computing load.
Am I correct in saying that, starting with a dividend factor of 1.0000 (call it DF) and then working backwards, DF stays constant until a dividend is declared (call it D). Then, the dividend day's adjusted closing price (call it C) is used to calculate a new DF (which will stay constant until the next dividend is encountered):
DF(new) = DF(old) * (1 - D/C)
I can't express strongly enough my appreciation for all you do for us. #yiv7693476884 #yiv7693476884 -- #yiv7693476884ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv7693476884 #yiv7693476884ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv7693476884 #yiv7693476884ygrp-mkp #yiv7693476884hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv7693476884 #yiv7693476884ygrp-mkp #yiv7693476884ads {margin-bottom:10px;}#yiv7693476884 #yiv7693476884ygrp-mkp .yiv7693476884ad {padding:0 0;}#yiv7693476884 #yiv7693476884ygrp-mkp .yiv7693476884ad p {margin:0;}#yiv7693476884 #yiv7693476884ygrp-mkp .yiv7693476884ad a {color:#0000ff;text-decoration:none;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ygrp-lc {font-family:Arial;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ygrp-lc #yiv7693476884hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ygrp-lc .yiv7693476884ad {margin-bottom:10px;padding:0 0;}#yiv7693476884 #yiv7693476884actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv7693476884 #yiv7693476884activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv7693476884 #yiv7693476884activity span {font-weight:700;}#yiv7693476884 #yiv7693476884activity span:first-child {text-transform:uppercase;}#yiv7693476884 #yiv7693476884activity span a {color:#5085b6;text-decoration:none;}#yiv7693476884 #yiv7693476884activity span span {color:#ff7900;}#yiv7693476884 #yiv7693476884activity span .yiv7693476884underline {text-decoration:underline;}#yiv7693476884 .yiv7693476884attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv7693476884 .yiv7693476884attach div a {text-decoration:none;}#yiv7693476884 .yiv7693476884attach img {border:none;padding-right:5px;}#yiv7693476884 .yiv7693476884attach label {display:block;margin-bottom:5px;}#yiv7693476884 .yiv7693476884attach label a {text-decoration:none;}#yiv7693476884 blockquote {margin:0 0 0 4px;}#yiv7693476884 .yiv7693476884bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv7693476884 .yiv7693476884bold a {text-decoration:none;}#yiv7693476884 dd.yiv7693476884last p a {font-family:Verdana;font-weight:700;}#yiv7693476884 dd.yiv7693476884last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv7693476884 dd.yiv7693476884last p span.yiv7693476884yshortcuts {margin-right:0;}#yiv7693476884 div.yiv7693476884attach-table div div a {text-decoration:none;}#yiv7693476884 div.yiv7693476884attach-table {width:400px;}#yiv7693476884 div.yiv7693476884file-title a, #yiv7693476884 div.yiv7693476884file-title a:active, #yiv7693476884 div.yiv7693476884file-title a:hover, #yiv7693476884 div.yiv7693476884file-title a:visited {text-decoration:none;}#yiv7693476884 div.yiv7693476884photo-title a, #yiv7693476884 div.yiv7693476884photo-title a:active, #yiv7693476884 div.yiv7693476884photo-title a:hover, #yiv7693476884 div.yiv7693476884photo-title a:visited {text-decoration:none;}#yiv7693476884 div#yiv7693476884ygrp-mlmsg #yiv7693476884ygrp-msg p a span.yiv7693476884yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv7693476884 .yiv7693476884green {color:#628c2a;}#yiv7693476884 .yiv7693476884MsoNormal {margin:0 0 0 0;}#yiv7693476884 o {font-size:0;}#yiv7693476884 #yiv7693476884photos div {float:left;width:72px;}#yiv7693476884 #yiv7693476884photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv7693476884 #yiv7693476884photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv7693476884 #yiv7693476884reco-category {font-size:77%;}#yiv7693476884 #yiv7693476884reco-desc {font-size:77%;}#yiv7693476884 .yiv7693476884replbq {margin:4px;}#yiv7693476884 #yiv7693476884ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv7693476884 #yiv7693476884ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv7693476884 #yiv7693476884ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv7693476884 #yiv7693476884ygrp-mlmsg select, #yiv7693476884 input, #yiv7693476884 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv7693476884 #yiv7693476884ygrp-mlmsg pre, #yiv7693476884 code {font:115% monospace;}#yiv7693476884 #yiv7693476884ygrp-mlmsg * {line-height:1.22em;}#yiv7693476884 #yiv7693476884ygrp-mlmsg #yiv7693476884logo {padding-bottom:10px;}#yiv7693476884 #yiv7693476884ygrp-msg p a {font-family:Verdana;}#yiv7693476884 #yiv7693476884ygrp-msg p#yiv7693476884attach-count span {color:#1E66AE;font-weight:700;}#yiv7693476884 #yiv7693476884ygrp-reco #yiv7693476884reco-head {color:#ff7900;font-weight:700;}#yiv7693476884 #yiv7693476884ygrp-reco {margin-bottom:20px;padding:0px;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ov li a {font-size:130%;text-decoration:none;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv7693476884 #yiv7693476884ygrp-sponsor #yiv7693476884ov ul {margin:0;padding:0 0 0 8px;}#yiv7693476884 #yiv7693476884ygrp-text {font-family:Georgia;}#yiv7693476884 #yiv7693476884ygrp-text p {margin:0 0 1em 0;}#yiv7693476884 #yiv7693476884ygrp-text tt {font-size:120%;}#yiv7693476884 #yiv7693476884ygrp-vital ul li:last-child {border-right:none !important;}#yiv7693476884
From: "mthmu3uhchaa4cu3fj53vppa7f2ab7cx7srhngvp@yahoo.com [smf_addin]" <smf_addin@yahoogroups.com>
To: smf_addin@yahoogroups.com
Sent: Friday, May 19, 2017 8:54 AM
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
Everything seems to be working fine based on your latest upload:
My old calls were:
RchGetYahooHistory(
The new calls are:
smfGetYahooHistory(
Of course, execution is much slower due to the increased computing load.
Am I correct in saying that, starting with a dividend factor of 1.0000 (call it DF) and then working backwards, DF stays constant until a dividend is declared (call it D). Then, the dividend day's adjusted closing price (call it C) is used to calculate a new DF (which will stay constant until the next dividend is encountered)
DF(new) = DF(old) * (1 - D/C)
I can't express strongly enough my appreciation for all you do for us. #yiv7693476884 #yiv7693476884 -- #yiv7693476884ygrp-
Fri May 19, 2017 11:01 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 10:54 AM, Michael Thomas thomas91112@ wrote:
>
> When you updated, where did you find the new SMF Add-In file to download?
> Don't see this latest version on the ogres-crypt.com/SMF website.
>
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 10:54 AM, Michael Thomas thomas91112@ wrote:
>
> When you updated, where did you find the new SMF Add-In file to download?
> Don't see this latest version on the ogres-crypt.
>
>
Fri May 19, 2017 11:01 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 7:44 AM, Eric Stats ericstats@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
> Randy, when you say it will just used the new function, what new function
> is that and is it available. You may have already explained this in
> another chain, but I've looked and I'm not seeing it; however, there are
> many emails and it is confusing for a novice like me. Thanks, Eric.
>
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 7:44 AM, Eric Stats ericstats@gmail.com [smf_addin]
<smf_addin@yahoogroups.com> wrote:
>
> Randy, when you say it will just used the new function, what new function
> is that and is it available. You may have already explained this in
> another chain, but I've looked and I'm not seeing it; however, there are
> many emails and it is confusing for a novice like me. Thanks, Eric.
>
>
Fri May 19, 2017 11:02 am (PDT) . Posted by:
websurfer1717@gmail.com
I just started to explore these functions when they stopped working. I appreciate all the effort to get them going again so quickly.
I want to get the total dividends for a year as a single value without a header. When I use the following I get "Error" returned as the value.
smfGetYahooHistory("MSFT",DATE(2016,1,1),DATE(2016,12,31),"a","x",0,0)
If set pNames to 1, I get "Dividend"; in the cell.
If I use an 2x1 array, I get the header row with "Dividend"; and a value of 1.47 in the second row. The value of 1.47 is correct for the year. So the "a" and "x" combination seems to work.
I also tried all the other pItems and only "s" gives the same "Error" value.
I'm using Excel 2013 with Windows 10.
I want to get the total dividends for a year as a single value without a header. When I use the following I get "Error" returned as the value.
smfGetYahooHistory(
If set pNames to 1, I get "Dividend"
If I use an 2x1 array, I get the header row with "Dividend"
I also tried all the other pItems and only "s" gives the same "Error" value.
I'm using Excel 2013 with Windows 10.
Fri May 19, 2017 11:20 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Fri, May 19, 2017 at 8:01 AM, Joe Williams joe.c.williams@
...wrote:
>
> Everything seems to be working fine based on your latest upload:
>
> My old calls were:
>
> RchGetYahooHistory(C1,YEAR(A1),MONTH(A1),DAY(A1),YEAR(A1),
> MONTH(A1),DAY(A1),"d","A",0)
>
> The new calls are:
>
> smfGetYahooHistory(C1,A1,A1,"d","c",0)
>
> Of course, execution is much slower due to the increased compute load.
>
Unfortunately, I don't think there is anything to do about the longer
update times. I like the quote I saw on one forum about these "security";
issues that different processing is working around:
"Don't attribute to evil plans what can more easily be attributed to
incompetence."
They went from a clean, streamlined interface to a convoluted,
time-consuming one. In the end, it's costing them, because even someone
accessing their data with a normal browser will be using a lot more of
their server power.
> =======
>
> I notice two differences:
>
> 1) Yahoo now supplies the current day's price on the historical prices
> list during market hours for ETFs.
>
> 2) If the current day's price is requested for all other (non-etf) stocks
> during market hours, the functionnow returns "Error" whereas before it
> returned zero.
>
In my testing, that seemed to change here and there, so I'm not sure what
Yahoo is going to be when things settle down.
Is there a reason you're using the historical quotes to get current day
data? Or am I inferring incorrectly?
>
> =======
>
> If the Yahoo historical table contains zeros, the function stops
> retrieving data:
>
> see VFINX on 6/29/2016
>
That's also the date ^GSPC has an issue with. Their provider must not have
loaded that day correctly. I'll have to decide how to handle the
situation. Easiest would be to drop that day, but better would be to
backload it with the closing price from the following day, but then there's
an issue if there are two bad days in a row...
>
> =======
>
> Am I correct in saying that, starting with a dividend factor of 1.0000
> (call it DF) and then working backwards, DF stays constant until a dividend
> is declared (call it D). Then, the dividend day's adjusted closing price
> (call it C) is used to calculate a new DF (which will stay constant until
> the next dividend is encountered):
>
> DF(new) = DF(old) * (1 - D/C)
>
Correct. The actual formula:
dAdj = dAdj * (d1 - vDivAmt) / d1
Note that it will mean the weekly/monthly/quarterly/annual adjustments will
not match the daily, because the adjustment will be based on the period's
closing price.
One process I was toying with was to do everything daily, and then extract
the weekly/monthly/quarterly/annual myself. If done that way, it would be
nice to be able to specify the ending point of a period, and then echo it
backward. A complicated process when dealing with holidays and such.
In the end, smfPricesByDates() would be the best way to go for periodic
quotes retrievals.
...wrote:
>
> Everything seems to be working fine based on your latest upload:
>
> My old calls were:
>
> RchGetYahooHistory(
> MONTH(A1),DAY(
>
> The new calls are:
>
> smfGetYahooHistory(
>
> Of course, execution is much slower due to the increased compute load.
>
Unfortunately, I don't think there is anything to do about the longer
update times. I like the quote I saw on one forum about these "security"
issues that different processing is working around:
"Don'
incompetence.
They went from a clean, streamlined interface to a convoluted,
time-consuming one. In the end, it's costing them, because even someone
accessing their data with a normal browser will be using a lot more of
their server power.
> =======
>
> I notice two differences:
>
> 1) Yahoo now supplies the current day's price on the historical prices
> list during market hours for ETFs.
>
> 2) If the current day's price is requested for all other (non-etf) stocks
> during market hours, the functionnow returns "Error" whereas before it
> returned zero.
>
In my testing, that seemed to change here and there, so I'm not sure what
Yahoo is going to be when things settle down.
Is there a reason you're using the historical quotes to get current day
data? Or am I inferring incorrectly?
>
> =======
>
> If the Yahoo historical table contains zeros, the function stops
> retrieving data:
>
> see VFINX on 6/29/2016
>
That's also the date ^GSPC has an issue with. Their provider must not have
loaded that day correctly. I'll have to decide how to handle the
situation. Easiest would be to drop that day, but better would be to
backload it with the closing price from the following day, but then there's
an issue if there are two bad days in a row...
>
> =======
>
> Am I correct in saying that, starting with a dividend factor of 1.0000
> (call it DF) and then working backwards, DF stays constant until a dividend
> is declared (call it D). Then, the dividend day's adjusted closing price
> (call it C) is used to calculate a new DF (which will stay constant until
> the next dividend is encountered)
>
> DF(new) = DF(old) * (1 - D/C)
>
Correct. The actual formula:
dAdj = dAdj * (d1 - vDivAmt) / d1
Note that it will mean the weekly/monthly/
not match the daily, because the adjustment will be based on the period's
closing price.
One process I was toying with was to do everything daily, and then extract
the weekly/monthly/
nice to be able to specify the ending point of a period, and then echo it
backward. A complicated process when dealing with holidays and such.
In the end, smfPricesByDates(
quotes retrievals.
Fri May 19, 2017 11:28 am (PDT) . Posted by:
"lswpubrw" lswpubrlw
Randy,
I checked my SMF directory and see that RCH_StockK_Market_Functions.XLA seems to have been updated automatically? I did go to the link you listed so was that when it happened? Anyway =RCHGetYahooQuotes($A$7,"l1") seems to be working fine.
As always thanks so much for your work and sharing.
Bob Warasila
lswpubrw@optonline.net
warasir@sunysuffolk.edu
bobwarasila@lswpub.com
From: Randy Harmelink rharmelink@gmail.com [smf_addin]
Sent: Friday, May 19, 2017 2:01 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 10:54 AM, Michael Thomas thomas91112@ wrote:
When you updated, where did you find the new SMF Add-In file to download? Don't see this latest version on the ogres-crypt.com/SMF website.
I checked my SMF directory and see that RCH_StockK_Market_Functions.XLA seems to have been updated automatically? I did go to the link you listed so was that when it happened? Anyway =RCHGetYahooQuotes($A$7,"l1") seems to be working fine.
As always thanks so much for your work and sharing.
Bob Warasila
lswpubrw@optonline.net
warasir@sunysuffolk.edu
bobwarasila@lswpub.com
From: Randy Harmelink rharmelink@gmail.com [smf_addin]
Sent: Friday, May 19, 2017 2:01 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Update on Yahoo Historical Quotes
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Fri, May 19, 2017 at 10:54 AM, Michael Thomas thomas91112@ wrote:
When you updated, where did you find the new SMF Add-In file to download? Don't see this latest version on the ogres-crypt.
Fri May 19, 2017 11:40 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You can check to see which version is installed with:
=RCHGetElementNumber("Version")
The date on the updated version is 2017.05.18.
Clicking on the XLA link would ask you if you want to open it or download
it, unless you have your file extension set up to automatically open it.
Not sure what EXCEL would do in that case.
RCHGetYahooQuotes() is was not changed with this update. It's just the new
smfGetYahooHistory() function and an update to smfPricesByDates(). Which
reminds me that I need to update smfPricesBetween() as well.
On Fri, May 19, 2017 at 11:27 AM, 'lswpubrw' lswpubrw@
...
wrote:
>
> I checked my SMF directory and see that RCH_StockK_Market_Functions.XLA
> seems to have been updated automatically? I did go to the link you listed
> so was that when it happened? Anyway =RCHGetYahooQuotes($A$7,"l1") seems
> to be working fine.
>
>
>
=RCHGetElementNumbe
The date on the updated version is 2017.05.18.
Clicking on the XLA link would ask you if you want to open it or download
it, unless you have your file extension set up to automatically open it.
Not sure what EXCEL would do in that case.
RCHGetYahooQuotes(
smfGetYahooHistory(
reminds me that I need to update smfPricesBetween(
On Fri, May 19, 2017 at 11:27 AM, 'lswpubrw&#
...
wrote:
>
> I checked my SMF directory and see that RCH_StockK_Market_
> seems to have been updated automatically? I did go to the link you listed
> so was that when it happened? Anyway =RCHGetYahooQuotes(
> to be working fine.
>
>
>
Fri May 19, 2017 11:40 am (PDT) . Posted by:
eadamy
Thank you for the new smfGetYahooHistory function.
I've been running it through its paces and it's working well with most symbols. I have run into two symbols which is giving it problems: MDY and SHY. I have not dug into your code; however I have documented the behavior with MDY. This is my function call:
vArrYahoo = smfGetYahooHistory(pTicker, _
pStartDate:=datStartDate, _
pEndDate:=Date, _
pPeriod:="d", _
pItems:="dcv", _
pNames:=0, _
pResort:=1, _
pRows:=lRowsEstimate, _
pCols:=3)
pTicker is MDY, datStartDate is 07/01/2006, and iRowsEstimate is 2839. MDY has data back to 04/27/1995 which is serial 34816 so it should be returning 2839 rows of data.
The function is returning 225 non-blank rows. Row 1 is serial 42873 or 5/18/2017 and the serials are increasing which indicates the data has not been sorted. Row 225 is serial 42550 or 6/29/16.
I was able to download data for MDY with full history. However, it shows nulls on 06/29/16. A download of SPY shows that 6/29/16 is a trading date.
It appears that nulls in the data are causing the smf to throw some kind of internal error and stop further processing. Since the nulls are in Y's data; the only solution I can think of is to carry the previous day's data forward and continue processing.
Once I am able to run all the way through, I have historical summary data to which I can compare the new results.
And yes, it is a bit slower than the old version; however still much faster than Quandl.
Excellent start!
Earl
I've been running it through its paces and it's working well with most symbols. I have run into two symbols which is giving it problems: MDY and SHY. I have not dug into your code; however I have documented the behavior with MDY. This is my function call:
vArrYahoo = smfGetYahooHistory(
pStartDate:=
pEndDate:=Date, _
pPeriod:="
pItems:="
pNames:=0, _
pResort:=1, _
pRows:=lRowsEstimat
pCols:=3)
pTicker is MDY, datStartDate is 07/01/2006, and iRowsEstimate is 2839. MDY has data back to 04/27/1995 which is serial 34816 so it should be returning 2839 rows of data.
The function is returning 225 non-blank rows. Row 1 is serial 42873 or 5/18/2017 and the serials are increasing which indicates the data has not been sorted. Row 225 is serial 42550 or 6/29/16.
I was able to download data for MDY with full history. However, it shows nulls on 06/29/16. A download of SPY shows that 6/29/16 is a trading date.
It appears that nulls in the data are causing the smf to throw some kind of internal error and stop further processing. Since the nulls are in Y's data; the only solution I can think of is to carry the previous day's data forward and continue processing.
Once I am able to run all the way through, I have historical summary data to which I can compare the new results.
And yes, it is a bit slower than the old version; however still much faster than Quandl.
Excellent start!
Earl
Fri May 19, 2017 11:52 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The "a" does some funky things when selecting rows, and pNames=0 also does
some strange row-handling, so I'll have to look at the combination of the
two. Thanks.
In the meantime, a workaround:
=INDEX(smfGetYahooHistory("MSFT",DATE(2016,1,1),DATE(2016,12,31),"a","x",1,0,2,1),2)
Ahhhh....light bulb. The reason your original formula causes a problem is
that it only creates a one-by-one array for the add-in to deal with.
Dividend and split amounts require at least 2 rows to deal with the "lag"
nature of them (they get applied to following dates).
Maybe if I set the internal array to be a minimum of 2 rows, that will
solve the problem. The second row would just be lost when the data is
returned to a one-by-one range.
On Fri, May 19, 2017 at 10:48 AM, websurfer1717@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I just started to explore these functions when they stopped working. I
> appreciate all the effort to get them going again so quickly.
>
> I want to get the total dividends for a year as a single value without a
> header. When I use the following I get "Error" returned as the value.
>
>
> smfGetYahooHistory("MSFT",DATE(2016,1,1),DATE(2016,12,31),"a","x",0,0)
>
> If set pNames to 1, I get "Dividend"; in the cell.
> If I use an 2x1 array, I get the header row with "Dividend"; and a value of
> 1.47 in the second row. The value of 1.47 is correct for the year. So the
> "a" and "x" combination seems to work.
>
> I also tried all the other pItems and only "s" gives the same "Error"
> value.
>
> I'm using Excel 2013 with Windows 10.
>
>
some strange row-handling, so I'll have to look at the combination of the
two. Thanks.
In the meantime, a workaround:
=INDEX(smfGetYahooHistory("MSFT",DATE(2016,1,1),DATE(2016,12,31),"a","x",1,0,2,1),2)
Ahhhh....light bulb. The reason your original formula causes a problem is
that it only creates a one-by-one array for the add-in to deal with.
Dividend and split amounts require at least 2 rows to deal with the "lag"
nature of them (they get applied to following dates).
Maybe if I set the internal array to be a minimum of 2 rows, that will
solve the problem. The second row would just be lost when the data is
returned to a one-by-one range.
On Fri, May 19, 2017 at 10:48 AM, websurfer1717@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I just started to explore these functions when they stopped working. I
> appreciate all the effort to get them going again so quickly.
>
> I want to get the total dividends for a year as a single value without a
> header. When I use the following I get "Error" returned as the value.
>
>
> smfGetYahooHistory(
>
> If set pNames to 1, I get "Dividend"
> If I use an 2x1 array, I get the header row with "Dividend"
> 1.47 in the second row. The value of 1.47 is correct for the year. So the
> "a" and "x" combination seems to work.
>
> I also tried all the other pItems and only "s" gives the same "Error"
> value.
>
> I'm using Excel 2013 with Windows 10.
>
>
Fri May 19, 2017 11:52 am (PDT) . Posted by:
eadamy
I intended to investigate SHY before hitting send.
I just downloaded data for SHY back to 12/17/2014 and I'd estimate that at least half the records are null. The first occurs on 6/29/16 but there are few valid data rows prior to 10/23/15.
This indicates that the nulls could be a massive problem. You may need to consider a setting to handles nulls with option to carry previous day forward and throw up and error after n occurrences. I know I could live with 1 or 2 in a large download but dozens, especially on consecutive days.
Earl
I just downloaded data for SHY back to 12/17/2014 and I'd estimate that at least half the records are null. The first occurs on 6/29/16 but there are few valid data rows prior to 10/23/15.
This indicates that the nulls could be a massive problem. You may need to consider a setting to handles nulls with option to carry previous day forward and throw up and error after n occurrences. I know I could live with 1 or 2 in a large download but dozens, especially on consecutive days.
Earl
Fri May 19, 2017 11:59 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Yeah. "null" and "0" values are causing issues. I'll have to handle them,
because I don't think we can rely on Yahoo fixing them any time soon... (
I suppose I could plug the zero/null values in as zero values, set a
switch, then at the end, scrub the whole array if the switch was set,
carrying forward the data from the last valid date. Then there's no
scrubbing overhead for things that don't need it.
On Fri, May 19, 2017 at 11:52 AM, earladamy@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I intended to investigate SHY before hitting send.
>
> I just downloaded data for SHY back to 12/17/2014 and I'd estimate that at
> least half the records are null. The first occurs on 6/29/16 but there are
> few valid data rows prior to 10/23/15.
>
> This indicates that the nulls could be a massive problem. You may need to
> consider a setting to handles nulls with option to carry previous day
> forward and throw up and error after n occurrences. I know I could live
> with 1 or 2 in a large download but dozens, especially on consecutive days.
>
>
because I don't think we can rely on Yahoo fixing them any time soon... (
I suppose I could plug the zero/null values in as zero values, set a
switch, then at the end, scrub the whole array if the switch was set,
carrying forward the data from the last valid date. Then there's no
scrubbing overhead for things that don't need it.
On Fri, May 19, 2017 at 11:52 AM, earladamy@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> I intended to investigate SHY before hitting send.
>
> I just downloaded data for SHY back to 12/17/2014 and I'd estimate that at
> least half the records are null. The first occurs on 6/29/16 but there are
> few valid data rows prior to 10/23/15.
>
> This indicates that the nulls could be a massive problem. You may need to
> consider a setting to handles nulls with option to carry previous day
> forward and throw up and error after n occurrences. I know I could live
> with 1 or 2 in a large download but dozens, especially on consecutive days.
>
>
Fri May 19, 2017 11:23 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I am currently running Office365 under Windows 10 on both a 2-year-old
laptop and a brand new desktop:
-- Laptop = HP Pavilion 13-s128nr (Core i5, 8GB RAM, 128GB SSD)
-- Desktop = Dell Inspiron 3252 Small Desktop (Intel J3710, 8GB RAM, 1TB
7200 rpm SATA)
I would say 80% of my use is browsing (mostly FireFox, some Chrome) and
about 20% is EXCEL.
I am not happy with the Dell. It's much slower than my laptop. At least
half the speed. OTOH, it cost half as much too. But, for example, when I go
to NetFlix, I get two "unresponsive script" warnings because the main page
takes so long to load.
I'm considering going back to the laptop as my main processor, and using
the desktop as a backup device. I have both the laptop and the desktop
plugged into my monitor, and can toggle between them by simply changing
between them by hitting the "Input" button on the back of the monitor.
However, I use a USB keyboard and mouse, and it would be inconvenient to
move the USB dongles between the two machines.
All of my data files are on Google Drive, so it's easy to move between
machines with GD syncing files. FireFox and Chrome also do some level of
syncing (although not on search engines, which was a pain).
On Fri, May 19, 2017 at 10:27 AM, rr76012@
... wrote:
<rr76012@yahoo.com>
>
> Randy what version of windows and excel are you using?
> If you don't mind answering. On your computer how much RAM do you use and
> what intel chip do you use?
>
> I have to upgrade my computer but I don't know how fast a computer and
> memory to buy?
>
> I really only use a computer for your smfaddin in excel and reading email?
>
>
>
laptop and a brand new desktop:
-- Laptop = HP Pavilion 13-s128nr (Core i5, 8GB RAM, 128GB SSD)
-- Desktop = Dell Inspiron 3252 Small Desktop (Intel J3710, 8GB RAM, 1TB
7200 rpm SATA)
I would say 80% of my use is browsing (mostly FireFox, some Chrome) and
about 20% is EXCEL.
I am not happy with the Dell. It's much slower than my laptop. At least
half the speed. OTOH, it cost half as much too. But, for example, when I go
to NetFlix, I get two "unresponsive script" warnings because the main page
takes so long to load.
I'm considering going back to the laptop as my main processor, and using
the desktop as a backup device. I have both the laptop and the desktop
plugged into my monitor, and can toggle between them by simply changing
between them by hitting the "Input" button on the back of the monitor.
However, I use a USB keyboard and mouse, and it would be inconvenient to
move the USB dongles between the two machines.
All of my data files are on Google Drive, so it's easy to move between
machines with GD syncing files. FireFox and Chrome also do some level of
syncing (although not on search engines, which was a pain).
On Fri, May 19, 2017 at 10:27 AM, rr76012@
... wrote:
<rr76012@yahoo.com>
>
> Randy what version of windows and excel are you using?
> If you don't mind answering. On your computer how much RAM do you use and
> what intel chip do you use?
>
> I have to upgrade my computer but I don't know how fast a computer and
> memory to buy?
>
> I really only use a computer for your smfaddin in excel and reading email?
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar