14 Messages
Digest #4146
2b
Re: Is there a fresh template RCHGetYahooHistory-Example-By-Paramete by "Randy Harmelink" rharmelink
Messages
Fri Sep 15, 2017 12:34 am (PDT) . Posted by:
zacharycal
Thanks.
I am actually trying to get the adjusted close. If you look at the values the formula is generating, it's actually the unadjusted close when I compare to the Yahoo! values.
Any thoughts?
Zach
I am actually trying to get the adjusted close. If you look at the values the formula is generating, it's actually the unadjusted close when I compare to the Yahoo! values.
Any thoughts?
Zach
Fri Sep 15, 2017 1:53 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I'm not seeing that here. What version of the add-in are you using. It
looks like you missed this update:
2017-07-12 smfGetYahooHistory Add back adjustments of O/H/L, swap/change
retrieval of adjusted and unadjusted close
Yahoo flip-flopped their data presentation on that date.
On Fri, Sep 15, 2017 at 12:34 AM, zabrams@
...
wrote:
>
> I am actually trying to get the adjusted close. If you look at the values
> the formula is generating, it's actually the unadjusted close when I
> compare to the Yahoo! values.
>
> Any thoughts?
>
>
looks like you missed this update:
2017-07-12 smfGetYahooHistory Add back adjustments of O/H/L, swap/change
retrieval of adjusted and unadjusted close
Yahoo flip-flopped their data presentation on that date.
On Fri, Sep 15, 2017 at 12:34 AM, zabrams@
...
wrote:
>
> I am actually trying to get the adjusted close. If you look at the values
> the formula is generating, it's actually the unadjusted close when I
> compare to the Yahoo! values.
>
> Any thoughts?
>
>
Fri Sep 15, 2017 9:10 am (PDT) . Posted by:
zacharycal
Thanks. I thought I updated it earlier this summer, but perhaps missed the latest one.
Fri Sep 15, 2017 4:37 am (PDT) . Posted by:
a.bereziuk
Thanks for the new template. Unfortunately it is not working by me. It shows only the date in the 1st cell of the array. See attachment. The same was with the old template. It could be caused by my European regional settings. I have Excel 2016, last version of addin. Any ideas?
Another question: Which function it is better to use to get the range of quotes for a time period, smfGetYahooHistory or smfPricesBetween ?
Another question: Which function it is better to use to get the range of quotes for a time period, smfGetYahooHistory or smfPricesBetween ?
Attachment(s) from
1 of 1 Photo(s)
Fri Sep 15, 2017 1:15 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
It probably is the regional settings. Looks like it converted the first
date correctly, but then tried to process something else, errored out, and
returned what it had up to that point. So maybe a numeric conversion didn't
work properly because of the regional settings?
smfGetYahooHistory() grabs raw price detail, while smfPricesBetween()
summarizes that price detail between two dates -- the starting and ending
prices are just extracted from that price detail, but the high is the
highest high and the low is the lowest low between them. smfPricesByDates()
can be a good way to just grab adjusted closing prices from multiple dates.
On Fri, Sep 15, 2017 at 4:37 AM, a.bereziuk@
...
wrote:
>
> Thanks for the new template. Unfortunately it is not working by me. It
> shows only the date in the 1st cell of the array. See attachment. The same
> was with the old template. It could be caused by my European regional
> settings. I have Excel 2016, last version of addin. Any ideas?
>
> Another question: Which function it is better to use to get the range of
> quotes for a time period, smfGetYahooHistory or smfPricesBetween ?
>
>
>
date correctly, but then tried to process something else, errored out, and
returned what it had up to that point. So maybe a numeric conversion didn't
work properly because of the regional settings?
smfGetYahooHistory(
summarizes that price detail between two dates -- the starting and ending
prices are just extracted from that price detail, but the high is the
highest high and the low is the lowest low between them. smfPricesByDates(
can be a good way to just grab adjusted closing prices from multiple dates.
On Fri, Sep 15, 2017 at 4:37 AM, a.bereziuk@
...
wrote:
>
> Thanks for the new template. Unfortunately it is not working by me. It
> shows only the date in the 1st cell of the array. See attachment. The same
> was with the old template. It could be caused by my European regional
> settings. I have Excel 2016, last version of addin. Any ideas?
>
> Another question: Which function it is better to use to get the range of
> quotes for a time period, smfGetYahooHistory or smfPricesBetween ?
>
>
>
Fri Sep 15, 2017 4:45 am (PDT) . Posted by:
a.bereziuk
>Personally, I don't use the Yahoo values.
Which values do you use? Is there an easy way to get the standard value of SMA90 for a time period?
Based on closing prices of trading days? Without all that details.
Which values do you use? Is there an easy way to get the standard value of SMA90 for a time period?
Based on closing prices of trading days? Without all that details.
Fri Sep 15, 2017 10:35 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
As I said in that same email: "I usually use the smfPricesByDates()
function and look at price changes instead. After all, the day-to-day
change in moving averages is just a change in one value -- the oldest day
is just replaced by the newest day. So the moving average only changes
based on their difference, because all the other prices stay the same."
The link pointed to the method of computing SMA90 yourself.
On Fri, Sep 15, 2017 at 4:45 AM, a.bereziuk@
...
wrote:
>
> >Personally, I don't use the Yahoo values.
> Which values do you use? Is there an easy way to get the standard value of
> SMA90 for a time period?
> Based on closing prices of trading days? Without all that details.
>
function and look at price changes instead. After all, the day-to-day
change in moving averages is just a change in one value -- the oldest day
is just replaced by the newest day. So the moving average only changes
based on their difference, because all the other prices stay the same."
The link pointed to the method of computing SMA90 yourself.
On Fri, Sep 15, 2017 at 4:45 AM, a.bereziuk@
...
wrote:
>
> >Personally, I don't use the Yahoo values.
> Which values do you use? Is there an easy way to get the standard value of
> SMA90 for a time period?
> Based on closing prices of trading days? Without all that details.
>
Fri Sep 15, 2017 7:14 am (PDT) . Posted by:
tonyestep
I can't get Google historical prices. Google still is providing historical prices and you can get them manually at, for example, https://finance.google.com/finance/historical?q=NYSEARCA:SPY https://finance.google.com/finance/historical?q=NYSEARCA:SPY. However, it appears that google's URL has changed from google.com/finance to google.finance.com/finance. I tried going through the add-in and changing it, but I still couldn't get it to work, probably because I don't know what I'm doing. Is there a simple fix for this?
thanks,
Tony
thanks,
Tony
Fri Sep 15, 2017 10:15 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I don't understand what you mean by "going through the add-in and changing
it"...?
Aren't you just using something like this:
=smfGetCSVFile("
http://finance.google.com/finance/historical?q=NYSEARCA:SPY&output=csv")
On Fri, Sep 15, 2017 at 7:14 AM, tonyestep@
...
wrote:
> I can't get Google historical prices. Google still is providing historical
> prices and you can get them manually at, for example,
> https://finance.google.com/finance/historical?q=NYSEARCA:SPY. However, it
> appears that google's URL has changed from google.com/finance to
> google.finance.com/finance. I tried going through the add-in and changing
> it, but I still couldn't get it to work, probably because I don't know what
> I'm doing. Is there a simple fix for this?
>
it"...?
Aren't you just using something like this:
=smfGetCSVFile("
http://finance.google.com/finance/historical?q=NYSEARCA:SPY&output=csv")
On Fri, Sep 15, 2017 at 7:14 AM, tonyestep@
...
wrote:
> I can't get Google historical prices. Google still is providing historical
> prices and you can get them manually at, for example,
> https://finance.google.com/finance/historical?q=NYSEARCA:SPY. However, it
> appears that google's URL has changed from google.com/finance to
> google.finance.
> it, but I still couldn't get it to work, probably because I don't know what
> I'm doing. Is there a simple fix for this?
>
Fri Sep 15, 2017 12:44 pm (PDT) . Posted by:
tonyestep
Aw gee, I am too dumb to be trading stocks. I see what I was doing wrong. Thanks, Randy.
Fri Sep 15, 2017 8:23 am (PDT) . Posted by:
rus101
Happy to save you some time, as YOU saved us all TONS of time over the years! I guess I'll just have to run that test of MS vs Yahoo for annual returns for each ticker I use to see if there are discrepancies. That way I can at least trust data on some tickers...
Fri Sep 15, 2017 11:33 am (PDT) . Posted by:
rus101
Randy, you mentioned "you should use an array-entered smfPricesByDates() and get them all in one Internet request." How is would you do an array in this case? I set up the dates the way you suggested, but how do you do array and refer to various cells for different dates?
Also, just FYI. I've been testing a lot of symbols for this. There doesn't seem to be any pattern. Most funds/ETFs are fine but some are screwed up. For Vanguard index funds some are good and some are bad, Vanguard Total Intl Stock Index Admiral is no bad but Vanguard Total Intl Stock Index Investor shares is fine..
Also, just FYI. I've been testing a lot of symbols for this. There doesn't seem to be any pattern. Most funds/ETFs are fine but some are screwed up. For Vanguard index funds some are good and some are bad, Vanguard Total Intl Stock Index Admiral is no bad but Vanguard Total Intl Stock Index Investor shares is fine..
Fri Sep 15, 2017 11:48 am (PDT) . Posted by:
"Craig Passow" passow
Standard Excel operation, Google is your friend: Create an array formula
<https://support.office.com/en-us/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d>
On 9/15/2017 1:33 PM, rus101@yahoo.com [smf_addin] wrote:
>
>
> Randy, you mentioned "you should use an array-entered
> smfPricesByDates() and get them all in one Internet request." How is
> would you do an array in this case? I set up the dates the way you
> suggested, but how do you do array and refer to various cells for
> different dates?
>
> Also, just FYI. I've been testing a lot of symbols for this. There
> doesn't seem to be any pattern. Most funds/ETFs are fine but some are
> screwed up. For Vanguard index funds some are good and some are bad,
> Vanguard Total Intl Stock Index Admiral is no bad but Vanguard Total
> Intl Stock Index Investor shares is fine..
>
>
<https://support.office.com/en-us/article/Create-an-array-formula-e43e12e0-afc6-4a12-bc7f-48361075954d>
On 9/15/2017 1:33 PM, rus101@yahoo.com [smf_addin] wrote:
>
>
> Randy, you mentioned "you should use an array-entered
> smfPricesByDates(
> would you do an array in this case? I set up the dates the way you
> suggested, but how do you do array and refer to various cells for
> different dates?
>
> Also, just FYI. I've been testing a lot of symbols for this. There
> doesn't seem to be any pattern. Most funds/ETFs are fine but some are
> screwed up. For Vanguard index funds some are good and some are bad,
> Vanguard Total Intl Stock Index Admiral is no bad but Vanguard Total
> Intl Stock Index Investor shares is fine..
>
>
Fri Sep 15, 2017 2:50 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I think the most likely source of missing distributions would be the mutual
funds. They are probably handled differently than stocks and ETFs. The data
may be coming from a different vendor.
Check out the two templates on the web site for examples of
smfPricesByDates(). I usually do tickers down a column and dates across the
top. I can usually create the table for a few tickers in under a minute, so
I don't use the templates. The formula is usually something like:
=IFERROR(smfPricesByDates(C8,$D$7:$O$7),"--")
The IFERROR() so it will display "--" for dates not found (before inception
or after today). The date range is usually an absolute range (i.e. the "$"
prefixes) so I can copy it down for other ticker symbols.
I've attached a copy of something I did "on the fly"...it's usually easier
to separate retrieved amounts from percentage calculations, so both can be
copied down for more symbols easily. Once I start, I find I usually want to
compare to a few more tickers. :)
Too often, I end up adding a few more dates too. So sometimes I just extend
the formula beyond my entered dates, so it's easy to add a date or two if I
want. I usually do current date on the left and older dates to the right,
so this was a little different for me. Adding dates to the right is easier
then too.
Sometimes I do cumulative returns instead of yearly returns. Sometimes I'll
add a CAGR calculation. It all depends on my needs at the time. It's a
function I use quite a bit.
On Fri, Sep 15, 2017 at 11:33 AM, rus101@
...
wrote:
>
> Randy, you mentioned "you should use an array-entered smfPricesByDates()
> and get them all in one Internet request." How is would you do an array in
> this case? I set up the dates the way you suggested, but how do you do
> array and refer to various cells for different dates?
>
> Also, just FYI. I've been testing a lot of symbols for this. There doesn't
> seem to be any pattern. Most funds/ETFs are fine but some are screwed up.
> For Vanguard index funds some are good and some are bad, Vanguard Total
> Intl Stock Index Admiral is no bad but Vanguard Total Intl Stock Index Investor
> shares is fine..
>
funds. They are probably handled differently than stocks and ETFs. The data
may be coming from a different vendor.
Check out the two templates on the web site for examples of
smfPricesByDates(
top. I can usually create the table for a few tickers in under a minute, so
I don't use the templates. The formula is usually something like:
=IFERROR(smfPricesB
The IFERROR() so it will display "--" for dates not found (before inception
or after today). The date range is usually an absolute range (i.e. the "$"
prefixes) so I can copy it down for other ticker symbols.
I've attached a copy of something I did "on the fly"...
to separate retrieved amounts from percentage calculations, so both can be
copied down for more symbols easily. Once I start, I find I usually want to
compare to a few more tickers. :)
Too often, I end up adding a few more dates too. So sometimes I just extend
the formula beyond my entered dates, so it's easy to add a date or two if I
want. I usually do current date on the left and older dates to the right,
so this was a little different for me. Adding dates to the right is easier
then too.
Sometimes I do cumulative returns instead of yearly returns. Sometimes I'll
add a CAGR calculation. It all depends on my needs at the time. It's a
function I use quite a bit.
On Fri, Sep 15, 2017 at 11:33 AM, rus101@
...
wrote:
>
> Randy, you mentioned "you should use an array-entered smfPricesByDates(
> and get them all in one Internet request." How is would you do an array in
> this case? I set up the dates the way you suggested, but how do you do
> array and refer to various cells for different dates?
>
> Also, just FYI. I've been testing a lot of symbols for this. There doesn't
> seem to be any pattern. Most funds/ETFs are fine but some are screwed up.
> For Vanguard index funds some are good and some are bad, Vanguard Total
> Intl Stock Index Admiral is no bad but Vanguard Total Intl Stock Index Investor
> shares is fine..
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar