15 Messages
Digest #4214
1b
Re: SMF Add-in Announcements - New ZIP File -- Version 2017.11.04 by "Randy Harmelink" rharmelink
Messages
Mon Nov 6, 2017 10:53 am (PST) . Posted by:
lewglenn
In the smfGetYahooPortfolioView-Example.xls http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls workbook, on the Field Number tab, field number 78 is described as Last Traded Date but has the Yahoo Field Name regularMarketTime. I'm guessing this is a typo. When I use this field number I get the correct date.
However, field number 79 is described as Last Traded Time and it too has the Yahoo Field Name regularMarketTime. In this case, when I use this field number I get what appears to be an EXCEL serial time. Is that correct?
Also, if you're still contemplating allowing the same ticker symbol to be passed twice I'd like to cast a vote in favor of this change.
Thanks.
However, field number 79 is described as Last Traded Time and it too has the Yahoo Field Name regularMarketTime. In this case, when I use this field number I get what appears to be an EXCEL serial time. Is that correct?
Also, if you're still contemplating allowing the same ticker symbol to be passed twice I'd like to cast a vote in favor of this change.
Thanks.
Mon Nov 6, 2017 12:21 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Not a typo. The "regularMarketTime" is a field on the JSON file that
contains the last traded date and time in UNIX format. Field #78 just
converts that UNIX value into an EXCEL serial date and takes the integer
portion. Field #79 just converts that UNIX value into an EXCEL serial date
and take the fractional portion. Field #87 just converts that UNIX value
into an EXCEL serial date, keeping the whole result. So all four output
fields come from the same input JSON field. In retrospect, I could have
just gone with #87, as it can be used in place of #78 and #79, just with a
different format applied to the value. Oh well.
The ticker symbol can still be passed twice. Right now, it's only returned
once. If returned multiple times, there would need to be additional
processing to check all slots every time instead of stopping after the
first slot is found.
On Mon, Nov 6, 2017 at 11:53 AM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> In the smfGetYahooPortfolioView-Example.xls
> <http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls>
> workbook, on the Field Number tab, field number 78 is described as Last
> Traded Date but has the Yahoo Field Name regularMarketTime. I'm guessing
> this is a typo. When I use this field number I get the correct date.
>
> However, field number 79 is described as Last Traded Time and it too has
> the Yahoo Field Name regularMarketTime. In this case, when I use this
> field number I get what appears to be an EXCEL serial time. Is that correct?
>
> Also, if you're still contemplating allowing the same ticker symbol to be
> passed twice I'd like to cast a vote in favor of this change.
>
>
>
contains the last traded date and time in UNIX format. Field #78 just
converts that UNIX value into an EXCEL serial date and takes the integer
portion. Field #79 just converts that UNIX value into an EXCEL serial date
and take the fractional portion. Field #87 just converts that UNIX value
into an EXCEL serial date, keeping the whole result. So all four output
fields come from the same input JSON field. In retrospect, I could have
just gone with #87, as it can be used in place of #78 and #79, just with a
different format applied to the value. Oh well.
The ticker symbol can still be passed twice. Right now, it's only returned
once. If returned multiple times, there would need to be additional
processing to check all slots every time instead of stopping after the
first slot is found.
On Mon, Nov 6, 2017 at 11:53 AM, lewglenn@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> In the smfGetYahooPortfolioView-Example.xls
> <http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls>
> workbook, on the Field Number tab, field number 78 is described as Last
> Traded Date but has the Yahoo Field Name regularMarketTime. I'm guessing
> this is a typo. When I use this field number I get the correct date.
>
> However, field number 79 is described as Last Traded Time and it too has
> the Yahoo Field Name regularMarketTime. In this case, when I use this
> field number I get what appears to be an EXCEL serial time. Is that correct?
>
> Also, if you're still contemplating allowing the same ticker symbol to be
> passed twice I'd like to cast a vote in favor of this change.
>
>
>
Mon Nov 6, 2017 11:17 am (PST) . Posted by:
gpommeranz1
Good morning, I'm having an issue with the Company Name (item number 13863) of RCHGetElementNumber. I've attempted several different ways of entering the company ticker and the field will return either "Error" or #Value!. This seems like it should be straightforward syntax. The syntax that I'm using is =RCHGetElementNumber(MMM,13863). I've tried it with "MMM" and with a space after the comma. I'm stumped. Hopefully someone can point out my error.
Mon Nov 6, 2017 12:23 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
This is working here:
=RCHGetElementNumber("MMM",13863)
I did just change the definition, but it was a fix for companies that had
hyphens in their names:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html
On Mon, Nov 6, 2017 at 10:43 AM, gpommeranz1@
...
wrote:
>
> Good morning, I'm having an issue with the Company Name (item number
> 13863) of RCHGetElementNumber. I've attempted several different ways of
> entering the company ticker and the field will return either "Error" or
> #Value!. This seems like it should be straightforward syntax. The syntax
> that I'm using is =RCHGetElementNumber(MMM,13863). I've tried it with
> "MMM" and with a space after the comma. I'm stumped. Hopefully someone
> can point out my error.
>
=RCHGetElementNumber("MMM",13863)
I did just change the definition, but it was a fix for companies that had
hyphens in their names:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html
On Mon, Nov 6, 2017 at 10:43 AM, gpommeranz1@
...
wrote:
>
> Good morning, I'm having an issue with the Company Name (item number
> 13863) of RCHGetElementNumber
> entering the company ticker and the field will return either "Error" or
> #Value!. This seems like it should be straightforward syntax. The syntax
> that I'm using is =RCHGetElementNumbe
> "MMM" and with a space after the comma. I'm stumped. Hopefully someone
> can point out my error.
>
I've been using the following to get NAV for EFTs from MORNINGSTAR:
=smfConvertData(smfGetTagContent("http://quotes.morningstar.com/fund/c-header?&t="&C11, "span", 0, "vkey=""NAV"">"))
Would it be possible to get LAST PRICE using a similar command?
I noticed that NAV and LAST PRICE are in the same Morningstar screen.
I've tried replacing "NAV" with all sorts of anagrams of the words "LAST PRICE" with no success.
Is there a way to achieve that? If so, the issue with Yahoo Quotes would be solved.
Thanks
Armando
I noticed that NAV and LAST PRICE are in the same Morningstar screen.
=smfConvertData(smfGetTagContent("http://quotes.morningstar.com/fund/c-header?&t="&C11, "span"
Would it be possible to get LAST PRICE using a similar command?
I noticed that NAV and LAST PRICE are in the same Morningstar screen.
I've tried replacing "NAV" with all sorts of anagrams of the words "LAST PRICE" with no success.
Is there a way to achieve that? If so, the issue with Yahoo Quotes would be solved.
Thanks
Armando
I noticed that NAV and LAST PRICE are in the same Morningstar screen.
Mon Nov 6, 2017 11:43 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Thanks for the heads up. I removed the item from the FAQs page.
Not a clue on the Win10 crash. That workbook has had nearly a thousand
downloads and I know others have used it. I can't imagine why simply
clicking on a cell would do something weird in that workbook.
I assume you're using EXCEL 2007.
For current quotes, I think most people will convert to the
smfGetYahooPortfolioView() function. I've seen a number of comments on
people trying the Google method I mentioned on the blog.
On Mon, Nov 6, 2017 at 11:39 AM, trendsekr@
...
wrote:
>
> That's what I was looking for, thanks. However, I'm still not sure what to
> do next. I was looking around for files and instructions but...
>
> BTW, in http://ogres-crypt.com/SMF/Tips-and-FAQs/ the link for 0.1
> Installing under EXCEL 2007 is wrong.
>
> Also, when I clicked on a cell in smfGetYahooPortfolioView-Example,.xls
> <http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls> windows
> 10 crashed.
>
Not a clue on the Win10 crash. That workbook has had nearly a thousand
downloads and I know others have used it. I can't imagine why simply
clicking on a cell would do something weird in that workbook.
I assume you're using EXCEL 2007.
For current quotes, I think most people will convert to the
smfGetYahooPortfolioView() function. I've seen a number of comments on
people trying the Google method I mentioned on the blog.
On Mon, Nov 6, 2017 at 11:39 AM, trendsekr@
...
wrote:
>
> That's what I was looking for, thanks. However, I'm still not sure what to
> do next. I was looking around for files and instructions but...
>
> BTW, in http://ogres-crypt.com/SMF/Tips-and-FAQs/ the link for 0.1
> Installing under EXCEL 2007 is wrong.
>
> Also, when I clicked on a cell in smfGetYahooPortfolioView-Example,.xls
> <http://ogres-crypt.com/SMF/Templates/smfGetYahooPortfolioView-Example.xls> windows
> 10 crashed.
>
Mon Nov 6, 2017 11:52 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I don't see "Last Price" on that web page?
Both NAV and Last Price are on the "quote-banner" static page:
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?&t=SPY
","div",-1,"id=""lastPrice""",,,,1)
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?&t=SPY
","span",-1,"id=""NAV""",,,,1)
P.S. Please start a new topic on the Yahoo group when your message is a
completely different subject.
On Mon, Nov 6, 2017 at 12:27 PM, armando@
...
wrote:
>
> I've been using the following to get NAV for EFTs from MORNINGSTAR:
>
> =smfConvertData(smfGetTagContent("http://quotes.morningstar.com/fund/c-
> header?&t="&C11, "span", 0, "vkey=""NAV"">"))
>
> Would it be possible to get LAST PRICE using a similar command?
>
> I noticed that NAV and LAST PRICE are in the same Morningstar screen.
>
> I've tried replacing "NAV" with all sorts of anagrams of the words "LAST
> PRICE" with no success.
>
> Is there a way to achieve that? If so, the issue with Yahoo Quotes would
> be solved.
>
>
>
Both NAV and Last Price are on the "quote-banner" static page:
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?&t=SPY
","div",-1,"id=""lastPrice""",,,,1)
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?&t=SPY
","span",-1,"id=""NAV""",,,,1)
P.S. Please start a new topic on the Yahoo group when your message is a
completely different subject.
On Mon, Nov 6, 2017 at 12:27 PM, armando@
...
wrote:
>
> I've been using the following to get NAV for EFTs from MORNINGSTAR:
>
> =smfConvertData(smfGetTagContent("http://quotes.morningstar.com/fund/c-
> header?&t="
>
> Would it be possible to get LAST PRICE using a similar command?
>
> I noticed that NAV and LAST PRICE are in the same Morningstar screen.
>
> I've tried replacing "NAV" with all sorts of anagrams of the words "LAST
> PRICE" with no success.
>
> Is there a way to achieve that? If so, the issue with Yahoo Quotes would
> be solved.
>
>
>
Mon Nov 6, 2017 11:41 am (PST) . Posted by:
"Dennis Sesar" dennis@zis.com
Randy
I installed the new SML XLA and it still does not work. Is the syntax still the same ? I am trying to pull the current price of a security an example of one of my formula is =RCHGetYahooQuotes(C31,"l1") where c31 contains USB
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, November 1, 2017 2:54 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] =RCHGetYahooQuotes(C78,"l1")
See the announcements blog:
https://smf-add-in.blogspot.com/2017/11/alert-rchgetyahooquotes-problems.html
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Wed, Nov 1, 2017 at 2:36 PM, 'Dennis Sesar' dennis@zis.com <mailto:dennis@zis.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
I have been using the function in the subject field for a long time. Today it returns blank, C79 contains the ticker symbol and I am trying to get the closing price returned. Any Ideas?
I installed the new SML XLA and it still does not work. Is the syntax still the same ? I am trying to pull the current price of a security an example of one of my formula is =RCHGetYahooQuotes(C31,"l1") where c31 contains USB
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, November 1, 2017 2:54 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] =RCHGetYahooQuotes(C78,"l1")
See the announcements blog:
https://smf-add-in.blogspot.com/2017/11/alert-rchgetyahooquotes-problems.html
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171031.html
On Wed, Nov 1, 2017 at 2:36 PM, 'Dennis Sesar' dennis@zis.com <mailto:dennis@zis.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
I have been using the function in the subject field for a long time. Today it returns blank, C79 contains the ticker symbol and I am trying to get the closing price returned. Any Ideas?
Mon Nov 6, 2017 12:05 pm (PST) . Posted by:
mtopper
Randy
thank you
I would like to try th PortfolioView function as a replacement for GetYahooQuotes.
I assume it requires the newest version. I am currently at Stock Market Functions add-in, Version 2.1.2017.05.03
Does it also require a "Portfolio" on the Yahoo Finance Page?
thanks
Marty
thank you
I would like to try th PortfolioView function as a replacement for GetYahooQuotes.
I assume it requires the newest version. I am currently at Stock Market Functions add-in, Version 2.1.2017.05.
Does it also require a "Portfolio&quo
thanks
Marty
Mon Nov 6, 2017 12:25 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
RCHGetYahooQuotes() is now obsolete. Yahoo unplugged the feed for it on
10/31. See recent posts on the announcement blogs for updates and possible
replacements:
https://smf-add-in.blogspot.com/
On Mon, Nov 6, 2017 at 12:41 PM, 'Dennis Sesar' dennis@zis.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> *I installed the new SML XLA and it still does not work. Is the syntax
> still the same ? I am trying to pull the current price of a security an
> example of one of my formula is
> =RCHGetYahooQuotes(C31,"l1") where c31 contains *
>
10/31. See recent posts on the announcement blogs for updates and possible
replacements:
https://smf-add-in.blogspot.com/
On Mon, Nov 6, 2017 at 12:41 PM, 'Dennis Sesar' dennis@zis.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
>
> *I installed the new SML XLA and it still does not work. Is the syntax
> still the same ? I am trying to pull the current price of a security an
> example of one of my formula is
> =RCHGetYahooQuotes(
>
Mon Nov 6, 2017 12:29 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I would recommend updating to the new 2017.11.04 version of the add-in:
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html
smfGetYahooPortfolioView() no longer extracts data from the Yahoo Portfolio
web pages. In August, Yahoo starting building those web pages from a JSON
data file, so tables could no longer be extracted from the source code of
the web page.
The function now parses out the data in that JSON file.
On Mon, Nov 6, 2017 at 1:05 PM, mtopper@
...
wrote:
>
> I would like to try th PortfolioView function as a replacement for
> GetYahooQuotes.
>
> I assume it requires the newest version. I am currently at Stock Market
> Functions add-in, Version 2.1.2017.05.03
>
> Does it also require a "Portfolio" on the Yahoo Finance Page?
>
>
https://smf-add-in.blogspot.com/2017/11/new-zip-file-version-20171104.html
smfGetYahooPortfoli
web pages. In August, Yahoo starting building those web pages from a JSON
data file, so tables could no longer be extracted from the source code of
the web page.
The function now parses out the data in that JSON file.
On Mon, Nov 6, 2017 at 1:05 PM, mtopper@
...
wrote:
>
> I would like to try th PortfolioView function as a replacement for
> GetYahooQuotes.
>
> I assume it requires the newest version. I am currently at Stock Market
> Functions add-in, Version 2.1.2017.05.
>
> Does it also require a "Portfolio&quo
>
>
Mon Nov 6, 2017 1:02 pm (PST) . Posted by:
"Dennis Sesar" dennis@zis.com
Can this add-in be used to get the quotes?
<http://ogres-crypt.com/SMF/RCH_Stock_Market_Functions-2.1.2017.11.04.zip> RCH_Stock_Market_Functions-2.1.2017.11.04.zip
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, November 6, 2017 12:25 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] =RCHGetYahooQuotes(C78,"l1")
RCHGetYahooQuotes() is now obsolete. Yahoo unplugged the feed for it on 10/31. See recent posts on the announcement blogs for updates and possible replacements:
https://smf-add-in.blogspot.com/
On Mon, Nov 6, 2017 at 12:41 PM, 'Dennis Sesar' dennis@zis.com <mailto:dennis@zis.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
I installed the new SML XLA and it still does not work. Is the syntax still the same ? I am trying to pull the current price of a security an example of one of my formula is =RCHGetYahooQuotes(C31,"l1") where c31 contains
<http://ogres-crypt.com/SMF/RCH_Stock_Market_Functions-2.1.2017.11.04.zip> RCH_Stock_Market_Functions-2.1.2017.11.04.zip
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Monday, November 6, 2017 12:25 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] =RCHGetYahooQuotes(C78,"l1")
RCHGetYahooQuotes() is now obsolete. Yahoo unplugged the feed for it on 10/31. See recent posts on the announcement blogs for updates and possible replacements:
https://smf-add-in.blogspot.com/
On Mon, Nov 6, 2017 at 12:41 PM, 'Dennis Sesar' dennis@zis.com <mailto:dennis@zis.com> [smf_addin] <smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com> > wrote:
I installed the new SML XLA and it still does not work. Is the syntax still the same ? I am trying to pull the current price of a security an example of one of my formula is =RCHGetYahooQuotes(
Mon Nov 6, 2017 12:45 pm (PST) . Posted by:
johnross999
Is there an Excel keyboard shortcut that will refresh/enable a retrieval of data from the internet?
From some reason I am getting the same data until I reopen the workbook.
From some reason I am getting the same data until I reopen the workbook.
Mon Nov 6, 2017 12:48 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
As with most add-in functions, you need to run the smfForceRecalculation
macro. I have it assigned to a button on my Quick Access Toolbar.
On Mon, Nov 6, 2017 at 1:45 PM, johnrr9@
...
wrote:
>
> Is there an Excel keyboard shortcut that will refresh/enable a retrieval
> of data from the internet?
> From some reason I am getting the same data until I reopen the workbook.
>
>
>
macro. I have it assigned to a button on my Quick Access Toolbar.
On Mon, Nov 6, 2017 at 1:45 PM, johnrr9@
...
wrote:
>
> Is there an Excel keyboard shortcut that will refresh/enable a retrieval
> of data from the internet?
> From some reason I am getting the same data until I reopen the workbook.
>
>
>
Mon Nov 6, 2017 1:10 pm (PST) . Posted by:
eremon9
When I call smfGetPortfolioView(Tickers, Items) within VBA, the worksheet containing my array formula isn't updated, and the underlying webpage appears not to be refreshed. Is there a way to do this without having to click on your context menu item?
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar