10 Messages
Digest #4145
1b
Re: Is there a fresh template RCHGetYahooHistory-Example-By-Paramete by "Randy Harmelink" rharmelink
Messages
Thu Sep 14, 2017 6:20 am (PDT) . Posted by:
a.bereziuk
Hello,
Is there a fresh example of use of RCHGetYahooHistory func? The one in the http://ogres-crypt.com/SMF/Templates/ http://ogres-crypt.com/SMF/Templates/ section and the documentation are obsolete and use separately day, month and year parameters.
Is there a fresh example of use of RCHGetYahooHistory func? The one in the http://ogres-crypt.com/SMF/Templates/ http://ogres-crypt.com/SMF/Templates/ section and the documentation are obsolete and use separately day, month and year parameters.
Thu Sep 14, 2017 10:06 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
That template won't change. RCHGetYahooHistory() still has the same
parameters.
I suspect what you're asking for is a template for the new
smfGetYahooHistory()? That version is not documented on the web site, but
is out there:
http://ogres-crypt.com/SMF/Works-In-Progress/smfGetYahooHistory-Example-By-Parameter.xls
One more thing on a long "to do" list . Sorry.
On Thu, Sep 14, 2017 at 6:20 AM, a.bereziuk@
...
wrote:
> Is there a fresh example of use of RCHGetYahooHistory func? The one in the
> http://ogres-crypt.com/SMF/Templates/ section and the documentation are
> obsolete and use separately day, month and year parameters.
>
>
>
parameters.
I suspect what you're asking for is a template for the new
smfGetYahooHistory()? That version is not documented on the web site, but
is out there:
http://ogres-crypt.com/SMF/Works-In-Progress/smfGetYahooHistory-Example-By-Parameter.xls
One more thing on a long "to do" list . Sorry.
On Thu, Sep 14, 2017 at 6:20 AM, a.bereziuk@
...
wrote:
> Is there a fresh example of use of RCHGetYahooHistory func? The one in the
> http://ogres-crypt.com/SMF/Templates/ section and the documentation are
> obsolete and use separately day, month and year parameters.
>
>
>
Thu Sep 14, 2017 8:52 am (PDT) . Posted by:
gfoster07k@sbcglobal.net
For some reason this add-in seems to have stopped working. I have used for a long time but can no longer get results. I am using a Toshiba Satellite lap top with Chrome V60 (64 bit), Windows 10 V1703, Excel 2013 v15 32 bit (part of Office Home and Student).
The add-ins are located in the required smf file. I used the Add-In Manager to point out their location. I have a spreadsheet and I tried to reenter the formula and press CTRL + Shift + enter to enter the formula in a two cell array. I have enclosed attachments of screen shots. Note that the add-in just returns zero (0)
I have tried entering just as it is in the documentation with "IBM" and get the same thing. ... 0.0.
Any help will be greatly appreciated.
Greg Foster
The add-ins are located in the required smf file. I used the Add-In Manager to point out their location. I have a spreadsheet and I tried to reenter the formula and press CTRL + Shift + enter to enter the formula in a two cell array. I have enclosed attachments of screen shots. Note that the add-in just returns zero (0)
I have tried entering just as it is in the documentation with "IBM" and get the same thing. ... 0.0.
Any help will be greatly appreciated.
Greg Foster
Attachment(s) from
2 of 2 Photo(s)
Thu Sep 14, 2017 10:49 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The formula appears to work here:
FSPTX 2017-06-12 2017-09-13 $164.33 $177.96
Are you sure you have the current beta version installed? What do you get
with:
=RCHGetElementNumber("Version")
Note that the add-in is loaded when EXCEL starts up, so if you update the
add-in files with EXCEL open, you would need to exit and restart EXCEL to
have EXCEL load the new version of the add-in..?
The last folder name on your file display gives me some concern, as it's
showing a sub-directory of the normal add-in folder, not the usual folder
that would be used?
On Thu, Sep 14, 2017 at 8:52 AM, gfoster07k@
...
wrote:
> For some reason this add-in seems to have stopped working. I have used
> for a long time but can no longer get results. I am using a Toshiba
> Satellite lap top with Chrome V60 (64 bit), Windows 10 V1703, Excel 2013
> v15 32 bit (part of Office Home and Student).
>
> The add-ins are located in the required smf file. I used the Add-In
> Manager to point out their location. I have a spreadsheet and I tried to
> reenter the formula and press CTRL + Shift + enter to enter the formula in
> a two cell array. I have enclosed attachments of screen shots. Note that
> the add-in just returns zero (0)
>
> I have tried entering just as it is in the documentation with "IBM" and
> get the same thing. ... 0.0.
>
> Any help will be greatly appreciated.
>
>
>
FSPTX 2017-06-12 2017-09-13 $164.33 $177.96
Are you sure you have the current beta version installed? What do you get
with:
=RCHGetElementNumbe
Note that the add-in is loaded when EXCEL starts up, so if you update the
add-in files with EXCEL open, you would need to exit and restart EXCEL to
have EXCEL load the new version of the add-in..?
The last folder name on your file display gives me some concern, as it's
showing a sub-directory of the normal add-in folder, not the usual folder
that would be used?
On Thu, Sep 14, 2017 at 8:52 AM, gfoster07k@
...
wrote:
> For some reason this add-in seems to have stopped working. I have used
> for a long time but can no longer get results. I am using a Toshiba
> Satellite lap top with Chrome V60 (64 bit), Windows 10 V1703, Excel 2013
> v15 32 bit (part of Office Home and Student).
>
> The add-ins are located in the required smf file. I used the Add-In
> Manager to point out their location. I have a spreadsheet and I tried to
> reenter the formula and press CTRL + Shift + enter to enter the formula in
> a two cell array. I have enclosed attachments of screen shots. Note that
> the add-in just returns zero (0)
>
> I have tried entering just as it is in the documentation with "IBM" and
> get the same thing. ... 0.0.
>
> Any help will be greatly appreciated.
>
>
>
Thu Sep 14, 2017 12:50 pm (PDT) . Posted by:
rus101
Hi Randy,
You've mentioned how you don't trust Yahoo pricing data anymore and I've been running into various issues with bad numbers. One BIG issue is that I can't seem to calculate performance from smfPricesByDates anymore.
I uploaded a file (performance checks.xlsx) https://xa.yimg.com/df/smf_addin/performance+checks.xlsx?token=kuFpdFAyZRW4V3KmbZNpfxTLIcWrsqviMQE1rDsQdbtCJcH08jEyzVGIkxtOUs24_9Gw5h_RqDog7uYYWOsZn5weRm0Rw7t1P6icX4ICUoFjt7cszwwBLvnPL6S2t-RRNzwuoFHGicmaS06AWlVsaL0fR3VxSlY&type=download so you can see what I'm talking about. There are four Vanguard funds/ETFs that I compared the annual performance data from Morningstar (first line) to the same year perf base on smfPricesByDates (second line), then compare the two in the third line. It used to be VERY close for each year, but now I have some very large discrepancies I highlighted in green.
What are your thoughts on this? Any way to get reliable performance data between 2 dates (not necessarily calendar years).
Thanks as always for all your great work!
Denis
You've mentioned how you don't trust Yahoo pricing data anymore and I've been running into various issues with bad numbers. One BIG issue is that I can't seem to calculate performance from smfPricesByDates anymore.
I uploaded a file (performance checks.xlsx) https://xa.yimg.com/df/smf_addin/performance+checks.xlsx?token=kuFpdFAyZRW4V3KmbZNpfxTLIcWrsqviMQE1rDsQdbtCJcH08jEyzVGIkxtOUs24_9Gw5h_RqDog7uYYWOsZn5weRm0Rw7t1P6icX4ICUoFjt7cszwwBLvnPL6S2t-RRNzwuoFHGicmaS06AWlVsaL0fR3VxSlY&type=download so you can see what I'm talking about. There are four Vanguard funds/ETFs that I compared the annual performance data from Morningstar (first line) to the same year perf base on smfPricesByDates (second line), then compare the two in the third line. It used to be VERY close for each year, but now I have some very large discrepancies I highlighted in green.
What are your thoughts on this? Any way to get reliable performance data between 2 dates (not necessarily calendar years).
Thanks as always for all your great work!
Denis
Thu Sep 14, 2017 1:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I briefly looked at it as soon as I got the note it was updated. I was not
happy with the way you designed it. Looking at my log, I saw 58 Internet
requests that took 43 seconds waiting for responses. It sat there
recalculating a long time...
If you are getting multiple dates for a single ticker symbol, you should
use an array-entered smfPricesByDates() and get them all in one Internet
request.
Also, you used the EXCEL TODAY() function. Anything based on that is
volatile and requires recalculation every time something is changed.
Unfortunately, I closed the workbook down already, and am not anxious to
reopen it. I'll get back to it later. But I have noticed differences in the
past.
It's easy to set up a set of dates for smfPricesByDates() without using
TODAY(). For example, I could do this in cell O7 to get the last date SPY
was traded, which is an acceptable non-volatile replacement for TODAY():
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
Then I can get the previous year-end value with this in cell O6:
=EOMONTH(O7,-MONTH(O7))
Then you copy that leftward as needed to get prior year-ends. Then just do
the smfPricesByDates() on those dates. Format your date cells as desired. I
end up with:
2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017
BND -- $56.94 $60.85 $63.06 $66.97 $72.28 $74.41 $72.84 $77.08 $77.36 $79.13
$82.17
-- -- 6.9% 3.6% 6.2% 7.9% 2.9% -2.1% 5.8% 0.4% 2.3% 3.8%
That took two Internet requests for under 2 seconds, while yours needed 14
requests taking 15 seconds.
I want to compare those numbers with what you had...
On Thu, Sep 14, 2017 at 12:50 PM, rus101@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> You've mentioned how you don't trust Yahoo pricing data anymore and I've
> been running into various issues with bad numbers. One BIG issue is that I
> can't seem to calculate performance from smfPricesByDates anymore.
>
> I uploaded a file (performance checks.xlsx)
> <https://xa.yimg.com/df/smf_addin/performance+checks.xlsx?token=kuFpdFAyZRW4V3KmbZNpfxTLIcWrsqviMQE1rDsQdbtCJcH08jEyzVGIkxtOUs24_9Gw5h_RqDog7uYYWOsZn5weRm0Rw7t1P6icX4ICUoFjt7cszwwBLvnPL6S2t-RRNzwuoFHGicmaS06AWlVsaL0fR3VxSlY&type=download>
> so you can see what I'm talking about. There are four Vanguard funds/ETFs
> that I compared the annual performance data from Morningstar (first line)
> to the same year perf base on smfPricesByDates (second line), then compare
> the two in the third line. It used to be VERY close for each year, but now
> I have some very large discrepancies I highlighted in green.
>
> What are your thoughts on this? Any way to get reliable performance data
> between 2 dates (not necessarily calendar years).
>
> Thanks as always for all your great work!
>
>
happy with the way you designed it. Looking at my log, I saw 58 Internet
requests that took 43 seconds waiting for responses. It sat there
recalculating a long time...
If you are getting multiple dates for a single ticker symbol, you should
use an array-entered smfPricesByDates() and get them all in one Internet
request.
Also, you used the EXCEL TODAY() function. Anything based on that is
volatile and requires recalculation every time something is changed.
Unfortunately, I closed the workbook down already, and am not anxious to
reopen it. I'll get back to it later. But I have noticed differences in the
past.
It's easy to set up a set of dates for smfPricesByDates() without using
TODAY(). For example, I could do this in cell O7 to get the last date SPY
was traded, which is an acceptable non-volatile replacement for TODAY():
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
Then I can get the previous year-end value with this in cell O6:
=EOMONTH(O7,-MONTH(O7))
Then you copy that leftward as needed to get prior year-ends. Then just do
the smfPricesByDates() on those dates. Format your date cells as desired. I
end up with:
2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017
BND -- $56.94 $60.85 $63.06 $66.97 $72.28 $74.41 $72.84 $77.08 $77.36 $79.13
$82.17
-- -- 6.9% 3.6% 6.2% 7.9% 2.9% -2.1% 5.8% 0.4% 2.3% 3.8%
That took two Internet requests for under 2 seconds, while yours needed 14
requests taking 15 seconds.
I want to compare those numbers with what you had...
On Thu, Sep 14, 2017 at 12:50 PM, rus101@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> You've mentioned how you don't trust Yahoo pricing data anymore and I've
> been running into various issues with bad numbers. One BIG issue is that I
> can't seem to calculate performance from smfPricesByDates anymore.
>
> I uploaded a file (performance checks.xlsx)
> <https://xa.yimg.com/df/smf_addin/performance+checks.xlsx?token=kuFpdFAyZRW4V3KmbZNpfxTLIcWrsqviMQE1rDsQdbtCJcH08jEyzVGIkxtOUs24_9Gw5h_RqDog7uYYWOsZn5weRm0Rw7t1P6icX4ICUoFjt7cszwwBLvnPL6S2t-RRNzwuoFHGicmaS06AWlVsaL0fR3VxSlY&type=download>
> so you can see what I'm talking about. There are four Vanguard funds/ETFs
> that I compared the annual performance data from Morningstar (first line)
> to the same year perf base on smfPricesByDates (second line), then compare
> the two in the third line. It used to be VERY close for each year, but now
> I have some very large discrepancies I highlighted in green.
>
> What are your thoughts on this? Any way to get reliable performance data
> between 2 dates (not necessarily calendar years).
>
> Thanks as always for all your great work!
>
>
Thu Sep 14, 2017 2:50 pm (PDT) . Posted by:
rus101
Yeah, sorry about the volatile functions. I turn off the calculation on all SMF spreadsheets and manually recalculate only the parts I need when I need... And thanks for the design pointers, very helpful!
Be that as it may, the underlying problem remains. BND performance in your calcs was exactly the same as mine and that means same big gaps. Good example is 2012 perf for BND - you and I have it at 2.9% while Morningstar (and Vanguard's own site) have it at 3.9%. So 95 bps difference is huge!
Doing some digging and comparing - sorry about turning this into a major project but it's very important to me to be confident in performance data!
So I compared BND adjusted closing prices on 12/30/2011 (72.28) and 12/31/2012 (74.41) on smfPricesByDates(), RCHGetYahooHistory, and Yahoo actual website https://finance.yahoo.com/quote/BND/history?period1=1324796400&period2=1356937200&interval=1d&filter=history&frequency=1d https://finance.yahoo.com/quote/BND/history?period1=1324796400&period2=1356937200&interval=1d&filter=history&frequency=1d
They all match exactly. So I went digging in dividends (and other distributions) that Yahoo uses to get the adjusted price. Comparign 2012 BND distribution on Vanguard site vs. Yahoo, it seems that Yahoo is missing 2 distributions $0.179 on 11/1/2012 and $0.593 on 12/24/2012. That adds up to $0.771 which is about the "missing" 95 bps gap.
So the issues is underlying data itself, regardless how you get to it - smfPricesByDates(), RCHGetYahooHistory, and Yahoo actual website!
Be that as it may, the underlying problem remains. BND performance in your calcs was exactly the same as mine and that means same big gaps. Good example is 2012 perf for BND - you and I have it at 2.9% while Morningstar (and Vanguard's own site) have it at 3.9%. So 95 bps difference is huge!
Doing some digging and comparing - sorry about turning this into a major project but it's very important to me to be confident in performance data!
So I compared BND adjusted closing prices on 12/30/2011 (72.28) and 12/31/2012 (74.41) on smfPricesByDates(), RCHGetYahooHistory, and Yahoo actual website https://finance.yahoo.com/quote/BND/history?period1=1324796400&period2=1356937200&interval=1d&filter=history&frequency=1d https://finance.yahoo.com/quote/BND/history?period1=1324796400&period2=1356937200&interval=1d&filter=history&frequency=1d
They all match exactly. So I went digging in dividends (and other distributions) that Yahoo uses to get the adjusted price. Comparign 2012 BND distribution on Vanguard site vs. Yahoo, it seems that Yahoo is missing 2 distributions $0.179 on 11/1/2012 and $0.593 on 12/24/2012. That adds up to $0.771 which is about the "missing" 95 bps gap.
So the issues is underlying data itself, regardless how you get to it - smfPricesByDates(
Thu Sep 14, 2017 5:00 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If Yahoo is missing distributions, that would certainly explain differences.
I was planning to compare the historical quotes between Yahoo and
Morningstar, as well as look at the distributions. Sounds like there is no
need to now. Thanks for letting me know.
On Thu, Sep 14, 2017 at 2:50 PM, rus101@
...
wrote:
>
> Yeah, sorry about the volatile functions. I turn off the calculation on
> all SMF spreadsheets and manually recalculate only the parts I need when I
> need... And thanks for the design pointers, very helpful!
>
> Be that as it may, the underlying problem remains. BND performance in your
> calcs was exactly the same as mine and that means same big gaps. Good
> example is 2012 perf for BND - you and I have it at 2.9% while Morningstar
> (and Vanguard's own site) have it at 3.9%. So 95 bps difference is huge!
>
> Doing some digging and comparing - sorry about turning this into a major
> project but it's very important to me to be confident in performance data!
> So I compared BND adjusted closing prices on 12/30/2011 (72.28) and
> 12/31/2012 (74.41) on smfPricesByDates(), RCHGetYahooHistory, and Yahoo
> actual website https://finance.yahoo.com/quote/BND/history?period1=
> 1324796400&period2=1356937200&interval=1d&filter=history&frequency=1d
>
> They all match exactly. So I went digging in dividends (and other
> distributions) that Yahoo uses to get the adjusted price. Comparign 2012
> BND distribution on Vanguard site vs. Yahoo, it seems that Yahoo is missing
> 2 distributions $0.179 on 11/1/2012 and $0.593 on 12/24/2012. That adds
> up to $0.771 which is about the "missing" 95 bps gap.
>
> So the issues is underlying data itself, regardless how you get to it - smfPricesByDates(),
> RCHGetYahooHistory, and Yahoo actual website!
>
I was planning to compare the historical quotes between Yahoo and
Morningstar, as well as look at the distributions. Sounds like there is no
need to now. Thanks for letting me know.
On Thu, Sep 14, 2017 at 2:50 PM, rus101@
...
wrote:
>
> Yeah, sorry about the volatile functions. I turn off the calculation on
> all SMF spreadsheets and manually recalculate only the parts I need when I
> need... And thanks for the design pointers, very helpful!
>
> Be that as it may, the underlying problem remains. BND performance in your
> calcs was exactly the same as mine and that means same big gaps. Good
> example is 2012 perf for BND - you and I have it at 2.9% while Morningstar
> (and Vanguard's own site) have it at 3.9%. So 95 bps difference is huge!
>
> Doing some digging and comparing - sorry about turning this into a major
> project but it's very important to me to be confident in performance data!
> So I compared BND adjusted closing prices on 12/30/2011 (72.28) and
> 12/31/2012 (74.41) on smfPricesByDates(), RCHGetYahooHistory, and Yahoo
> actual website https://finance.yahoo.com/quote/BND/history?period1=
> 1324796400&period2=
>
> They all match exactly. So I went digging in dividends (and other
> distributions) that Yahoo uses to get the adjusted price. Comparign 2012
> BND distribution on Vanguard site vs. Yahoo, it seems that Yahoo is missing
> 2 distributions $0.179 on 11/1/2012 and $0.593 on 12/24/2012. That adds
> up to $0.771 which is about the "missing" 95 bps gap.
>
> So the issues is underlying data itself, regardless how you get to it - smfPricesByDates(
> RCHGetYahooHistory, and Yahoo actual website!
>
Thu Sep 14, 2017 2:20 pm (PDT) . Posted by:
zacharycal
Sorry for the delay. Please see the attached, where I use adjusted close and close.
Thanks!
Thanks!
Attachment(s) from
2 of 2 Photo(s)
Thu Sep 14, 2017 3:59 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sorry. In RCHGetYahooQuotes(), "A" and "C" now mean the same thing. If you
want unadjusted close, you need to use "U". That's because adjusted close
is the new norm on Yahoo, or at least was when I made all my programming
changes.
On Thu, Sep 14, 2017 at 2:20 PM, zabrams@
...
wrote:
>
> Sorry for the delay. Please see the attached, where I use adjusted close
> and close.
>
>
want unadjusted close, you need to use "U"
is the new norm on Yahoo, or at least was when I made all my programming
changes.
On Thu, Sep 14, 2017 at 2:20 PM, zabrams@
...
wrote:
>
> Sorry for the delay. Please see the attached, where I use adjusted close
> and close.
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar