15 Messages
Digest #4017
Messages
Sat May 20, 2017 9:59 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
When you call it from VBA, you need to tell it the side of the array to
return. Normally, that's based on the range you enter the formula over, but
you don't have a range in this function. So you need to add the pRows and
pCols parameters:
ActiveCell.Value =
smfGetYahooHistory(gTicker, Cells(iRow, 1), Cells(iRow, 1), "d", "c", 0,
pRows:=1, pCols:=1)
I just added code to default them if no range was used for the function, to
the difference between the two dates and the size of the pItems parameter.
That may be larger than needed, but it will just use what it needs when
returning data.
Thanks for catching that.
On Sat, May 20, 2017 at 7:47 AM, Gary.Hartling@
...wrote:
>
> That works. How about the same thing with a VBA statement. This produces a
> "Type Mismatch" error for me:
>
>
>
> ActiveCell.Value =
>
> smfGetYahooHistory(gTicker, Cells(iRow, 1), Cells(iRow, 1), "d", "c", 0)
>
> where:
> gTicker = "COP"
> Cells(iRow,1) = "3/17/2017"
>
>
return. Normally, that's based on the range you enter the formula over, but
you don't have a range in this function. So you need to add the pRows and
pCols parameters:
ActiveCell.Value =
smfGetYahooHistory(
pRows:=1, pCols:=1)
I just added code to default them if no range was used for the function, to
the difference between the two dates and the size of the pItems parameter.
That may be larger than needed, but it will just use what it needs when
returning data.
Thanks for catching that.
On Sat, May 20, 2017 at 7:47 AM, Gary.Hartling@
...wrote:
>
> That works. How about the same thing with a VBA statement. This produces a
> "Type Mismatch" error for me:
>
>
>
> ActiveCell.Value =
>
> smfGetYahooHistory(
>
> where:
> gTicker = "COP"
> Cells(iRow,1) = "3/17/2017&quo
>
>
Sat May 20, 2017 10:03 am (PDT) . Posted by:
kitiany
I had the latest "zipped" version (2015.05.03) that I downloaded from the ogres-crypt.com/smf/ directory. I now loaded the update you pointed to in message https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112 https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
and it now gives me the 2017.05.18 version and all examples, such as
=smfGetYahooHistory("IBM","3/1/2017","3/1/2017","d","c",0), delivers.
By "listed" I meant, if I type "=smf" in Excel, it will give me in a list box with functions that starts with "smf". After installing the 5/18/17 version, smfGetYahooHistory now shows up on the list of available functions.
Thanks again.
Kit
and it now gives me the 2017.05.18 version and all examples, such as
=smfGetYahooHistory
By "listed" I meant, if I type "=smf" in Excel, it will give me in a list box with functions that starts with "smf"
Thanks again.
Kit
Sat May 20, 2017 10:06 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
You need to array-enter the function over a large enough range to hold all
the data. Otherwise, the function can only return enough data to fill the
range it was entered over. Functions can only put values into cells they've
been given access to.
Is that the problem?
On Sat, May 20, 2017 at 7:56 AM, V A viewasia@
...wrote:
>
> Even if I specify a 'start date' (say, 2012-01-1), daily data limits
> itself to begin from 2016-03-03. However, 'weekly' & 'monthly' data fetch
> seem to fetch correctly?Could I be missing something?
> Thanks for your help!
>
>
the data. Otherwise, the function can only return enough data to fill the
range it was entered over. Functions can only put values into cells they've
been given access to.
Is that the problem?
On Sat, May 20, 2017 at 7:56 AM, V A viewasia@
...wrote:
>
> Even if I specify a 'start date' (say, 2012-01-1), daily data limits
> itself to begin from 2016-03-03. However, 'weekly&#
> seem to fetch correctly?Could I be missing something?
> Thanks for your help!
>
>
Sat May 20, 2017 10:48 am (PDT) . Posted by:
tonyestep
Well, thanks to Randy's excellent new function, I'm back as if nothing had happened. Great work, Randy! I have found no bugs or problems with the function, but I do have a suggestion that might apply to some users. Since this function is a bit slower than the old one, I made a separate workbook that gets all the historical prices I'm going to use for various applications. Then I save that workbook and use it as a database for the individual sheets, so they have no calls to the smf function. They just reference cells in the database workbook. This is faster, and makes for fewer hits on Yahoo -- there's always the chance that hitting them too often will make them want to throttle us, as Google and Twitter do with API calls.
Sat May 20, 2017 10:48 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmm. Yahoo does the split adjustments, but the RSP and BRK-B splits seems
OK to me?
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2006-04-28 $44.4400 $44.6400 $44.3900 $44.4800 216600 $44.4800 1.00000
1.00000
2006-04-27 $44.0800 $44.6900 $44.0200 $44.4400 576700 $44.4400 1.00000
1.00000 4 for 1
2006-04-26 $44.5225 $44.6075 $44.3050 $44.4300 322800 $177.7200 1.00000
0.25000
2006-04-25 $44.5250 $44.5625 $44.2000 $44.3150 373600 $177.2600 1.00000
0.25000
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2010-01-22 $72.9000 $73.2700 $69.3600 $70.1000 8783200 $70.1000 1.00000
1.00000
2010-01-21 $71.1300 $73.4300 $70.5100 $72.7200 14497200 $72.7200 1.00000
1.00000 50 for 1
2010-01-20 $67.5200 $70.0500 $66.8000 $69.5200 12475000 $3,475.9998 1.00000
0.02000
2010-01-19 $65.5200 $66.7600 $65.2500 $66.6400 5535000 $3,332.0000 1.00000
0.02000
The only history I see for WFC-PL is yesterday:
https://finance.yahoo.com/quote/WFC-PL/history
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2017-05-19 $1,269.0000 $1,272.9700 $1,272.9700 $1,272.9700 3555 $1,272.9700
1.00000 1.00000
Keep in mind that Yahoo may be "scrubbing" their data as this testing is
going on. We know there are issues...
On Sat, May 20, 2017 at 8:05 AM, tamurphy@
...
wrote:
>
> Add my thanks to those of all the others, Randy. I've been relying on your
> add-in for many years.
>
> By way of additional debugging feedback, I notice that splits for RSP and
> BRK-B aren't handled. RSP a 4 for 1 split on on 4/27/06, and BRK-B a 50 for
> 1 split on 1/21/10.
>
> Also, no data returned for WFC series L preferred stock.
>
>
OK to me?
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2006-04-28 $44.4400 $44.6400 $44.3900 $44.4800 216600 $44.4800 1.00000
1.00000
2006-04-27 $44.0800 $44.6900 $44.0200 $44.4400 576700 $44.4400 1.00000
1.00000 4 for 1
2006-04-26 $44.5225 $44.6075 $44.3050 $44.4300 322800 $177.7200 1.00000
0.25000
2006-04-25 $44.5250 $44.5625 $44.2000 $44.3150 373600 $177.2600 1.00000
0.25000
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2010-01-22 $72.9000 $73.2700 $69.3600 $70.1000 8783200 $70.1000 1.00000
1.00000
2010-01-21 $71.1300 $73.4300 $70.5100 $72.7200 14497200 $72.7200 1.00000
1.00000 50 for 1
2010-01-20 $67.5200 $70.0500 $66.8000 $69.5200 12475000 $3,475.9998 1.00000
0.02000
2010-01-19 $65.5200 $66.7600 $65.2500 $66.6400 5535000 $3,332.0000 1.00000
0.02000
The only history I see for WFC-PL is yesterday:
https://finance.yahoo.com/quote/WFC-PL/history
*Date* *Open* *High* *Low* *Close* *Volume* *Unadj* *Div Adj* *Split Adj*
*Dividend* *Split*
2017-05-19 $1,269.0000 $1,272.9700 $1,272.9700 $1,272.9700 3555 $1,272.9700
1.00000 1.00000
Keep in mind that Yahoo may be "scrubbing&quo
going on. We know there are issues...
On Sat, May 20, 2017 at 8:05 AM, tamurphy@
...
wrote:
>
> Add my thanks to those of all the others, Randy. I've been relying on your
> add-in for many years.
>
> By way of additional debugging feedback, I notice that splits for RSP and
> BRK-B aren't handled. RSP a 4 for 1 split on on 4/27/06, and BRK-B a 50 for
> 1 split on 1/21/10.
>
> Also, no data returned for WFC series L preferred stock.
>
>
Sat May 20, 2017 12:46 pm (PDT) . Posted by:
bushpilote
Thanks Randy for the temporary fix to Yahoo's Historical Quotes.
Sat May 20, 2017 1:23 pm (PDT) . Posted by:
kennethkyim
A big THANK YOU! Randy
I use get historic prices for many spreadsheets.
You saved many hours of my time and work.
Your effort is greatly appreciated.
KY
I use get historic prices for many spreadsheets.
You saved many hours of my time and work.
Your effort is greatly appreciated.
KY
Sat May 20, 2017 1:53 pm (PDT) . Posted by:
eremon9
Thanks for your prompt response. I don't know how you do it.
I see from what you provided that the splits for RSP and BRK-B are reflected in the "Close" price, but not in the "Unadjusted close" price, which is what I was using, as I wanted historical prices unadjusted for dividends. So I gather the unadjusted closing prices ignore splits. It looks as though the adjusted closing prices do account for dividends, so I'm not sure how I'll be able to get closing prices adjusted for splits but not for dividends.
I'm now getting the same as you reported for WFC-PL—i.e., just one day.
By the way, I also got a number of inexplicable "null" returns for 6/29/16—e.g., SAN and JNJ—with data for dates before and after.
Tom
I see from what you provided that the splits for RSP and BRK-B are reflected in the "Close" price, but not in the "Unadjusted close" price, which is what I was using, as I wanted historical prices unadjusted for dividends. So I gather the unadjusted closing prices ignore splits. It looks as though the adjusted closing prices do account for dividends, so I'm not sure how I'll be able to get closing prices adjusted for splits but not for dividends.
I'm now getting the same as you reported for WFC-PL—i.e., just one day.
By the way, I also got a number of inexplicable "null" returns for 6/29/16—e.g., SAN and JNJ—with data for dates before and after.
Tom
Sat May 20, 2017 12:02 pm (PDT) . Posted by:
sdwcyberdude
Randy,
Be very careful in using Google Drive. While I am a big fan of most of what Google produces, there are flaws in Google Drive that at some point might well blow away a lot of your content (Google it - well documented issues), and you will not be able to recover it.
If you are using it, you need to maintain a separate backup on regular basis not tied to Google Drive.
While I prefer the GD pricing, I pay up for Dropbox for that reason, while has a much safer technology structure.
Scott
Be very careful in using Google Drive. While I am a big fan of most of what Google produces, there are flaws in Google Drive that at some point might well blow away a lot of your content (Google it - well documented issues), and you will not be able to recover it.
If you are using it, you need to maintain a separate backup on regular basis not tied to Google Drive.
While I prefer the GD pricing, I pay up for Dropbox for that reason, while has a much safer technology structure.
Scott
Sat May 20, 2017 12:42 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Thanks for the warning. I do use a batch file (I'm from the DOS age) that
does backups daily and weekly, but those backups were also on Google Drive.
I added a weekly batch file that will back up things locally.
On Sat, May 20, 2017 at 12:02 PM, scwalker1986@
...wrote:
>
> Be very careful in using Google Drive. While I am a big fan of most of
> what Google produces, there are flaws in Google Drive that at some point
> might well blow away a lot of your content (Google it - well documented
> issues), and you will not be able to recover it.
>
> If you are using it, you need to maintain a separate backup on regular
> basis not tied to Google Drive.
>
> While I prefer the GD pricing, I pay up for Dropbox for that reason, while
> has a much safer technology structure.
>
does backups daily and weekly, but those backups were also on Google Drive.
I added a weekly batch file that will back up things locally.
On Sat, May 20, 2017 at 12:02 PM, scwalker1986@
...wrote:
>
> Be very careful in using Google Drive. While I am a big fan of most of
> what Google produces, there are flaws in Google Drive that at some point
> might well blow away a lot of your content (Google it - well documented
> issues), and you will not be able to recover it.
>
> If you are using it, you need to maintain a separate backup on regular
> basis not tied to Google Drive.
>
> While I prefer the GD pricing, I pay up for Dropbox for that reason, while
> has a much safer technology structure.
>
Sat May 20, 2017 12:05 pm (PDT) . Posted by:
palminha
Hi Randy,
Today I try to update sheets using formulas like this:
=smfPricesByDates(Ticker, DATE(B115,B116,31)) and I get #N/A for in all formulas and files!
I have the latest version installed
Stock Market Functions add-in, Version 2.1.2017.05.03 (C:\Program Files\SMF Add-In; Windows (32-bit) NT 6.02; 9.0; ; ; 1)
What can I do or what can be the problem?
I have many sheets using this kind of formulas so it would be great to keep them in use
Thank you for you support!
Today I try to update sheets using formulas like this:
=smfPricesByDates(
I have the latest version installed
Stock Market Functions add-in, Version 2.1.2017.05.
What can I do or what can be the problem?
I have many sheets using this kind of formulas so it would be great to keep them in use
Thank you for you support!
Sat May 20, 2017 12:24 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
This has been the topic of the week, here and on other groups around the
web that use Yahoo historical quotes.
smfPricesByDates() doesn't work because RCHGetYahooQuotes() is broken --
the Yahoo CSV file is no longer usable because of Yahoo changes.
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Sat, May 20, 2017 at 12:05 PM, palminha@
...wrote:
>
> Today I try to update sheets using formulas like this:
>
> =smfPricesByDates(Ticker, DATE(B115,B116,31)) and I get #N/A for in all
> formulas and files!
>
> I have the latest version installed
> Stock Market Functions add-in, Version 2.1.2017.05.03 (C:\Program
> Files\SMF Add-In; Windows (32-bit) NT 6.02; 9.0; ; ; 1)
>
> What can I do or what can be the problem?
>
> I have many sheets using this kind of formulas so it would be great to
> keep them in use
>
>
web that use Yahoo historical quotes.
smfPricesByDates() doesn't work because RCHGetYahooQuotes() is broken --
the Yahoo CSV file is no longer usable because of Yahoo changes.
See:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32112
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/32114
On Sat, May 20, 2017 at 12:05 PM, palminha@
...wrote:
>
> Today I try to update sheets using formulas like this:
>
> =smfPricesByDates(
> formulas and files!
>
> I have the latest version installed
> Stock Market Functions add-in, Version 2.1.2017.05.
> Files\SMF Add-In; Windows (32-bit) NT 6.02; 9.0; ; ; 1)
>
> What can I do or what can be the problem?
>
> I have many sheets using this kind of formulas so it would be great to
> keep them in use
>
>
Sat May 20, 2017 1:04 pm (PDT) . Posted by:
palminha
Well Randy this very bad news for me, I have hundreds of files that use this formula, about 10 times each file.
I use this formula to get the stock quote once each fiscal year close (10 years = 10 formulas).
What suggestion do you give me?
find and replace all
smfPricesByDates
by
smfGetYahooHistory
or is there any other formula I can use to get the same data to solve this problem in a simpler and faster way
Once again thank you for you kind support and sorry for trouble you
I use this formula to get the stock quote once each fiscal year close (10 years = 10 formulas).
What suggestion do you give me?
find and replace all
smfPricesByDates
by
smfGetYahooHistory
or is there any other formula I can use to get the same data to solve this problem in a simpler and faster way
Once again thank you for you kind support and sorry for trouble you
Sat May 20, 2017 1:31 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
You should just need the new version of the add-in, either the beta version
I pointed to, or when I officially release it in a ZIP file.
smfPricesByDates() will automatically work, as it will then call the new
smfGetYahooHistory() function.
You shouldn't NEED to change any smfPricesByDates() functions for the new
updates.
But it sounds like you SHOULD change your processing. If you use
smfPricesByDates() for each individual fiscal year close, you are wasting
resources by getting that Yahoo history 10 times. Just ask for all 10 dates
at once. It should speed things up quite a bit, especially since the new
process WILL take longer.
On Sat, May 20, 2017 at 1:04 PM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Well Randy this very bad news for me, I have hundreds of files that use
> this formula, about 10 times each file.
> I use this formula to get the stock quote once each fiscal year close (10
> years = 10 formulas).
>
> What suggestion do you give me?
>
> find and replace all
>
> smfPricesByDates
>
> by
>
> smfGetYahooHistory
>
> or is there any other formula I can use to get the same data to solve this
> problem in a simpler and faster way
>
> Once again thank you for you kind support and sorry for trouble you
>
>
I pointed to, or when I officially release it in a ZIP file.
smfPricesByDates() will automatically work, as it will then call the new
smfGetYahooHistory() function.
You shouldn't NEED to change any smfPricesByDates() functions for the new
updates.
But it sounds like you SHOULD change your processing. If you use
smfPricesByDates() for each individual fiscal year close, you are wasting
resources by getting that Yahoo history 10 times. Just ask for all 10 dates
at once. It should speed things up quite a bit, especially since the new
process WILL take longer.
On Sat, May 20, 2017 at 1:04 PM, palminha@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Well Randy this very bad news for me, I have hundreds of files that use
> this formula, about 10 times each file.
> I use this formula to get the stock quote once each fiscal year close (10
> years = 10 formulas).
>
> What suggestion do you give me?
>
> find and replace all
>
> smfPricesByDates
>
> by
>
> smfGetYahooHistory
>
> or is there any other formula I can use to get the same data to solve this
> problem in a simpler and faster way
>
> Once again thank you for you kind support and sorry for trouble you
>
>
Sat May 20, 2017 1:44 pm (PDT) . Posted by:
palminha
OK Randy thank you very much I'm already using the beta version and it works perfectly!
As for your suggestion not to use the formula 10 times, how can I do this if I need the 10 values one for each column per year as you can see in the attached table bellow:
As for your suggestion not to use the formula 10 times, how can I do this if I need the 10 values one for each column per year as you can see in the attached table bellow:
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar