7 Messages
Digest #4059
Messages
Thu Jun 15, 2017 12:36 am (PDT) . Posted by:
msimmsx
I see we have smfGetYahooHistory, RCHGetYahooHistory, RCHGetYahooHistory2.
Which is the function with the latest JSON format compatibility ?
Which is the function with the latest JSON format compatibility ?
Thu Jun 15, 2017 2:00 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
smfGetYahooHistory() is what I would call the new standard function. It
extracts the data from the JSON data coded on the web page. No cookie. No
crumb.
RCHGetYahooHistory() is just an attempt to allow people backward
compatibility. It calls smfGetYahooHistory() and should work for most uses
RCHGetYahooHistory() was used for. But not all.
RCHGetYahooHistory2() uses the cookie and crumb method to get the CSV file
from Yahoo. It can be faster, but also fails often enough that I notice it.
And it does 5 tries to get the CSV file!
My version of the add-in also has smfGetGoogleHistoryCSV(),
smfGetMorningstarHistoryCSV(), and smfGetBarchartHistoryCSV() -- just
bare-boned functions to get historical quote CSV files from other sources.
They are more or less just front ends for a call to smfGetCSVFile(). I may
even obsolete them all create a generic smfGetHistoricalQuotes() function
that has a parameter specifying which source to retrieve the data from.
When I'm done, the smfGetYahooHistory() may be the only function of those
with documentation on the web site. All the other functions are there as
backups or for backward compatibility, and provide data "as is" from the
sourced web site.
On Thu, Jun 15, 2017 at 12:36 AM, marksimms@
...
wrote:
>
> I see we have smfGetYahooHistory, RCHGetYahooHistory, RCHGetYahooHistory2.
>
> Which is the function with the latest JSON format compatibility ?
>
>
>
extracts the data from the JSON data coded on the web page. No cookie. No
crumb.
RCHGetYahooHistory(
compatibility. It calls smfGetYahooHistory(
RCHGetYahooHistory(
RCHGetYahooHistory2
from Yahoo. It can be faster, but also fails often enough that I notice it.
And it does 5 tries to get the CSV file!
My version of the add-in also has smfGetGoogleHistory
smfGetMorningstarHi
bare-boned functions to get historical quote CSV files from other sources.
They are more or less just front ends for a call to smfGetCSVFile(
even obsolete them all create a generic smfGetHistoricalQuo
that has a parameter specifying which source to retrieve the data from.
When I'm done, the smfGetYahooHistory(
with documentation on the web site. All the other functions are there as
backups or for backward compatibility, and provide data "as is" from the
sourced web site.
On Thu, Jun 15, 2017 at 12:36 AM, marksimms@
...
wrote:
>
> I see we have smfGetYahooHistory, RCHGetYahooHistory, RCHGetYahooHistory2
>
> Which is the function with the latest JSON format compatibility ?
>
>
>
Thu Jun 15, 2017 7:01 pm (PDT) . Posted by:
tonyestep
In line with Randy's comments in the previous post, I downloaded the beta version and tried it, only to discover that sometimes it refuses to get anything. Sometimes it works okay, but sometimes pressing Ctrl-Alt-F9 does not make the sheet recalc; it just sits there defiantly. So I went back to the previous version.
Thu Jun 15, 2017 5:43 am (PDT) . Posted by:
rimanda1
Randy,
I am using the following call:
=RCHGetYahooHistory("aapl";, 2016, 6,30,2016,7, 4,"d")
It returns the following:
Error Starting date can not be after ending date:6/30/2016,7/4/2016
In the function smfGetYahooHistory there is a test on the VarType of pStartDate
Case VarType(pStartDate) = vbDate Or VarType(pStartDate) = vbDouble
In my case, the Vartype for both pStartDate and pEndDate is 8 so dBegin and dEnd are evaluated in the Case Else statements resulting in the error.
I am in Australia so perhaps our dd/mm/yy format may be contributing, or perhaps I am using the wrong version Version 2.1.2017.06.09?
Thanks in advance
David
I am using the following call:
=RCHGetYahooHistory
It returns the following:
Error Starting date can not be after ending date:6/30/2016,
In the function smfGetYahooHistory there is a test on the VarType of pStartDate
Case VarType(pStartDate) = vbDate Or VarType(pStartDate) = vbDouble
In my case, the Vartype for both pStartDate and pEndDate is 8 so dBegin and dEnd are evaluated in the Case Else statements resulting in the error.
I am in Australia so perhaps our dd/mm/yy format may be contributing, or perhaps I am using the wrong version Version 2.1.2017.06.
Thanks in advance
David
Thu Jun 15, 2017 8:42 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I cut and pasted the formula, and it works fine here. I think you
identified the problem. VBA does date and numeric processing based on your
computer's regional setting.
On Thu, Jun 15, 2017 at 5:43 AM, rimanda1@
...
wrote:
> I am using the following call:
>
> =RCHGetYahooHistory("aapl";, 2016, 6,30,2016,7, 4,"d")
>
> It returns the following:
>
> Error Starting date can not be after ending date:6/30/2016,7/4/2016
>
> In the function smfGetYahooHistory there is a test on the VarType of
> pStartDate
>
> Case VarType(pStartDate) = vbDate Or VarType(pStartDate) = vbDouble
>
> In my case, the Vartype for both pStartDate and pEndDate is 8 so dBegin
> and dEnd are evaluated in the Case Else statements resulting in the error.
>
> I am in Australia so perhaps our dd/mm/yy format may be contributing, or
> perhaps I am using the wrong version Version 2.1.2017.06.09?
>
>
>
identified the problem. VBA does date and numeric processing based on your
computer'
On Thu, Jun 15, 2017 at 5:43 AM, rimanda1@
...
wrote:
> I am using the following call:
>
> =RCHGetYahooHistory
>
> It returns the following:
>
> Error Starting date can not be after ending date:6/30/2016,
>
> In the function smfGetYahooHistory there is a test on the VarType of
> pStartDate
>
> Case VarType(pStartDate) = vbDate Or VarType(pStartDate) = vbDouble
>
> In my case, the Vartype for both pStartDate and pEndDate is 8 so dBegin
> and dEnd are evaluated in the Case Else statements resulting in the error.
>
> I am in Australia so perhaps our dd/mm/yy format may be contributing, or
> perhaps I am using the wrong version Version 2.1.2017.06.
>
>
>
Thu Jun 15, 2017 10:34 am (PDT) . Posted by:
joelfp2000
Randy,
Thanks again for all of your help.
I can't figure this one out. How do I identify a symbol as being a mutual fund or an ETF?. As an example, if an input symbol in my spreadsheet is a mutual fund I want to direct excel to run one set of analysis and if the symbol is an ETF I want excel can run a different set of analysis. I can't seem to find in Morningstar any indicator that defines the type of product the symbol represents.
Any help would be greatly appreciated!
Thanks,
Joel
Thanks again for all of your help.
I can't figure this one out. How do I identify a symbol as being a mutual fund or an ETF?. As an example, if an input symbol in my spreadsheet is a mutual fund I want to direct excel to run one set of analysis and if the symbol is an ETF I want excel can run a different set of analysis. I can't seem to find in Morningstar any indicator that defines the type of product the symbol represents.
Any help would be greatly appreciated!
Thanks,
Joel
Thu Jun 15, 2017 1:22 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
I would think the ticker symbol itself would tell you? Aren't all mutual
fund symbols five characters, and end with an "X"?
Or, POSSIBLY, use the Morningstar JSON file for their auto-complete
process, when you enter a ticker symbol:
=smfJSONExtractField(RCHGetWebData("
http://www.morningstar.com/api/v1/autocomplete/12/us/
"&LOWER($D7)),"m.0.r.0.OS010")
I tried this on a number of ticker symbols, and it appears the returned
values were::
-- ST = Stock?
-- FE = ETF?
-- FO = Fund?
...but I can't be sure. And there probably are additional values?
On Thu, Jun 15, 2017 at 10:34 AM, joel.andrews@
...
wrote:
> I can't figure this one out. How do I identify a symbol as being a mutual
> fund or an ETF?. As an example, if an input symbol in my spreadsheet is a
> mutual fund I want to direct excel to run one set of analysis and if the
> symbol is an ETF I want excel can run a different set of analysis. I can't
> seem to find in Morningstar any indicator that defines the type of product
> the symbol represents.
>
> Any help would be greatly appreciated!
>
>
>
fund symbols five characters, and end with an "X"?
Or, POSSIBLY, use the Morningstar JSON file for their auto-complete
process, when you enter a ticker symbol:
=smfJSONExtractField(RCHGetWebData("
http://www.morningstar.com/api/v1/autocomplete/12/us/
"&LOWER(
I tried this on a number of ticker symbols, and it appears the returned
values were::
-- ST = Stock?
-- FE = ETF?
-- FO = Fund?
...but I can't be sure. And there probably are additional values?
On Thu, Jun 15, 2017 at 10:34 AM, joel.andrews@
...
wrote:
> I can't figure this one out. How do I identify a symbol as being a mutual
> fund or an ETF?. As an example, if an input symbol in my spreadsheet is a
> mutual fund I want to direct excel to run one set of analysis and if the
> symbol is an ETF I want excel can run a different set of analysis. I can't
> seem to find in Morningstar any indicator that defines the type of product
> the symbol represents.
>
> Any help would be greatly appreciated!
>
>
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar