Sabtu, 17 Maret 2018

[smf_addin] Digest Number 4316

15 Messages

Digest #4316

Messages

Fri Mar 16, 2018 4:12 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sigh. It's impossible for one invocation to return #NAME? and another to
return a blank. If one function usage returns #NAME?, they all should.
#NAME? means the function isn't being found. But a blank return would mean
it was found.

In any case, you're not getting a price because the URL you are using
doesn't exist. That URL displays this web page:

https://www.theglobeandmail.com/investing/markets/funds/

So, the smfGetTagContent() function is returning "Error" because of a
missing web page and then the smfStrExtr() extracts nothing, so
smfConvertData() returns nothing.

On Fri, Mar 16, 2018 at 3:56 PM, cschwartz@
​...
wrote:

>
> Here you go:
>
> =RCHGetElementNumber("Version") gets me:
> Stock Market Functions add-iin Version 2.1.2018.01.24
> (C:\Users\nugge\Documents\SMF; Windows (64-bit) NT 10.00; 16.0; ; ; 2)
>
> =smfConvertData("1B") gets me: #NAME?
>
> Formula being used:
> =smfConvertData(smfStrExtr(
> ​​
> smfGetTagContent("https://www.theglobeandmail.com/globe-investo
> r/funds-and-etfs/funds/summary/?id=93852","p",-1,"Fund price:"),":","("))
>
> Which returns a blank cell
>
>

Fri Mar 16, 2018 4:25 pm (PDT) . Posted by:

stumpy_chris

What I provided is what populates - both cases - the #NAME? and the blank.

I realize now that the blank is cause by the new web address (which must have recently changed).


Do you have any suggestions as to how to use the address to pull the fund prices.

Thanks.

Fri Mar 16, 2018 4:44 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Why wouldn't you just use smfGetYahooPortfolioView()?

Otherwise, I would need a sample URL. It looks like it was extracting from
JSON or script code before.

On Fri, Mar 16, 2018 at 4:25 PM, cschwartz@
​...
wrote:

>
> What I provided is what populates - both cases - the #NAME? and the blank.
>
> I realize now that the blank is cause by the new web address (which must
> have recently changed).
>
> Do you have any suggestions as to how to use the address to pull the fund
> prices.
>
>
>

Fri Mar 16, 2018 5:41 pm (PDT) . Posted by:

stumpy_chris

Stupid question. How do I pull mutual fund quotes with the Portfolio View.

I know how to do it for a stock quote.

Fri Mar 16, 2018 5:46 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Just use the mutual fund ticker symbol instead of a stock ticker symbol.

On Fri, Mar 16, 2018 at 5:41 PM, cschwartz@
​...
wrote:

>
> Stupid question. How do I pull mutual fund quotes with the Portfolio View.
>
> I know how to do it for a stock quote.
>
>

Fri Mar 16, 2018 5:56 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

How can you expect me to be of any help if you don't show me what you're
trying?

Did you try the template? You'll probably need to use the smfFixLinks macro
since you have a non-standard add-in location.

On Fri, Mar 16, 2018 at 5:53 PM, cschwartz@
​...
wrote:

>
> Now I am getting Invalid items parameter
>
>

Fri Mar 16, 2018 6:10 pm (PDT) . Posted by:

stumpy_chris

Not sure what to say. I use the formula, put in the parameters and any number I put in (#2, #15, etc) sends me back the Invalid Items Parameter.

Fri Mar 16, 2018 6:18 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Why didn't you just post the formula you used?

From the documentation:

*An optional list of field number codes indicating which fields are
desired. Each field number code is a 2-byte string ranging from "00" to
"91". This parameter can either be a string concatenation of all desired
field number codes, a cell reference, or a range reference (which can be
numbers instead of 2-byte strings). See the example template
<http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls> for
a list of the available field number codes. The default value for this
parameter is to display all 91 available fields. *

​You can't just use a number.​ But you could use "0215".

On Fri, Mar 16, 2018 at 6:10 PM, cschwartz@
​...
wrote:

>
> Not sure what to say. I use the formula, put in the parameters and any
> number I put in (#2, #15, etc) sends me back the Invalid Items Parameter.
>

Fri Mar 16, 2018 6:23 pm (PDT) . Posted by:

stumpy_chris

=smfGetYahooPortfolioView($T$14,$T$12,,1)


T14 is the symbol
T12 is the number - I have 15 in for the quote

Fri Mar 16, 2018 6:32 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The only time numbers can be used is for a range of cells. An individual
cell reference would still require a 2-byte string.

But DO NOT use this function for a number of individual quotes. If you are
getting a number of quotes, use the array-entered version of the function
to get all of the requested data in one Internet access. I don't want Yahoo
to unplug this data source because it's being overused.

You'll need it to be array-entered anyway, if you want headers as you are
requesting.

On Fri, Mar 16, 2018 at 6:23 PM, cschwartz@
​...
wrote:

>
> =smfGetYahooPortfolioView($T$14,$T$12,,1)
>
> T14 is the symbol
> T12 is the number - I have 15 in for the quote
>
>

Sat Mar 17, 2018 3:42 am (PDT) . Posted by:

stumpy_chris

I will ask this just to check. Can I still use this function to pull mutual fund quotes from Globe Investor.


This issue is the link I had been using, but the website address has changed (which I can fix)


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


New Link for this particular fund: https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/ https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/


What I am wondering is what do I put in to pull the quote (ie: the portion of the string that shows "p",-1 "Fund Price:",".",(")).


Thanks.


Sat Mar 17, 2018 5:05 am (PDT) . Posted by:

"Higrm" higrm

Randy has the patience of a saint.
Try this equation in one of your cells, assuming you can get the application to work:
=LEFT(RCHGetTableCell("https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/",1,"Period open:"),FIND(" ",RCHGetTableCell("https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/",1,"Period open:")))
I'm sure Randy would/will propose a nicer, shorter, cleaner version, but this works for me on this web page for this particular fund, without having all his innate wisdom.

The data you seek doesn't appear to be on Yahoo for this particular fund, but it is easy enough to find on several other web sites using the fund ID "FID669".  It may be a simpler matter to extract just the price you want on one of those.

There is a very, very useful template provided by Randy called SMF-Quick-Web Page-Examination, http://ogres-crypt.com/SMF/Templates/.  Find it.  Use it.  With it, I was able to come up with the above formula in a few minutes. Use the By Table Cell tab after you have looked into the web site's source code to find the line where the price you want can be found.  Then once you find the cell, clean it up with excel functionality.
This pulls in the correct table cell: =RCHGetTableCell("https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/",1,"Period open:")But the value contains some text after the price you are looking for, so I use the =Left(x,Find(" ",x)) to get rid of it.

Cheers,Higrm


On Saturday, March 17, 2018, 11:43:28 AM GMT+1, cschwartz@nf.sympatico.ca [smf_addin] <smf_addin@yahoogroups.com> wrote:

 

I will ask this just to check.   Can I still use this function to pull mutual fund quotes from Globe Investor.
This issue is the link I had been using, but the website address has changed (which I can fix)
=smfConvertData(smfStrExtr(smfGetTagContent("https://www.theglobeandmail.com/globe-investor/funds-and-etfs/funds/summary/?id=32005","p",-1,"Fund price:"),":","("))

New Link for this particular fund: https://www.theglobeandmail.com/investing/markets/funds/FID669.CF/
What I am wondering is what do I put in to pull the quote (ie: the portion of the string that shows "p",-1 "Fund Price:",".",(")).
Thanks.

Sat Mar 17, 2018 7:44 am (PDT) . Posted by:

stumpy_chris

Thank you for this. It works perfectly.

Thanks to all for the help. I should be good going forward (for now lol)


And yes Randy is a saint.

Sat Mar 17, 2018 6:37 am (PDT) . Posted by:

ajtalukdar

Hi Randy,


Just noticed that SMF add-in is not pulling Google Finance data since the past couple of days. Anyone else facing this issue. Thanks.


Abhijit Talukdar
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 07.44

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)