Senin, 22 Mei 2017

[smf_addin] Digest Number 4023

15 Messages

Digest #4023
3a
Re: smfGetTableCell help by "Marco Deen" marco.deen
4c
Re: Replace RCHGetYahooHistory call by "Randy Harmelink" rharmelink

Messages

Sun May 21, 2017 9:58 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

​I just uploaded the 2017-05​-21 *beta* version of the XLA file:

http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_Market_Functions.xla

Cumulative changes since the last official release:
​
*Date* *Routine* *Description*
2017-05-21 smfGetYahooHistory Change null values to zeroes and then forward
fill zeroes as possible
2017-05-21 smfGetYahooHistory Change default starting date to "1/1/1970";
2017-05-21 smfPricesByDates Allow input range to contain dates in string
format in addition to EXCEL serial dates
2017-05-20 RCHGetElementNumber BarChart Market Momentum -- new elements
#1115 thru #1138, reinstated #1146 thru #1178, on smf-elements-9.txt
2017-05-20 smfGetYahooHistory Add default array size if used from VBA call
2017-05-19 RCHGetElementNumber Use new smf-elements-22.txt file to define
"constants" to use in element definitions, to get around the 255-byte limit
of EXCEL's Eval() function
2017-05-19 smfRSITargetPrices
smfPricesBetween Change functions to use the new smfGetYahooHistory()
function
2017-05-18 smfGetYahooHistory Add new function to read Yahoo Historical
Quotes JSON data, replaces RCHGetYahooHistory()
2017-05-17 smfDate2Unix
smfUnix2Date Add two new utility functions for UNIX date conversioms
2017-05-16 smfGetYahooJSONField Add "portfolioView" and "headlineNews" as
fake modules for special processing
2017-05-05 Various Use new smf-elements-21.txt as a "configuration" file to
set initial default settings ​

Mon May 22, 2017 1:39 am (PDT) . Posted by:

"Paul Austin" jpaulaustin

Randy,
I tried to open my spreadsheet this morning and nothing shows up. It also
seems you've been pretty busy over the weekend!

Is RCHGetYahooHistory no longer valid?
Can I just do find/replace with SMFGetYahooHistory to update?

Thanks! Paul

On Mon, May 22, 2017 at 12:36 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> ​I just uploaded the 2017-05​-21 *beta* version of the XLA file:
>
> http://ogres-crypt.com/SMF/Works-In-Progress/RCH_Stock_
> Market_Functions.xla
>
> Cumulative changes since the last official release:
> ​
> *Date* *Routine* *Description*
> 2017-05-21 smfGetYahooHistory Change null values to zeroes and then
> forward fill zeroes as possible
> 2017-05-21 smfGetYahooHistory Change default starting date to "1/1/1970";
> 2017-05-21 smfPricesByDates Allow input range to contain dates in string
> format in addition to EXCEL serial dates
> 2017-05-20 RCHGetElementNumber BarChart Market Momentum -- new elements
> #1115 thru #1138, reinstated #1146 thru #1178, on smf-elements-9.txt
> 2017-05-20 smfGetYahooHistory Add default array size if used from VBA
> call
> 2017-05-19 RCHGetElementNumber Use new smf-elements-22.txt file to define
> "constants&quot; to use in element definitions, to get around the 255-byte limit
> of EXCEL's Eval() function
> 2017-05-19 smfRSITargetPrices
> smfPricesBetween Change functions to use the new smfGetYahooHistory()
> function
> 2017-05-18 smfGetYahooHistory Add new function to read Yahoo Historical
> Quotes JSON data, replaces RCHGetYahooHistory()
> 2017-05-17 smfDate2Unix
> smfUnix2Date Add two new utility functions for UNIX date conversioms
> 2017-05-16 smfGetYahooJSONField Add "portfolioView" and "headlineNews&quot; as
> fake modules for special processing
> 2017-05-05 Various Use new smf-elements-21.txt as a "configuration" file
> to set initial default settings ​
>
>
>

Mon May 22, 2017 2:22 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 a bit, so you can't just replace the function
name. And there may be changes to the parameters before it becomes official.

At this point, RCHGetYahooHistory() is done. The CSV file that fed it is no
longer available to the add-in.

On Mon, May 22, 2017 at 1:31 AM, Paul Austin paul@
​...wrote:

>
> I tried to open my spreadsheet this morning and nothing shows up. It also
> seems you've been pretty busy over the weekend!
>
> Is RCHGetYahooHistory no longer valid?
> Can I just do find/replace with SMFGetYahooHistory to update?
>
>
>

Mon May 22, 2017 2:49 am (PDT) . Posted by:

summit15

I'm in the same boat. I know that yahoo is messing with things and I've downloaded the recent beta you have posted. I'm just getting blanks in my spreadsheet. My question is is it still not working or am I doing something wrong. I'm happy to sit back and be patient if I knew it weren't me.

Thanks for everything.


Lance

Mon May 22, 2017 2:57 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You were just getting blanks in your sheet from RCHGetYahooHistory()? Or
you are getting blanks in your sheet from smfGetYahooHistory()?

If the latter, what does your formula look like? Are you sure you have the
new version up and working? Check with:

=RCHGetElementNumber("Version&quot;)

On Mon, May 22, 2017 at 2:28 AM, summit15@
​...wrote:

>
> I'm in the same boat. I know that yahoo is messing with things and I've
> downloaded the recent beta you have posted. I'm just getting blanks in my
> spreadsheet. My question is is it still not working or am I doing
> something wrong. I'm happy to sit back and be patient if I knew it weren't
> me.
>
>
>

Mon May 22, 2017 3:13 am (PDT) . Posted by:

"Paul Austin" jpaulaustin

I've now realized that the new Yahoo data doesn't properly compute the
"Adjusted Close." The "Adjusted Close" on the website (and of course
therefore on the download) doesn't work anymore. I hope this is temporary.
Did you notice the same?

On Mon, May 22, 2017 at 5:16 AM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:

>
>
> 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 a bit, so you can't just replace the function
> name. And there may be changes to the parameters before it becomes official.
>
> At this point, RCHGetYahooHistory() is done. The CSV file that fed it is
> no longer available to the add-in.
>
> On Mon, May 22, 2017 at 1:31 AM, Paul Austin paul@
> ​...wrote:
>
>>
>> I tried to open my spreadsheet this morning and nothing shows up. It also
>> seems you've been pretty busy over the weekend!
>>
>> Is RCHGetYahooHistory no longer valid?
>> Can I just do find/replace with SMFGetYahooHistory to update?
>>
>>
>>
>

Mon May 22, 2017 6:02 am (PDT) . Posted by:

zacharycal

Good Morning,

I am getting blanks from =RCHGetYahooHistory(). I am having trouble following the thread, but I think you said this is dead moving forward?


If so, what is the correct way to use smfGetYahooHistory()?


Thanks!

Zach







---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

You were just getting blanks in your sheet from RCHGetYahooHistory()? Or you are getting blanks in your sheet from smfGetYahooHistory()?


If the latter, what does your formula look like? Are you sure you have the new version up and working? Check with:


=RCHGetElementNumber("Version&quot;)


On Mon, May 22, 2017 at 2:28 AM, summit15@ ​...wrote:


I'm in the same boat. I know that yahoo is messing with things and I've downloaded the recent beta you have posted. I'm just getting blanks in my spreadsheet. My question is is it still not working or am I doing something wrong. I'm happy to sit back and be patient if I knew it weren't me.









Mon May 22, 2017 9:40 am (PDT) . Posted by:

zacharycal

Nevermind. I have read through everything. Thanks!

Mon May 22, 2017 9:46 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

The smfGetYahooHistory() function is computing the dividend adjustments.
For example, on MMM:

*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2017-05-18 $194.5000 $195.8200 $193.5400 $195.2400 1929000 $195.2400 1.00000
1.00000
2017-05-17 $196.7400 $196.7400 $194.7200 $194.8400 1881800 $194.8400 1.00000
1.00000 $1.1750
2017-05-16 $196.3075 $196.7648 $195.6912 $196.7350 1183600 $197.9100 0.99406
1.00000
2017-05-15 $195.5024 $196.5660 $195.0650 $196.4567 1474500 $197.6300 0.99406
1.00000
​​
​As is, they are only correctly applied on daily data that ends on the most
recent date.​

On Mon, May 22, 2017 at 2:43 AM, Paul Austin paul@austinco.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> I've now realized that the new Yahoo data doesn't properly compute the
> "Adjusted Close." The "Adjusted Close" on the website (and of course
> therefore on the download) doesn't work anymore. I hope this is temporary.
> Did you notice the same?
>
> On Mon, May 22, 2017 at 5:16 AM, Randy Harmelink rharmelink@gmail.com
> [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>>
>>
>> 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 a bit, so you can't just replace the function
>> name. And there may be changes to the parameters before it becomes official.
>>
>> At this point, RCHGetYahooHistory() is done. The CSV file that fed it is
>> no longer available to the add-in.
>>
>> On Mon, May 22, 2017 at 1:31 AM, Paul Austin paul@
>> ​...wrote:
>>
>>>
>>> I tried to open my spreadsheet this morning and nothing shows up. It
>>> also seems you've been pretty busy over the weekend!
>>>
>>> Is RCHGetYahooHistory no longer valid?
>>> Can I just do find/replace with SMFGetYahooHistory to update?
>>>
>>>
>>>

Mon May 22, 2017 12:55 am (PDT) . Posted by:

bizmark_ee

Here is the specific topic that appears to be highest ranked for now (45th):

https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa https://yahoo.uservoice.com/forums/382977-finance/suggestions/18220969--closing-price-adjusted-closing-price-are-the-sa

Please vote and pass it on..

Mon May 22, 2017 1:15 am (PDT) . Posted by:

"Marco Deen" marco.deen

Got it, thanks!
On May 21, 2017 11:40 PM, "marco.deen@gmail.com [smf_addin]" <
smf_addin@yahoogroups.com> wrote:

>
>
> Randy,
>
>
> I would like to pull swap rates into excel from this page
>
>
> http://www.myfxbook.com/forex-broker-swaps/ic-markets/312
>
>
> I did manage to pull the data into your "Quickwebpage Examination" file
> and can refer to that file, but I'm wondering how to construct the
> Gettablecell formula.
>
>
> I tried
>
> =RCHGetTableCell("http://www.myfxbook.com/forex-broker-
> swaps/ic-markets/312",3,>Symbol,,,1,</table,,"--") to pull the short swap
> for AUDCAD but get a message that the formula is incorrect. Can you start
> me up?
>
>
>
>
>
>
>
>

Mon May 22, 2017 9:19 am (PDT) . Posted by:

"Yahoo!" lewglenn

How do I do that with the smfGetHYahooHistory function? The 32114 note mentions the pRows and pCols parameters but doesn't indicate where in the argument string they go. At the end?
Thanks.

On Sunday, May 21, 2017, 6:11:13 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote: 

With your version of the function, you still need to supply the pRows and pCols parameters.

On Sun, May 21, 2017 at 5:14 PM, Yahoo! lewglenn@​...wrote:

I'm still getting the type mismatch error with:
Range("A1:B20000") = smfGetYahooHistory("TLT", "1/1/1970", , "d", "dc", 0)

Where could that be coming from?
On Sunday, May 21, 2017, 4:41:33 PM PDT, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote: 

The "pRows" and "pCols" parameters of smfGetYahooHistory() are the equivalent of the "pDim1" and "pDim2" parameters you were using with RCHGetYahooHistory(). In your version of smfGetYahooHistory(), you need to tell the function how many rows and columns of data to return, because they are otherwise set to the size of the range you are array-entering the formula over. And you aren't array-entering the formula over a range if you are calling it from VBA.

Your new code will not contain the date field. I think you want "dc" instead of "c". Also, you will default to a year's worth of data without specifying the date parameters. Just shove a "1/1/1970"; in as your starting date, and you should get all available data. I just hope TLT isn't one of those with "null" values, which will cause it to error out when it hits one.

I am changing smfGetYahooHistory() so that it will default to the "1/1/1970"; beginning date, as well as set the default # of rows and columns for a VBA call based on the starting date and ending date it ends up asking for from Yaho..

In my version of the function, this returns dates and adjusted closing prices back to 7/30/02, without a header line:

Range("A1:B20000") = smfGetYahooHistory("TLT", , , "d", "dc", 0)

#yiv1603248440 #yiv1603248440 -- #yiv1603248440ygrp-mkp {border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1603248440 #yiv1603248440ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1603248440 #yiv1603248440ygrp-mkp #yiv1603248440hd {color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}#yiv1603248440 #yiv1603248440ygrp-mkp #yiv1603248440ads {margin-bottom:10px;}#yiv1603248440 #yiv1603248440ygrp-mkp .yiv1603248440ad {padding:0 0;}#yiv1603248440 #yiv1603248440ygrp-mkp .yiv1603248440ad p {margin:0;}#yiv1603248440 #yiv1603248440ygrp-mkp .yiv1603248440ad a {color:#0000ff;text-decoration:none;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ygrp-lc {font-family:Arial;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ygrp-lc #yiv1603248440hd {margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ygrp-lc .yiv1603248440ad {margin-bottom:10px;padding:0 0;}#yiv1603248440 #yiv1603248440actions {font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1603248440 #yiv1603248440activity {background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1603248440 #yiv1603248440activity span {font-weight:700;}#yiv1603248440 #yiv1603248440activity span:first-child {text-transform:uppercase;}#yiv1603248440 #yiv1603248440activity span a {color:#5085b6;text-decoration:none;}#yiv1603248440 #yiv1603248440activity span span {color:#ff7900;}#yiv1603248440 #yiv1603248440activity span .yiv1603248440underline {text-decoration:underline;}#yiv1603248440 .yiv1603248440attach {clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}#yiv1603248440 .yiv1603248440attach div a {text-decoration:none;}#yiv1603248440 .yiv1603248440attach img {border:none;padding-right:5px;}#yiv1603248440 .yiv1603248440attach label {display:block;margin-bottom:5px;}#yiv1603248440 .yiv1603248440attach label a {text-decoration:none;}#yiv1603248440 blockquote {margin:0 0 0 4px;}#yiv1603248440 .yiv1603248440bold {font-family:Arial;font-size:13px;font-weight:700;}#yiv1603248440 .yiv1603248440bold a {text-decoration:none;}#yiv1603248440 dd.yiv1603248440last p a {font-family:Verdana;font-weight:700;}#yiv1603248440 dd.yiv1603248440last p span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1603248440 dd.yiv1603248440last p span.yiv1603248440yshortcuts {margin-right:0;}#yiv1603248440 div.yiv1603248440attach-table div div a {text-decoration:none;}#yiv1603248440 div.yiv1603248440attach-table {width:400px;}#yiv1603248440 div.yiv1603248440file-title a, #yiv1603248440 div.yiv1603248440file-title a:active, #yiv1603248440 div.yiv1603248440file-title a:hover, #yiv1603248440 div.yiv1603248440file-title a:visited {text-decoration:none;}#yiv1603248440 div.yiv1603248440photo-title a, #yiv1603248440 div.yiv1603248440photo-title a:active, #yiv1603248440 div.yiv1603248440photo-title a:hover, #yiv1603248440 div.yiv1603248440photo-title a:visited {text-decoration:none;}#yiv1603248440 div#yiv1603248440ygrp-mlmsg #yiv1603248440ygrp-msg p a span.yiv1603248440yshortcuts {font-family:Verdana;font-size:10px;font-weight:normal;}#yiv1603248440 .yiv1603248440green {color:#628c2a;}#yiv1603248440 .yiv1603248440MsoNormal {margin:0 0 0 0;}#yiv1603248440 o {font-size:0;}#yiv1603248440 #yiv1603248440photos div {float:left;width:72px;}#yiv1603248440 #yiv1603248440photos div div {border:1px solid #666666;height:62px;overflow:hidden;width:62px;}#yiv1603248440 #yiv1603248440photos div label {color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}#yiv1603248440 #yiv1603248440reco-category {font-size:77%;}#yiv1603248440 #yiv1603248440reco-desc {font-size:77%;}#yiv1603248440 .yiv1603248440replbq {margin:4px;}#yiv1603248440 #yiv1603248440ygrp-actbar div a:first-child {margin-right:2px;padding-right:5px;}#yiv1603248440 #yiv1603248440ygrp-mlmsg {font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}#yiv1603248440 #yiv1603248440ygrp-mlmsg table {font-size:inherit;font:100%;}#yiv1603248440 #yiv1603248440ygrp-mlmsg select, #yiv1603248440 input, #yiv1603248440 textarea {font:99% Arial, Helvetica, clean, sans-serif;}#yiv1603248440 #yiv1603248440ygrp-mlmsg pre, #yiv1603248440 code {font:115% monospace;}#yiv1603248440 #yiv1603248440ygrp-mlmsg * {line-height:1.22em;}#yiv1603248440 #yiv1603248440ygrp-mlmsg #yiv1603248440logo {padding-bottom:10px;}#yiv1603248440 #yiv1603248440ygrp-msg p a {font-family:Verdana;}#yiv1603248440 #yiv1603248440ygrp-msg p#yiv1603248440attach-count span {color:#1E66AE;font-weight:700;}#yiv1603248440 #yiv1603248440ygrp-reco #yiv1603248440reco-head {color:#ff7900;font-weight:700;}#yiv1603248440 #yiv1603248440ygrp-reco {margin-bottom:20px;padding:0px;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ov li a {font-size:130%;text-decoration:none;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ov li {font-size:77%;list-style-type:square;padding:6px 0;}#yiv1603248440 #yiv1603248440ygrp-sponsor #yiv1603248440ov ul {margin:0;padding:0 0 0 8px;}#yiv1603248440 #yiv1603248440ygrp-text {font-family:Georgia;}#yiv1603248440 #yiv1603248440ygrp-text p {margin:0 0 1em 0;}#yiv1603248440 #yiv1603248440ygrp-text tt {font-size:120%;}#yiv1603248440 #yiv1603248440ygrp-vital ul li:last-child {border-right:none !important;}#yiv1603248440

Mon May 22, 2017 9:36 am (PDT) . Posted by:

lewglenn

How do I do that with the smfGetHYahooHistory function? The 32114 note mentions the pRows and pCols parameters but doesn't indicate where in the argument string they go. At the end?


Thanks

Mon May 22, 2017 9:57 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

EXCEL should show you parameter placement as you enter the formula in VBA.
At the moment, they are the last two parameters.

However, if you grab the 5/21 version of the beta release, you shouldn't
need them (or need to specify that starting date).

On Mon, May 22, 2017 at 9:19 AM, Yahoo! lewglenn@
​...wrote:

>
>
> How do I do that with the smfGetHYahooHistory function? The 32114 note
> mentions the pRows and pCols parameters but doesn't indicate where in the
> argument string they go. At the end?
>
>
>

Mon May 22, 2017 10:11 am (PDT) . Posted by:

lewglenn

What's the url for the 5/21 version?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 10.11

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)