15 Messages
Digest #4217
6b
Re: alternative to RCHgetYahooQuotes for Last Trade and Previous Clo by "Randy Harmelink" rharmelink
Messages
Mon Nov 6, 2017 6:53 pm (PST) . Posted by:
shane.kelly2222
Hi Randy,
Just a quick message to say thank you for all your hard work on this addin, and the new GetPortfolioView function. It is much appreciated. I feel a separate message is warranted to avoid the gratitude being lost amongst the flurry of recent messages.
Thanks
Shane
Just a quick message to say thank you for all your hard work on this addin, and the new GetPortfolioView function. It is much appreciated. I feel a separate message is warranted to avoid the gratitude being lost amongst the flurry of recent messages.
Thanks
Shane
Mon Nov 6, 2017 8:18 pm (PST) . Posted by:
johnross999
Took me a while to find this hidden macro :) but at the end I assigned a button as you suggested here: https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/7625 https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/7625 and all is working.
TX
TX
Mon Nov 6, 2017 9:24 pm (PST) . Posted by:
dleeper47
Possible solution to getting the portfolio view to refresh is to include a "ticker symbol" that is just the value of =NOW(). That function changes every time a sheet recalc is called for.
Of course there is no such ticker symbol, and the portfolio view matrix just returns '--' for every column. But it's enough to get an update of the full matrix as well as the spreadsheet. I then use a VLOOKUP function to read updates for my list of 20 or so ticker symbols.
This is extremely fast, and I only use do the sheet recalc manually a few times a day when I need an update of my spreadsheet. Hopefully this does not put much load on Yahoo servers (?) -- I'd hate to have portfolio view "obsoleted" now that I've got it working as a replacement to RCHGetYahooQuotes() !!
I've tried several solutions to managing my portfolio in Excel. Randy's add-ins have been the best for me.
Much appreciated these past several years, Randy!
Of course there is no such ticker symbol, and the portfolio view matrix just returns '--' for every column. But it's enough to get an update of the full matrix as well as the spreadsheet. I then use a VLOOKUP function to read updates for my list of 20 or so ticker symbols.
This is extremely fast, and I only use do the sheet recalc manually a few times a day when I need an update of my spreadsheet. Hopefully this does not put much load on Yahoo servers (?) -- I'd hate to have portfolio view "obsoleted&quo
I've tried several solutions to managing my portfolio in Excel. Randy's add-ins have been the best for me.
Much appreciated these past several years, Randy!
Tue Nov 7, 2017 7:16 am (PST) . Posted by:
"V. Shankar" tarakayan
Occasionally, I find the following issues. This is no big deal as I am able to overcome this, just thought i report:
- I use around 120 or so stock symbols, although smf matrix range covers 500 rows (C to AJ columns), in other words, 121 on wards no symbol in use and left blank
- once in a while, one or two symbols (tasr, algn or a mf symbol, it varies not symbol specific) returns error/#NA for that symbol
when I see this, I just reenter the symbol in say 121, repeating it. It then works fine...
- when I try to remove the duplicate symbols, the whole spreadsheet data vanishes...
As mentioned, this happens once in a while and the sheet performs well with my duplicate symbol entries whenever a symbol doesnt work.
Shankar
On Monday, November 6, 2017, 3:35:15 AM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
You'll need the 2017.11.04 version of the add-in to use the "51" field number.
On Sun, Nov 5, 2017 at 2:41 PM, u1704141@... wrote:
I transitioned over to using smfGetPortfolioView for Yahoo Quotes. =smfGetYahooPortfolioView(C9," 15") works fine pulling it current quote. However, I'm not having success using field "51" which I think is the previous close quote... =smfGetYahooPortfolioView(C9," 51"). Any thoughts on perhaps it's not pulling? The cell contains '-' when trying to execute that field..
- I use around 120 or so stock symbols, although smf matrix range covers 500 rows (C to AJ columns), in other words, 121 on wards no symbol in use and left blank
- once in a while, one or two symbols (tasr, algn or a mf symbol, it varies not symbol specific) returns error/#NA for that symbol
when I see this, I just reenter the symbol in say 121, repeating it. It then works fine...
- when I try to remove the duplicate symbols, the whole spreadsheet data vanishes...
As mentioned, this happens once in a while and the sheet performs well with my duplicate symbol entries whenever a symbol doesnt work.
Shankar
On Monday, November 6, 2017, 3:35:15 AM GMT+5:30, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
You'll need the 2017.11.04 version of the add-in to use the "51" field number.
On Sun, Nov 5, 2017 at 2:41 PM, u1704141@... wrote:
I transitioned over to using smfGetPortfolioView for Yahoo Quotes. =smfGetYahooPortfol
Tue Nov 7, 2017 7:30 am (PST) . Posted by:
"Higrm" higrm
I wouldn't recommend using Now() as a ticker symbol. Instead, put in a reference to another cell somewhere convenient on your sheet such as ='Sheet1'!A1 Put a nice label on this cell and then whenever you want a refresh of the portfolio, change the value of this cell from 1 to 2 or 2 to 1. This change is enough to make the array function refresh all the prices. This is better than bombarding yahoo with dozens of unnecessary requests that would potentially cause Yahoo to block this function from working as well.
On Tuesday, November 7, 2017, 6:25:06 AM GMT+1, dleeper@mail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Possible solution to getting the portfolio view to refresh is to include a "ticker symbol" that is just the value of =NOW(). That function changes every time a sheet recalc is called for.
Of course there is no such ticker symbol, and the portfolio view matrix just returns '--' for every column. But it's enough to get an update of the full matrix as well as the spreadsheet.I then use a VLOOKUP function to read updates for my list of 20 or so ticker symbols.
This is extremely fast, and I only use do the sheet recalc manually a few times a day when I need an update of my spreadsheet. Hopefully this does not put much load on Yahoo servers (?) -- I'd hate to have portfolio view "obsoleted" now that I've got it working as a replacement to RCHGetYahooQuotes() !!
I've tried several solutions to managing my portfolio in Excel. Randy's add-ins have been the best for me.
Much appreciated these past several years, Randy!
On Tuesday, November 7, 2017, 6:25:06 AM GMT+1, dleeper@mail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Possible solution to getting the portfolio view to refresh is to include a "ticker symbol" that is just the value of =NOW(). That function changes every time a sheet recalc is called for.
Of course there is no such ticker symbol, and the portfolio view matrix just returns '--' for every column. But it's enough to get an update of the full matrix as well as the spreadsheet.
This is extremely fast, and I only use do the sheet recalc manually a few times a day when I need an update of my spreadsheet. Hopefully this does not put much load on Yahoo servers (?) -- I'd hate to have portfolio view "obsoleted&quo
I've tried several solutions to managing my portfolio in Excel. Randy's add-ins have been the best for me.
Much appreciated these past several years, Randy!
Tue Nov 7, 2017 9:22 am (PST) . Posted by:
eremon9
I'm using the 2017.11.04 version.
Re: WFCPL, The problem is that the last trade date I'm getting is 09/26/17, and the corresponding price is, as you show, $1321. That price is incorrect for the last trade date as of today. The dates and prices for other tickers appear to be correct.
Re: BRK-B Book, I believe for all other tickers Item #41 returns Book/Share, whereas for BRK it returns total Book value. For example, WMT shows as 25.52 both here and when using smfGetElement, whereas BRK-B shows 182767 when using GetPortfolioView and 121.84 when using GetElement.
Re: selectively recalculating a smfGetPortfolioValue array without using smfForceRecalculation. I've managed to make this work by creating a public procedure that does essentially what your private procedure smfMenuRecalculateSelection() does. The only difference is that it refers to a named range [pvTblData] rather than to a selection...
Public Sub RecalcPortView()
sWebCache = "N" ' this is a SMF public variable
With [pvTblData]
.Dirty
.Calculate
End With
End Sub
According to the Time item, smfGetPortfolioView is now retrieving practically real-time quotes in a fraction of the time previously required using smfGetYahooQuotes.
Thanks again for your most valuable assistance over so many years, Randy. My Portfolio Manager is heavily dependent upon the fine utilities you've created.
Re: WFCPL, The problem is that the last trade date I'm getting is 09/26/17, and the corresponding price is, as you show, $1321. That price is incorrect for the last trade date as of today. The dates and prices for other tickers appear to be correct.
Re: BRK-B Book, I believe for all other tickers Item #41 returns Book/Share, whereas for BRK it returns total Book value. For example, WMT shows as 25.52 both here and when using smfGetElement, whereas BRK-B shows 182767 when using GetPortfolioView and 121.84 when using GetElement.
Re: selectively recalculating a smfGetPortfolioValu
Public Sub RecalcPortView(
sWebCache = "N" ' this is a SMF public variable
With [pvTblData]
.Dirty
.Calculate
End With
End Sub
According to the Time item, smfGetPortfolioView is now retrieving practically real-time quotes in a fraction of the time previously required using smfGetYahooQuotes.
Thanks again for your most valuable assistance over so many years, Randy. My Portfolio Manager is heavily dependent upon the fine utilities you've created.
Thanks Randy....
Will do next time... thought it was related.... sorry.
Armando
-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Nov 6, 2017 5:52 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: What's happening with yahoo quotes?
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 <http://etfs.morningstar.com/etfq/quote-banner?&t=SPY>
","div",-1,"id=""lastPrice""",,,,1)
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?\
&t=SPY <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/f\
und/c-header?&t=
<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.
Will do next time... thought it was related.... sorry.
Armando
-----Original Message-----
From: "Randy Harmelink rharmelink@gmail.com [smf_addin]"
Sent: Nov 6, 2017 5:52 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: What's happening with yahoo quotes?
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 <http://etfs.morningstar.com/etfq/quote-banner?&t=SPY>
","div",-1,"id=""lastPrice""",,,,1)
=smfGetTagContent("http://etfs.morningstar.com/etfq/quote-banner?\
&t=SPY <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/f\
und/c-header?&t=
<http://quotes.morningstar.com/fund/c-header?&t=> "&C11, "span"
0, "vkey="
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.
Good morning Randy.
Thanks you very much for your hint on how to get ETF last price from Morningstar.
I have two follow up questions:
(1) Is there a way to get Last Price for Stocks from Morningstar?
(2) I used to get US 30-year, 10-year and 5-year yields from Yahoo (respectively ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from Morningstar?
Thanks
Armando
P.S> Of course if there is another site to get this info would be welcome. But I found that things run faster if I the info is collected from the same site.
Thanks again
Thanks you very much for your hint on how to get ETF last price from Morningstar.
I have two follow up questions:
(1) Is there a way to get Last Price for Stocks from Morningstar?
(2) I used to get US 30-year, 10-year and 5-year yields from Yahoo (respectively ^TYX, ^TNX, and ^FVX). Is there a way to get something similar from Morningstar?
Thanks
Armando
P.S> Of course if there is another site to get this info would be welcome. But I found that things run faster if I the info is collected from the same site.
Thanks again
Tue Nov 7, 2017 5:49 am (PST) . Posted by:
codyklein
Does anyone know if the smfGetGuruFocusCSVItem work if you are not a Guru subscriber.
Tue Nov 7, 2017 8:40 am (PST) . Posted by:
"Randy Harmelink" rharmelink
It will not work. The CSV file GuruFocus produces can only be accessed by
subscribers.
On Tue, Nov 7, 2017 at 6:49 AM, codyklein@
...
wrote:
> Does anyone know if the smfGetGuruFocusCSVItem work if you are not a Guru
> subscriber.
>
>
subscribers.
On Tue, Nov 7, 2017 at 6:49 AM, codyklein@
...
wrote:
> Does anyone know if the smfGetGuruFocusCSVI
> subscriber.
>
>
Tue Nov 7, 2017 9:09 am (PST) . Posted by:
codyklein
Thanks Randy. That answers why it works great for me but not at all for others.
Tue Nov 7, 2017 6:50 am (PST) . Posted by:
colinmmoors
nasdaq.com is a good source for last trade using:
RCHGetTableCell("http://www.nasdaq.com/aspx/flashquotes.aspx?symbol=SHY",2,"Last Sale",,,,1)
here's previous close:
RCHGetTableCell("http://www.nasdaq.com/symbol/SHY",1,"Previous Close")
RCHGetTableCell("http://www.nasdaq.com/aspx/flashquotes.aspx?symbol=SHY",2,"Last Sale",,,,1)
here's previous close:
RCHGetTableCell("http://www.nasdaq.com/symbol/SHY",1,"
Tue Nov 7, 2017 9:26 am (PST) . Posted by:
"Randy Harmelink" rharmelink
But not good alternatives for someone that "wants" hundreds of quotes.
For bulk quotes, things like RCHGetYahooQuotes() or
smfGetYahooPortfolioView() or smfGetCSVFile() is needed.
More than once, I've had people complain that grabbing quotes took a long
time -- 20 minutes or longer to recalculate the worksheet. When they
converted the thousands of individual RCHGetYahooQuotes() invocations into
a single array-entered RCHGetYahooQuotes() invocation, recalculation
becomes almost instantaneous.
The rub is that the data itself needs to be provided in a way that it can
be retrieved that way.
On Tue, Nov 7, 2017 at 7:50 AM, colinmmoors@
...
wrote:
> <http://nasdaq.com>
> <http://nasdaq.com>
> nasdaq.com is a good source for last trade using:
>
> RCHGetTableCell("http://www.nasdaq.com/aspx/flashquotes.aspx?symbol=SHY",2,"Last
> Sale",,,,1)
>
> here's previous close:
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/SHY",1,"Previous Close")
>
>
>
For bulk quotes, things like RCHGetYahooQuotes() or
smfGetYahooPortfolioView() or smfGetCSVFile() is needed.
More than once, I've had people complain that grabbing quotes took a long
time -- 20 minutes or longer to recalculate the worksheet. When they
converted the thousands of individual RCHGetYahooQuotes() invocations into
a single array-entered RCHGetYahooQuotes() invocation, recalculation
becomes almost instantaneous.
The rub is that the data itself needs to be provided in a way that it can
be retrieved that way.
On Tue, Nov 7, 2017 at 7:50 AM, colinmmoors@
...
wrote:
> <http://nasdaq.com>
> <http://nasdaq.com>
> nasdaq.com is a good source for last trade using:
>
> RCHGetTableCell("http://www.nasdaq.com/aspx/flashquotes.aspx?symbol=SHY",2,"Last
> Sale",,,,1)
>
> here's previous close:
>
> RCHGetTableCell("http://www.nasdaq.com/symbol/SHY",1,"
>
>
>
Tue Nov 7, 2017 9:55 am (PST) . Posted by:
a.bereziuk
Hi Randy,
I am now testing the fresh example with smfGetYahooPortfolioView(). But it is not working smooth. Sometimes it does not deliver some ticker data. Seems that Yahoo has problems with provision of requested data.
Besides that there is no documentation on this function. I don't know how to change the array. How to remove some rows?
I am now testing the fresh example with smfGetYahooPortfoli
Besides that there is no documentation on this function. I don't know how to change the array. How to remove some rows?
Tue Nov 7, 2017 9:52 am (PST) . Posted by:
gpommeranz1
Thanks Randy. Not sure what happened. I had the latest Add-in file but I deleted everything from the folder it was in and reinstalled the add-in. Works now.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar