[smf_addin] Digest Number 4029

EXCEL Stock Market Functions Add-in Group

5 Messages

Digest #4029

1a

Re: SMF Convert data by stumpy_chris

1b

Re: SMF Convert data by "Randy Harmelink" rharmelink

2a

Re: Another question about smfGetYahooHistory by "Randy Harmelink" rharmelink

3a

Re: Update on Yahoo Historical Quotes (as of 2017-05-21) by lexstar

3b

Re: Update on Yahoo Historical Quotes (as of 2017-05-21) by "Randy Harmelink" rharmelink

Messages

1a

Re: SMF Convert data

Tue May 23, 2017 5:30 pm (PDT) . Posted by:

stumpy_chris

Not sure about the version number, 2.1 (May 3, 2017).

Here is the string I have been using:


='C:\Users\Chris\Documents\SMF AddIns\RCH_Stock_Market_Functions.xla'!smfConvertData('C:\Users\Chris\Documents\SMF AddIns\RCH_Stock_Market_Functions.xla'!smfstrExtr('C:\Users\Chris\Documents\SMF AddIns\RCH_Stock_Market_Functions.xla'!smfGetTagContent("http://www.theglobeandmail.com/globe-investor/funds-and-etfs/funds/summary/?id=32005","p",-1,"Fund price:"),":","("))



I think this is what you want.


Thanks.

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

1b

Re: SMF Convert data

Tue May 23, 2017 6:22 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Your problem is location errors (the paths in front of the functions). You
must have moved your add-in since that workbook was last saved, as EXCEL
saves a hard-coded location of the add-in whenever a workbook is saved. It
appears that location is no longer valid, so EXCEL flags the formula as an
error, with unresolved links. You should have gotten a warning when you
opened up the workbook.

Just run the smfFixLinks macro and it will remove those paths and then
EXCEL will use the active functions it finds.

That said, you also need to change the "http://" to "https://". So:

=smfConvertData(smfStrExtr(smfGetTagContent("
https://www.theglobeandmail.com/globe-investor/funds-and-etfs/funds/summary/?id=32005","p",-1,"Fund
price:"),":","("))

On Tue, May 23, 2017 at 5:30 PM, cschwartz@
​...wrote:

>
> Not sure about the version number, 2.1 (May 3, 2017).
>
> Here is the string I have been using:
>
> =
> ​​
> 'C:\Users\Chris\Documents\SMF AddIns\RCH_Stock_Market_
> Functions.xla'!smfConvertData('C:\Users\Chris\Documents\SMF
> AddIns\RCH_Stock_Market_Functions.xla'!smfstrExtr('C:\Users\Chris\Documents\SMF
> AddIns\RCH_Stock_Market_Functions.xla'!smfGetTagContent("http://www.
> theglobeandmail.com/globe-investor/funds-and-etfs/funds/summary/?id=32005
> ","p",-1,"Fund price:"),":","("))
>
> I think this is what you want.
>
>
>
>
> Visit Your Group
> <https://groups.yahoo.com/neo/groups/smf_addin/info;_ylc=X3oDMTJmcHJsdDM5BF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDdnRsBHNsawN2Z2hwBHN0aW1lAzE0OTU1ODU4NDI->
>
> - New Members
> <https://groups.yahoo.com/neo/groups/smf_addin/members/all;_ylc=X3oDMTJnZGFkazluBF9TAzk3MzU5NzE0BGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDdnRsBHNsawN2bWJycwRzdGltZQMxNDk1NTg1ODQy>
> 15
>
> [image: Yahoo! Groups]
> <https://groups.yahoo.com/neo;_ylc=X3oDMTJlMXVrYjEzBF9TAzk3NDc2NTkwBGdycElkAzE4MDk0NjIwBGdycHNwSWQDMTcwNTYzMjE5OARzZWMDZnRyBHNsawNnZnAEc3RpbWUDMTQ5NTU4NTg0Mg-->
> • Privacy <https://info.yahoo.com/privacy/us/yahoo/groups/details.html> •
> Unsubscribe <smf_addin-unsubscribe@yahoogroups.com?subject=Unsubscribe> • Terms
> of Use <https://info.yahoo.com/legal/us/yahoo/utos/terms/>
>
>
>

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

2a

Re: Another question about smfGetYahooHistory

Tue May 23, 2017 6:16 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Say what?

Of course the DateValue() function won't work on the EXCEL serial dates.
That function is used to convert string value INTO EXCEL serial dates.

It would make no sense for the smfGetYahooHistory() function to convert
EXCEL serial dates into string dates, just so someone can convert them back
into EXCEL serial dates??

On Tue, May 23, 2017 at 4:53 PM, lewglenn@
​...wrote:

>
> I know you're up to your eyeballs at the moment but any chance on
> providing an option that converts EXCEL serial sates to the string values
> that RCHGetYahooHistory used to provide? I think most folks will not find
> the EXCEL serial dates to be particularly useful since the function
> DateValue in vba doesn't recognize the EXCEL serial dates in the argument.
> I realize it's possible to write a macro segment that does the conversion
> but it would be nice if the smfGetYahooHistory does it when it extracts the
> data.
>

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

3a

Re: Update on Yahoo Historical Quotes (as of 2017-05-21)

Tue May 23, 2017 6:16 pm (PDT) . Posted by:

lexstar

In using this:

=TRANSPOSE(smfGetYahooHistory($B5,C$4,D$4,"m","ohlc";,0,1))​



For example,


=TRANSPOSE(smfGetYahooHistory("MMM",1/1/2016,5/21/2017,"m","ohlc";,0,1))​


I'm only able to get 4 months worth of data. Everything after the 4 months return "#N/A"


184.08 190.20 194.67 197.33 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 173.04 186.97 190.73 194.34 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 186.11 192.35 195.95 198.72 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 171.43 186.90 187.50 192.39 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A

Something doesn't seem right.

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

3b

Re: Update on Yahoo Historical Quotes (as of 2017-05-21)

Tue May 23, 2017 6:41 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Ahh. You're right. Apparently, my test was too small of a date range, so it
fit.

The function determines the size of the returned array by the size of the
range it is array-entered, so it's returning 4 rows and 13 columns of data.

You'll need to add the "pRows,pCols" parameters. In this case "13,4",
because you want 13 rows and 4 columns of data, before the transpose. You
can make it larger if you like. It will blank fill the unused rows and
columns. The #N/A are a result of rows and columns that were never created
by the function.

BTW, your "for example" wouldn't work. 1/1/2016 means 1 divided by 1
divided by 2016, so it wouldn't be a date value. It would need to be:

=TRANSPOSE(smfGetYahooHistory("MMM","1/1/2016","5/21/2017","m","ohlc";,0,1,13,4))

Is it returning the dates you expect? You may want to include a "d" in your
output string, at least when testing. The "pResort" will only resort the
number of rows it has kept.

On Tue, May 23, 2017 at 6:15 PM, lexstar@
​...wrote:

>
> In using this:
>
> =TRANSPOSE(smfGetYahooHistory($B5,C$4,D$4,"m","ohlc";,0,1))​
>
> For example,
>
> =
> ​​
> TRANSPOSE(smfGetYahooHistory("MMM",1/1/2016,5/21/2017,"m","ohlc";,0,1))​
>
> I'm only able to get 4 months worth of data. Everything after the 4 months
> return "#N/A"
>
> 184.08 190.20 194.67 197.33 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
> 173.04 186.97 190.73 194.34 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
> 186.11 192.35 195.95 198.72 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
> 171.43 186.90 187.50 192.39 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A
>
> Something doesn't seem right.
>

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

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

Visit Your Group

• New Members 15

• Privacy • Unsubscribe • Terms of Use