15 Messages
Digest #4310
1a
Re: RchGetYahooHistory -> Error: Starting date cannot be after endin by "Guy" descormiersg@videotron.ca
1b
Re: RchGetYahooHistory -> Error: Starting date cannot be after endin by "Randy Harmelink" rharmelink
1e
Re: RchGetYahooHistory -> Error: Starting date cannot be after endin by "Randy Harmelink" rharmelink
Messages
Thu Mar 1, 2018 11:16 am (PST) . Posted by:
"Guy" descormiersg@videotron.ca
Thanks
How do I install USA regional settings on my system ?
Thanks again
Guy
From: Randy Harmelink rharmelink@gmail.com [smf_addin]
Sent: Thursday, March 1, 2018 11:40 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] RchGetYahooHistory -> Error: Starting date cannot be after ending date: 1/11/2018,2/28/2018
Like I said, it probably has to do with VBA date processing. It needs USA regional settings on your system.
On Thu, Mar 1, 2018 at 8:26 AM, 'Guy' descormiersg@videotron.ca [smf_addin] <smf_addin@yahoogroups.com> wrote:
I have the same problem. I put the RchGetYahooHistory in a matrice. Result : No data at all. Is it the Version ? I am using this version 2.1.2018.24(C:\SMF add-in;Windows(32-bits) NT 10.00;16.0;;;33)
Thanks in advance for your help.
How do I install USA regional settings on my system ?
Thanks again
Guy
From: Randy Harmelink rharmelink@gmail.com [smf_addin]
Sent: Thursday, March 1, 2018 11:40 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] RchGetYahooHistory -> Error: Starting date cannot be after ending date: 1/11/2018,2/28/2018
Like I said, it probably has to do with VBA date processing. It needs USA regional settings on your system.
On Thu, Mar 1, 2018 at 8:26 AM, 'Guy' descormiersg@videotron.ca [smf_addin] <smf_addin@yahoogroups.com> wrote:
I have the same problem. I put the RchGetYahooHistory in a matrice. Result : No data at all. Is it the Version ? I am using this version 2.1.2018.24(
Thanks in advance for your help.
Thu Mar 1, 2018 1:26 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's not something you install. It's in your Window settings, and you may
not want to do it.
How to do it varies by operating system. It's within your Control Panel..
I've never done anything but USA, so I have no idea what complications it
might cause for you.
On Thu, Mar 1, 2018 at 12:16 PM, 'Guy' descormiersg@
...
wrote:
>
> How do I install USA regional settings on my system ?
>
>
>
not want to do it.
How to do it varies by operating system. It's within your Control Panel..
I've never done anything but USA, so I have no idea what complications it
might cause for you.
On Thu, Mar 1, 2018 at 12:16 PM, 'Guy' descormiersg@
...
wrote:
>
> How do I install USA regional settings on my system ?
>
>
>
Sat Mar 3, 2018 12:20 pm (PST) . Posted by:
alberto.ratti
Randy,
Please, consider to make your extraordinary application independent from USA Regional Settings.
Have a look at https://bettersolutions.com/vba/macros/region-language.htm:
"…
Date Literals
Date literals ignore the WRS [Windows Regional Settings] and are always in US format (mm/dd/yyyy)
When any other format different to mm/dd/yyyy is entered Excel will automatically convert it to the #mm/dd/yyyy# format.
It is much safer to avoid using date literals altogether and use VBA functions instead (DateSerial, DateValue)
…"
And all of us will deserve you our immense gratitude for your cross-country application!
Please, consider to make your extraordinary application independent from USA Regional Settings.
Have a look at https://bettersolutions.com/vba/macros/region-language.htm:
"…
Date Literals
Date literals ignore the WRS [Windows Regional Settings] and are always in US format (mm/dd/yyyy)
When any other format different to mm/dd/yyyy is entered Excel will automatically convert it to the #mm/dd/yyyy# format.
It is much safer to avoid using date literals altogether and use VBA functions instead (DateSerial, DateValue)
…"
And all of us will deserve you our immense gratitude for your cross-country application!
Sun Mar 4, 2018 12:09 pm (PST) . Posted by:
alberto.ratti
I have circumvented the problem by using "smfGetYahooHistory" module directly, instead of "RCHGetYahooHistory" (see attached template).
"smfGetYahooHistory" expects a "date" and I provide the function call with DATE(Year, Month, Day), that is a locale-independent code.
And now I am relieved not expecting RANDY to work around an international implementation. Take things easy, Randy!
"smfGetYahooHistory" expects a "date" and I provide the function call with DATE(Year, Month, Day), that is a locale-independent code.
And now I am relieved not expecting RANDY to work around an international implementation. Take things easy, Randy!
Attachment(s) from
1 of 1 File(s)
Sun Mar 4, 2018 12:21 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Glad to hear that works for you. If you pass it an EXCEL serial date, it
skips the step of having to convert a text date into a serial date, which
is where the problem was coming up, because of the "mm/dd/yy" versus
"dd/mm/yy" text variations.
It didn't used to work for the old Yahoo, because their CSV file contained
dates as text, so the conversion would fail. Now that they are using a JSON
file instead of a CSV file, they have the dates as UNIX values. Those can
be directly converted into an EXCEL serial date, without worrying about
which international text format would be used. The bad thing right now is
that I use the UNIX dates "as is", but they should use a GMT offset. Not an
issue for the USA, but I know it is for Australians, who are in a far
different time zone from GMT.
I'm always hesitant about adding code that I wouldn't use regularly, or
requires unusual settings for me to test. I'm just one person sharing
something I wrote for my own use. Not a corporate staff supporting a
commercial product that should test all the permutations of different
versions of operating systems, EXCEL, IE, and regions. I just don't have
the time, or the inclination. Sorry.
On Sun, Mar 4, 2018 at 1:09 PM, alberto.ratti@
...
wrote:
>
> I have circumvented the problem by using "smfGetYahooHistory" module
> directly, instead of "RCHGetYahooHistory" (see attached template).
>
> "smfGetYahooHistory" expects a "date" and I provide the function call with
> DATE(Year, Month, Day), that is a locale-independent code.
>
> And now I am relieved not expecting RANDY to work around an international
> implementation. Take things easy, Randy!
>
skips the step of having to convert a text date into a serial date, which
is where the problem was coming up, because of the "mm/dd/
"dd/mm/
It didn't used to work for the old Yahoo, because their CSV file contained
dates as text, so the conversion would fail. Now that they are using a JSON
file instead of a CSV file, they have the dates as UNIX values. Those can
be directly converted into an EXCEL serial date, without worrying about
which international text format would be used. The bad thing right now is
that I use the UNIX dates "as is", but they should use a GMT offset. Not an
issue for the USA, but I know it is for Australians, who are in a far
different time zone from GMT.
I'm always hesitant about adding code that I wouldn't use regularly, or
requires unusual settings for me to test. I'm just one person sharing
something I wrote for my own use. Not a corporate staff supporting a
commercial product that should test all the permutations of different
versions of operating systems, EXCEL, IE, and regions. I just don't have
the time, or the inclination. Sorry.
On Sun, Mar 4, 2018 at 1:09 PM, alberto.ratti@
...
wrote:
>
> I have circumvented the problem by using "smfGetYahooHistory" module
> directly, instead of "RCHGetYahooHistory" (see attached template).
>
> "smfGetYahooHistory" expects a "date" and I provide the function call with
> DATE(Year, Month, Day), that is a locale-independent code.
>
> And now I am relieved not expecting RANDY to work around an international
> implementation. Take things easy, Randy!
>
Fri Mar 2, 2018 9:55 am (PST) . Posted by:
gpommeranz1
Hi All,
I'm using smfGetYahooHistory and smfGetCSVFile (using Morningstar financial data) and have run into problems. My spreadsheet is such that I enter a ticker symbol and it gets passed into an array entered formula to pull in the price history and financial data. The problem is that after about 2 ticker symbol searches, the formulas don't want to return data. Is there a limit on how much or how often the data can be retrieved?
I'm using smfGetYahooHistory and smfGetCSVFile (using Morningstar financial data) and have run into problems. My spreadsheet is such that I enter a ticker symbol and it gets passed into an array entered formula to pull in the price history and financial data. The problem is that after about 2 ticker symbol searches, the formulas don't want to return data. Is there a limit on how much or how often the data can be retrieved?
Fri Mar 2, 2018 10:53 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Doesn't sound like there should be an issue. There is a limit, but it's
1000 web pages (because they are stored internally before data is extracted
from them, and I only set up 1000 slots for saving the web pages).
If you're doing one ticker at a time, you could run the
smfForceRecalculation macro between each ticker symbol and see if you still
have a problem after a couple of ticker symbols.
Morningstar recently went live with their beta redesign. I'm not sure if
the CSV files will continue to be provided or not, or whether it has timing
issues. I don't like the new Morningstar web site. It takes nearly 30
seconds for a stock's web page to render properly in my browser. And to use
the web pages with the add-in, the data needs to be extracted from a
rendered page, so it can take 30 to 40 seconds to get data with the add-in.
On Fri, Mar 2, 2018 at 10:52 AM, gpommeranz1@
...
wrote:
> I'm using smfGetYahooHistory and smfGetCSVFile (using Morningstar
> financial data) and have run into problems. My spreadsheet is such that I
> enter a ticker symbol and it gets passed into an array entered formula to
> pull in the price history and financial data. The problem is that after
> about 2 ticker symbol searches, the formulas don't want to return data. Is
> there a limit on how much or how often the data can be retrieved?
>
1000 web pages (because they are stored internally before data is extracted
from them, and I only set up 1000 slots for saving the web pages).
If you're doing one ticker at a time, you could run the
smfForceRecalculati
have a problem after a couple of ticker symbols.
Morningstar recently went live with their beta redesign. I'm not sure if
the CSV files will continue to be provided or not, or whether it has timing
issues. I don't like the new Morningstar web site. It takes nearly 30
seconds for a stock's web page to render properly in my browser. And to use
the web pages with the add-in, the data needs to be extracted from a
rendered page, so it can take 30 to 40 seconds to get data with the add-in.
On Fri, Mar 2, 2018 at 10:52 AM, gpommeranz1@
...
wrote:
> I'm using smfGetYahooHistory and smfGetCSVFile (using Morningstar
> financial data) and have run into problems. My spreadsheet is such that I
> enter a ticker symbol and it gets passed into an array entered formula to
> pull in the price history and financial data. The problem is that after
> about 2 ticker symbol searches, the formulas don't want to return data. Is
> there a limit on how much or how often the data can be retrieved?
>
Fri Mar 2, 2018 12:09 pm (PST) . Posted by:
gpommeranz1
Thanks Randy,
Is the smfForceRecalculation macro different from CTRL+SHFT+ALT+F9? I've tried that to force recalculation of the workbook but it only works sometimes.
Is there a data source similar to Morningstar that is stable?
Is the smfForceRecalculati
Is there a data source similar to Morningstar that is stable?
Fri Mar 2, 2018 12:23 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
The various native methods of recalculation are worthless, because the
add-in is extracting data from a saved copy of the web page. They would
just re-extract data from the same saved copy of the web page. The macro
purges all of the saved copies of web pages so that a new one needed to be
retrieved, saved, and then extracted from.
I've been retrieving data from the Internet for several decades now. Things
can be great and then suddenly change on a dime. A lot of sources of
information have disappeared or become unusable in the last few years. From
sites I would have said were stable -- MSN, AdvFN, Yahoo, MorningStar,
Google, Zacks, OptionsXpress, ...
On Fri, Mar 2, 2018 at 1:09 PM, gpommeranz1@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Is the smfForceRecalculation macro different from CTRL+SHFT+ALT+F9? I've
> tried that to force recalculation of the workbook but it only works
> sometimes.
>
> Is there a data source similar to Morningstar that is stable?
>
>
add-in is extracting data from a saved copy of the web page. They would
just re-extract data from the same saved copy of the web page. The macro
purges all of the saved copies of web pages so that a new one needed to be
retrieved, saved, and then extracted from.
I've been retrieving data from the Internet for several decades now. Things
can be great and then suddenly change on a dime. A lot of sources of
information have disappeared or become unusable in the last few years. From
sites I would have said were stable -- MSN, AdvFN, Yahoo, MorningStar,
Google, Zacks, OptionsXpress, ...
On Fri, Mar 2, 2018 at 1:09 PM, gpommeranz1@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Is the smfForceRecalculati
> tried that to force recalculation of the workbook but it only works
> sometimes.
>
> Is there a data source similar to Morningstar that is stable?
>
>
Sat Mar 3, 2018 8:30 am (PST) . Posted by:
lm5585@sbcglobal.net
hi, I just added SMF add-in to my new Office 365 and started creating a new spreadsheet. I use the "ticker" capability and I use different tikers down column A in Excel. (I do change the formula from "ticker" the cell name - A1, A2, etc. - It worked last night and now all of my links don't work. Can't figure out how to get it to link back up. What happened???
Sun Mar 4, 2018 12:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I would need more details. Are formulas entered in new worksheets not
working, or just ones you're opening that were previously saved?
If the latter, you may have an "unresolved link" issue. It happens when the
add-in is in a different location than when the workbook was saved (because
EXCEL saves a hard-code location of the add-in when it saves the workbook).
This can be easily fixed by using the smfFixLinks macro from the add-in.
And *once the workbook is saved*, it shouldn't need to be done again *for
that workbook* as EXCEL will save it with the new location of the add-in.
On Sat, Mar 3, 2018 at 9:30 AM, lm5585@
...
wrote:
>
> hi, I just added SMF add-in to my new Office 365 and started creating a
> new spreadsheet. I use the "ticker" capability and I use different tikers
> down column A in Excel. (I do change the formula from "ticker" the cell
> name - A1, A2, etc. - It worked last night and now all of my links don't
> work. Can't figure out how to get it to link back up. What happened???
>
>
>
working, or just ones you're opening that were previously saved?
If the latter, you may have an "unresolved link" issue. It happens when the
add-in is in a different location than when the workbook was saved (because
EXCEL saves a hard-code location of the add-in when it saves the workbook).
This can be easily fixed by using the smfFixLinks macro from the add-in.
And *once the workbook is saved*, it shouldn'
that workbook* as EXCEL will save it with the new location of the add-in.
On Sat, Mar 3, 2018 at 9:30 AM, lm5585@
...
wrote:
>
> hi, I just added SMF add-in to my new Office 365 and started creating a
> new spreadsheet. I use the "ticker" capability and I use different tikers
> down column A in Excel. (I do change the formula from "ticker" the cell
> name - A1, A2, etc. - It worked last night and now all of my links don't
> work. Can't figure out how to get it to link back up. What happened???
>
>
>
Sat Mar 3, 2018 8:35 am (PST) . Posted by:
petekoch
Yep. I changed all iterations of "http:" to "https:" in my sheet and everything worked.
Mon Mar 5, 2018 12:59 pm (PST) . Posted by:
codyklein
Did the smfGetGuruFocusCSVItem function stop working? My sheet stopped returning any data. It's been working up through at least last week.
Mon Mar 5, 2018 1:26 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I just grabbed the template from the website and tried it with tickers MMM
and MCD. Appears to be working fine here.
Maybe your security cookie expired? Or maybe GuruFocus was down for a bit?
On Mon, Mar 5, 2018 at 1:59 PM, codyklein@
...
wrote:
> Did the smfGetGuruFocusCSVItem function stop working? My sheet stopped
> returning any data. It's been working up through at least last week.
>
>
>
>
and MCD. Appears to be working fine here.
Maybe your security cookie expired? Or maybe GuruFocus was down for a bit?
On Mon, Mar 5, 2018 at 1:59 PM, codyklein@
...
wrote:
> Did the smfGetGuruFocusCSVI
> returning any data. It's been working up through at least last week.
>
>
>
>
Mon Mar 5, 2018 1:39 pm (PST) . Posted by:
codyklein
I can download the CSV file so I guess that means my security cookie is good, the site seems to be working fine.
This is what I have as an example:
=smfGetGuruFocusCSVItem(Ticker,$B6,C$5) where Ticker is AAPL, $B6 is Revenue per Share and C$5 is a number between 22 and 32. Data is blank, same for any ticker. Any ideas?
This is what I have as an example:
=smfGetGuruFocusCSV
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar