15 Messages
Digest #4032
3a
Re: smfPricesByDates-Example-Stocks-By-Dates returns error by "Rico Teikotte" dzi6ewzrdnxj7wkxo5f6mlhps7ydjxexnjzqjzdg
Messages
Thu May 25, 2017 1:34 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I just uploaded the 2017-05-25 *beta* version of the XLA file:
http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions.xla
The only change is that I made an attempt at a backward compatible
RCHGetYahooHistory() function, trying to set the default parameters values
to something appropriate.
-- If a request asks for both a closing price and an adjusted closing
price, it will ignore the request for the adjusted closing price.
-- If a request asks for closing price and no adjusted closing price, it
will get the adjusted closing price.
-- If the pAdjust parameter asks for unadjusted data, an error message will
be returned.
http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions.xla
The only change is that I made an attempt at a backward compatible
RCHGetYahooHistory(
to something appropriate.
-- If a request asks for both a closing price and an adjusted closing
price, it will ignore the request for the adjusted closing price.
-- If a request asks for closing price and no adjusted closing price, it
will get the adjusted closing price.
-- If the pAdjust parameter asks for unadjusted data, an error message will
be returned.
Thu May 25, 2017 1:37 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I tried to make a backward compatible version of RCHGetYahooHistory(). See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32343
I think you can leave those formulas alone?
On Mon, May 22, 2017 at 11:43 AM, Jose Jacob pepecan47@
...wrote:
> Hello Randy, first of all you are amazing, thank you very much for all you
> do for us.
>
> What are the new formulas with the latest changes?
>
> Dividend History
> =RCHGetYahooHistory(A2,,,,,,,"v")
>
> Price Historry
> =RCHGetYahooHistory(A1,,,,,,,,"DA",1,1)
>
> Dividend History
> =RCHGetYahooHistory(A1,,,,,,,"v",,,,1)
>
> Latest Dividend
> =INDEX(RCHGetYahooHistory(A13,,,,,,,"v",,0,,,1,2),1,2)
>
> Second Latest Dividend
> =INDEX(RCHGetYahooHistory(A13,,,,,,,"v",,0,,,2,2),2,2)
>
> Second Latest Dividend
> =INDEX(RCHGetYahooHistory(A14,,,,,,,"v",,0,,,3,2),3,2)
>
>
>
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32343
I think you can leave those formulas alone?
On Mon, May 22, 2017 at 11:43 AM, Jose Jacob pepecan47@
...wrote:
> Hello Randy, first of all you are amazing, thank you very much for all you
> do for us.
>
> What are the new formulas with the latest changes?
>
> Dividend History
> =RCHGetYahooHistory
>
> Price Historry
> =RCHGetYahooHistory
>
> Dividend History
> =RCHGetYahooHistory
>
> Latest Dividend
> =INDEX(RCHGetYahooH
>
> Second Latest Dividend
> =INDEX(RCHGetYahooH
>
> Second Latest Dividend
> =INDEX(RCHGetYahooH
>
>
>
Thu May 25, 2017 5:18 am (PDT) . Posted by:
lexstar
Somehow, mine doesn't work (with the new RCHGetYahooHistory()). Still gives only blank values. Unless, of course, I'm doing something else wrong.
Thu May 25, 2017 8:23 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Check the version and then give me the exact function parameters.
On Thu, May 25, 2017 at 5:18 AM, lexstar@
...wrote:
>
> Somehow, mine doesn't work (with the new RCHGetYahooHistory()). Still
> gives only blank values. Unless, of course, I'm doing something else wrong.
>
>
On Thu, May 25, 2017 at 5:18 AM, lexstar@
...wrote:
>
> Somehow, mine doesn't work (with the new RCHGetYahooHistory(
> gives only blank values. Unless, of course, I'm doing something else wrong.
>
>
Thu May 25, 2017 10:37 am (PDT) . Posted by:
lexstar
Version is 2017-05-25 beta
Transpose(RCHGetYahooHistory($b5,year(c$1),month(c$1),day(c$1),year(c$2),month(c$2),day(c$2),"m","dcohlv",0,1,0,100,6))
where $b5= SPY
c$1= 5/1/2017
c$2= 5/14/2017
Transpose(RCHGetYah
where $b5= SPY
c$1= 5/1/2017
c$2= 5/14/2017
Thu May 25, 2017 11:38 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmmm. I didn't like the data I was seeing. Couldn't reconcile it with daily
data I was looking at. It looks like Yahoo is generating the monthly values
BEFORE it selects the monthly data. See:
https://finance.yahoo.com/quote/MMM/history?period1=1493622000&period2=1494745200&interval=1mo&filter=history&frequency=1mo
https://finance.yahoo.com/quote/MMM/history?period1=1493596800&period2=1494806400&interval=1mo&filter=history&frequency=1mo
Not sure what is happening here. It looks the numbers are:
-- Closing and high price from 5/24
-- Open from 5/1
-- Low from 5/18
-- Volume is sum of volume from 5/1 thru 5/24
On Thu, May 25, 2017 at 10:37 AM, lexstar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYahooHistory($b5,year(c$1),month(c$1),day(c$1),year(c$2),month(c$2),day(c$2),"m","dcohlv",0,1,0,100,6))*
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
>
>
data I was looking at. It looks like Yahoo is generating the monthly values
BEFORE it selects the monthly data. See:
https://finance.yahoo.com/quote/MMM/history?period1=1493622000&period2=1494745200&interval=1mo&filter=history&frequency=1mo
https://finance.yahoo.com/quote/MMM/history?period1=1493596800&period2=1494806400&interval=1mo&filter=history&frequency=1mo
Not sure what is happening here. It looks the numbers are:
-- Closing and high price from 5/24
-- Open from 5/1
-- Low from 5/18
-- Volume is sum of volume from 5/1 thru 5/24
On Thu, May 25, 2017 at 10:37 AM, lexstar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYa
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
>
>
Thu May 25, 2017 11:46 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Not sure exactly what you're trying to do, but I think the
smfPricesBetween() might work better?
On Thu, May 25, 2017 at 10:37 AM, lexstar@
...wrote:
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYahooHistory($b5,year(c$1),month(c$1),day(c$1),year(c$2),month(c$2),day(c$2),"m","dcohlv",0,1,0,100,6))*
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
smfPricesBetween(
On Thu, May 25, 2017 at 10:37 AM, lexstar@
...wrote:
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYa
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
Thu May 25, 2017 1:00 pm (PDT) . Posted by:
lexstar
Basically, I'm just trying to get the Date, OHLCV for the Month of May 1st to May 24th for SPY
Thu May 25, 2017 1:03 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm getting data back, but I do see a problem. I had to fix the issue of
you overriding the range, because of the TRANSPOSE() function. Originally,
I defaulted the range size to 20000 by 7, then changed it if the function
was entered over a range, but the TRANSPOSE() makes that decision
incorrect. So now I'm only changing it to the size of the range if the two
dimensions are still the original defaults.
This is one reason backward compatibility is so difficult -- so many
permutations of parameters and use.
Thanks.
I now get this for range B1:D9:
2017-05-01
2017-05-14
2017-05-01
SPY $240.61
$238.68
$240.73
$235.43
1254847500
However, note that if a dividend had been paid between 5/14 and today,
those would NOT be adjusted prices. And monthly data will not give you
correct adjustments either.
On Thu, May 25, 2017 at 10:37 AM, lexstar@
...wrote:
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYahooHistory($b5,year(c$1),month(c$1),day(c$1),year(c$2),month(c$2),day(c$2),"m","dcohlv",0,1,0,100,6))*
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
you overriding the range, because of the TRANSPOSE() function. Originally,
I defaulted the range size to 20000 by 7, then changed it if the function
was entered over a range, but the TRANSPOSE() makes that decision
incorrect. So now I'm only changing it to the size of the range if the two
dimensions are still the original defaults.
This is one reason backward compatibility is so difficult -- so many
permutations of parameters and use.
Thanks.
I now get this for range B1:D9:
2017-05-01
2017-05-14
2017-05-01
SPY $240.61
$238.68
$240.73
$235.43
1254847500
However, note that if a dividend had been paid between 5/14 and today,
those would NOT be adjusted prices. And monthly data will not give you
correct adjustments either.
On Thu, May 25, 2017 at 10:37 AM, lexstar@
...wrote:
>
> Version is 2017-05-25 *beta*
>
>
> *Transpose(RCHGetYa
>
> *where $b5= SPY*
> *c$1= 5/1/2017*
> *c$2= 5/14/2017*
>
>
Thu May 25, 2017 2:07 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
But you used May 14 as your ending date?
In any case, I'm not trusting monthly data that Yahoo is returning at this
point.
I would suggest using smfPricesBetween(). For example:
Ticker Start Date End Date Close Date Close Price Open Price High Price Low
Price Volume
SPY 2017-05-01 2017-05-24 2017-05-24 $240.61 $238.68 $240.73 $235.43
1254847500
The first three columns are the input. The last six are (headers and data):
=smfPricesBetween("Header",,,"070802040609")
=smfPricesBetween(C11,D11,E11,"070802040609")
And the transpose work fine:
=TRANSPOSE(smfPricesBetween(C11,D11,E11,"070802040609"))
...returning:
2017-05-24
$240.61
$238.68
$240.73
$235.43
1254847500
On Thu, May 25, 2017 at 1:00 PM, lexstar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Basically, I'm just trying to get the Date, OHLCV for the Month of May 1st
> to May 24th for SPY
>
>
>
In any case, I'm not trusting monthly data that Yahoo is returning at this
point.
I would suggest using smfPricesBetween(). For example:
Ticker Start Date End Date Close Date Close Price Open Price High Price Low
Price Volume
SPY 2017-05-01 2017-05-24 2017-05-24 $240.61 $238.68 $240.73 $235.43
1254847500
The first three columns are the input. The last six are (headers and data):
=smfPricesBetween("Header",,,"070802040609")
=smfPricesBetween(C11,D11,E11,"070802040609")
And the transpose work fine:
=TRANSPOSE(smfPricesBetween(C11,D11,E11,"070802040609"))
...returning:
2017-05-24
$240.61
$238.68
$240.73
$235.43
1254847500
On Thu, May 25, 2017 at 1:00 PM, lexstar@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Basically, I'm just trying to get the Date, OHLCV for the Month of May 1st
> to May 24th for SPY
>
>
>
Thu May 25, 2017 1:41 am (PDT) . Posted by:
"Rico Teikotte" dzi6ewzrdnxj7wkxo5f6mlhps7ydjxexnjzqjzdg
Thank you Randy, will check!
Sent from Yahoo Mail. Get the app
On Tuesday, May 23, 2017 11:43 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
You'll need the experimental beta version of the add-in for smfPricesByDates() to work...
See:
https://groups.yahoo.com/neo/ groups/smf_addin/ conversations/messages/32112
https://groups.yahoo.com/neo/ groups/smf_addin/ conversations/messages/32114
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237
On Tue, May 23, 2017 at 11:16 AM, Rico Teikotte ricoteikotte@...wrote:
I've a problem with the smfPricesByDates-Example-S tocks-By-Dates SMF file.The file returns for all fields and tickers an #N/A error.
I'm using Excel 2007. Other details: Stock Market Functions add-in, Version 2.1.2017.05.03 (C:\SMF Add-IN; Windows (32-bit) NT 6.02; 12.0; ; ; 31)
I've attached a screenshot of the errors.
Could you please advise on what to do?Many thanks in advance!
#yiv5099489107 #yiv5099489107 -- #yiv5099489107ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv5099489107 #yiv5099489107ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv5099489107 #yiv5099489107ygrp-mkp #yiv5099489107hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv5099489107 #yiv5099489107ygrp-mkp #yiv5099489107ads {margin-bottom:10px;}#yiv5099489107 #yiv5099489107ygrp-mkp .yiv5099489107ad {padding:0 0;}#yiv5099489107 #yiv5099489107ygrp-mkp .yiv5099489107ad p {margin:0;}#yiv5099489107 #yiv5099489107ygrp-mkp .yiv5099489107ad a {color:#0000ff;text-decoration:none;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ygrp-lc {font-family:Arial;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ygrp-lc #yiv5099489107hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ygrp-lc .yiv5099489107ad {margin-bottom:10px;padding:0 0;}#yiv5099489107 #yiv5099489107actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv5099489107 #yiv5099489107activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv5099489107 #yiv5099489107activity span {font-weight:700;}#yiv5099489107 #yiv5099489107activity span:first-child {text-transform:uppercase;}#yiv5099489107 #yiv5099489107activity span a {color:#5085b6;text-decoration:none;}#yiv5099489107 #yiv5099489107activity span span {color:#ff7900;}#yiv5099489107 #yiv5099489107activity span .yiv5099489107underline {text-decoration:underline;}#yiv5099489107 .yiv5099489107attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv5099489107 .yiv5099489107attach div a {text-decoration:none;}#yiv5099489107 .yiv5099489107attach img {border:none;padding-right:5px;}#yiv5099489107 .yiv5099489107attach label {display:block;margin-bottom:5px;}#yiv5099489107 .yiv5099489107attach label a {text-decoration:none;}#yiv5099489107 blockquote {margin:0 0 0 4px;}#yiv5099489107 .yiv5099489107bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv5099489107 .yiv5099489107bold a {text-decoration:none;}#yiv5099489107 dd.yiv5099489107last p a {font-family:Verdana;font-weight:700;}#yiv5099489107 dd.yiv5099489107last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv5099489107 dd.yiv5099489107last p span.yiv5099489107yshortcuts {margin-right:0;}#yiv5099489107 div.yiv5099489107attach-table div div a {text-decoration:none;}#yiv5099489107 div.yiv5099489107attach-table {width:400px;}#yiv5099489107 div.yiv5099489107file-title a, #yiv5099489107 div.yiv5099489107file-title a:active, #yiv5099489107 div.yiv5099489107file-title a:hover, #yiv5099489107 div.yiv5099489107file-title a:visited {text-decoration:none;}#yiv5099489107 div.yiv5099489107photo-title a, #yiv5099489107 div.yiv5099489107photo-title a:active, #yiv5099489107 div.yiv5099489107photo-title a:hover, #yiv5099489107 div.yiv5099489107photo-title a:visited {text-decoration:none;}#yiv5099489107 div#yiv5099489107ygrp-mlmsg #yiv5099489107ygrp-msg p a span.yiv5099489107yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv5099489107 .yiv5099489107green {color:#628c2a;}#yiv5099489107 .yiv5099489107MsoNormal {margin:0 0 0 0;}#yiv5099489107 o {font-size:0;}#yiv5099489107 #yiv5099489107photos div {float:left;width:72px;}#yiv5099489107 #yiv5099489107photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv5099489107 #yiv5099489107photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv5099489107 #yiv5099489107reco-category {font-size:77%;}#yiv5099489107 #yiv5099489107reco-desc {font-size:77%;}#yiv5099489107 .yiv5099489107replbq {margin:4px;}#yiv5099489107 #yiv5099489107ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv5099489107 #yiv5099489107ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv5099489107 #yiv5099489107ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv5099489107 #yiv5099489107ygrp-mlmsg select, #yiv5099489107 input, #yiv5099489107 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv5099489107 #yiv5099489107ygrp-mlmsg pre, #yiv5099489107 code {font:115% monospace;}#yiv5099489107 #yiv5099489107ygrp-mlmsg * {line-height:1.22em;}#yiv5099489107 #yiv5099489107ygrp-mlmsg #yiv5099489107logo {padding-bottom:10px;}#yiv5099489107 #yiv5099489107ygrp-msg p a {font-family:Verdana;}#yiv5099489107 #yiv5099489107ygrp-msg p#yiv5099489107attach-count span {color:#1E66AE;font-weight:700;}#yiv5099489107 #yiv5099489107ygrp-reco #yiv5099489107reco-head {color:#ff7900;font-weight:700;}#yiv5099489107 #yiv5099489107ygrp-reco {margin-bottom:20px;padding:0px;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ov li a {font-size:130%;text-decoration:none;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv5099489107 #yiv5099489107ygrp-sponsor #yiv5099489107ov ul {margin:0;padding:0 0 0 8px;}#yiv5099489107 #yiv5099489107ygrp-text {font-family:Georgia;}#yiv5099489107 #yiv5099489107ygrp-text p {margin:0 0 1em 0;}#yiv5099489107 #yiv5099489107ygrp-text tt {font-size:120%;}#yiv5099489107 #yiv5099489107ygrp-vital ul li:last-child {border-right:none !important;}#yiv5099489107
Sent from Yahoo Mail. Get the app
On Tuesday, May 23, 2017 11:43 PM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
You'll need the experimental beta version of the add-in for smfPricesByDates() to work...
See:
https://groups.yahoo.com/neo/ groups/smf_addin/ conversations/messages/32112
https://groups.yahoo.com/neo/ groups/smf_addin/ conversations/messages/32114
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32237
On Tue, May 23, 2017 at 11:16 AM, Rico Teikotte ricoteikotte@...wrote:
I've a problem with the smfPricesByDates
I'm using Excel 2007. Other details: Stock Market Functions add-in, Version 2.1.2017.05.
I've attached a screenshot of the errors.
Could you please advise on what to do?Many thanks in advance!
#yiv5099489107 #yiv5099489107 -- #yiv5099489107ygrp-
Thu May 25, 2017 12:35 pm (PDT) . Posted by:
petekoch
FWIW, I'm also using Excel 2007 and RCHGetYahooHistory and smfPricesByDates both work just fine with the Beta version.
Many thanks to "The Amazing Randy" for his tireless efforts.....
Many thanks to "The Amazing Randy" for his tireless efforts.....
Thu May 25, 2017 7:16 am (PDT) . Posted by:
"Paul Austin" jpaulaustin
Do we know if the Yahoo Adjusted Close will ever be corrected again?
Right now on each time frame it just shows the adjusted close the same as
the close and no longer accounts for any dividends.
Right now on each time frame it just shows the adjusted close the same as
the close and no longer accounts for any dividends.
Thu May 25, 2017 8:23 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Supposedly, Yahoo is working on getting prices adjusted for dividends. For
the new smfGetYahooHistory() function, I am computing the adjustments
myself, but the request needs to be daily data ending with the most recent
date for the calculations to be accurate. I can unplug my processing if
they end up doing it.
On Thu, May 25, 2017 at 7:16 AM, Paul Austin paul@
...wrote:
>
> Do we know if the Yahoo Adjusted Close will ever be corrected again?
> Right now on each time frame it just shows the adjusted close the same as
> the close and no longer accounts for any dividends.
>
the new smfGetYahooHistory(
myself, but the request needs to be daily data ending with the most recent
date for the calculations to be accurate. I can unplug my processing if
they end up doing it.
On Thu, May 25, 2017 at 7:16 AM, Paul Austin paul@
...wrote:
>
> Do we know if the Yahoo Adjusted Close will ever be corrected again?
> Right now on each time frame it just shows the adjusted close the same as
> the close and no longer accounts for any dividends.
>
Thu May 25, 2017 5:51 pm (PDT) . Posted by:
ridgebacksexcel
Randy,
I've been following all the changes lately. I also have the latest beta version of 5/25/17 I believe.
One parameter I can't seem to figure out is finding the highest close for a single stock between dates.
I used to use RCHGetYahooHistory for this.
I don't think it works with the new smfGetYahooHistory function.
How would I find the highest close for lets say:
KSS
Between dates 5/9/17 and 5/25/17.
The result should be: $40.32 on 5/10/17.
It could be the adjusted close number.
Thanks.
I've been following all the changes lately. I also have the latest beta version of 5/25/17 I believe.
One parameter I can't seem to figure out is finding the highest close for a single stock between dates.
I used to use RCHGetYahooHistory for this.
I don't think it works with the new smfGetYahooHistory function.
How would I find the highest close for lets say:
KSS
Between dates 5/9/17 and 5/25/17.
The result should be: $40.32 on 5/10/17.
It could be the adjusted close number.
Thanks.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar