10 Messages
Digest #4144
Messages
Mon Sep 11, 2017 9:00 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Please show me one of the formulas you're using that is incorrect...
On Mon, Sep 11, 2017 at 11:26 AM, zabrams@
...
wrote:
> I am using the get Yahoo! History to get monthly data. I am trying to get
> the adjusted close, but when I look at the data, it's giving me the actual
> close. I have tried each combination of "C" or "A" for the data items
> and "1" or "0" for the Adjust line.
>
> I looked through the message boards and could not find a solution, but
> maybe I missed something.
>
> Any help would be appreciated. As always, thanks for all your help and
> work on this.
>
>
>
On Mon, Sep 11, 2017 at 11:26 AM, zabrams@
...
wrote:
> I am using the get Yahoo! History to get monthly data. I am trying to get
> the adjusted close, but when I look at the data, it's giving me the actual
> close. I have tried each combination of "C" or "A" for the data items
> and "1" or "0" for the Adjust line.
>
> I looked through the message boards and could not find a solution, but
> maybe I missed something.
>
> Any help would be appreciated. As always, thanks for all your help and
> work on this.
>
>
>
Tue Sep 12, 2017 11:32 am (PDT) . Posted by:
buckleca
Hi Randy
I've done more research into why this is happening.
I think by default your add-on calls XMLHTTP "Get" Request. There's options to call
1 = IE Object Request
2 = HTMLDocument Request
3 = XMLHTTP "Post" Request
I have a series of functions that have always worked with the default. Not that long ago it started to return 'error'.
I discovered that when I run the URL in the IE internal (data from web) and then close, then recalc - all the functions work as they should.
I started to test trying different "types", like 1 or 2
smfGetTagContent($C$7,$C$8,B12,$C$9,,,,,,2)
This seemed to work, but what I've noticed now is if I just do the first formula with type 2 (as above) - all the other default formulas now work. If the first formula is set to the default (type 0) then all fail.
So I'm assuming that if the first call is to XMLHTTP it fails for some reason (maybe access denied), then calling the first one only with HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie settings).
Thanks
I've done more research into why this is happening.
I think by default your add-on calls XMLHTTP "Get" Request. There's options to call
1 = IE Object Request
2 = HTMLDocument Request
3 = XMLHTTP "Post" Request
I have a series of functions that have always worked with the default. Not that long ago it started to return 'error&#
I discovered that when I run the URL in the IE internal (data from web) and then close, then recalc - all the functions work as they should.
I started to test trying different "types"
smfGetTagContent(
This seemed to work, but what I've noticed now is if I just do the first formula with type 2 (as above) - all the other default formulas now work. If the first formula is set to the default (type 0) then all fail.
So I'm assuming that if the first call is to XMLHTTP it fails for some reason (maybe access denied), then calling the first one only with HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie settings).
Thanks
Tue Sep 12, 2017 1:31 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Some sites, like M*, used to redirect URLs on the first visit of the day.
Mostly for advertising. The only way to clear that redirection was to get a
cookie that indicated the first visit of the day had occurred. Since
XMLHTTP just retrieves source code and doesn't do scripting, that cookie
never got created/updated. So I would just use the EXCEL Web Query dialog
(alt+d+d+w) to visit the web site and get that cookie established, cancel
the dialog, and run the smfForceRecalculation macro to force the add-in to
retrieve the web page again, post-cookie.
It could be you are running into something similar.
On Tue, Sep 12, 2017 at 11:32 AM, buckleca@
...
wrote:
>
> I've done more research into why this is happening.
>
> I think by default your add-on calls XMLHTTP "Get" Request. There's
> options to call
>
> 1 = IE Object Request
> 2 = HTMLDocument Request
> 3 = XMLHTTP "Post" Request
>
> I have a series of functions that have always worked with the default. Not
> that long ago it started to return 'error'.
>
> I discovered that when I run the URL in the IE internal (data from web)
> and then close, then recalc - all the functions work as they should.
>
> I started to test trying different "types", like 1 or 2
>
> smfGetTagContent($C$7,$C$8,B12,$C$9,,,,,,2)
>
> This seemed to work, but what I've noticed now is if I just do the first
> formula with type 2 (as above) - all the other default formulas now work.
> If the first formula is set to the default (type 0) then all fail.
>
> So I'm assuming that if the first call is to XMLHTTP it fails for some
> reason (maybe access denied), then calling the first one only with
> HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
>
> Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie
> settings).
>
>
Mostly for advertising. The only way to clear that redirection was to get a
cookie that indicated the first visit of the day had occurred. Since
XMLHTTP just retrieves source code and doesn't do scripting, that cookie
never got created/updated. So I would just use the EXCEL Web Query dialog
(alt+d+d+w) to visit the web site and get that cookie established, cancel
the dialog, and run the smfForceRecalculati
retrieve the web page again, post-cookie.
It could be you are running into something similar.
On Tue, Sep 12, 2017 at 11:32 AM, buckleca@
...
wrote:
>
> I've done more research into why this is happening.
>
> I think by default your add-on calls XMLHTTP "Get" Request. There's
> options to call
>
> 1 = IE Object Request
> 2 = HTMLDocument Request
> 3 = XMLHTTP "Post" Request
>
> I have a series of functions that have always worked with the default. Not
> that long ago it started to return 'error&#
>
> I discovered that when I run the URL in the IE internal (data from web)
> and then close, then recalc - all the functions work as they should.
>
> I started to test trying different "types"
>
> smfGetTagContent(
>
> This seemed to work, but what I've noticed now is if I just do the first
> formula with type 2 (as above) - all the other default formulas now work.
> If the first formula is set to the default (type 0) then all fail.
>
> So I'm assuming that if the first call is to XMLHTTP it fails for some
> reason (maybe access denied), then calling the first one only with
> HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
>
> Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie
> settings).
>
>
Tue Sep 12, 2017 3:08 pm (PDT) . Posted by:
buckleca
Thanks Randy for the explanation.
If I have the first function to call to IE or HTMLDocument then I don't seem to have to go through the (alt+d+d+w) - so that's an easier workaround for me.
One thing that I'm not clear on is XMLHTTP always seemed to work with the cookie in the past - so what you're saying is that it's not handling the first cookie that's now required because it doesn't do any scripting. Correct?
But IE or HTMLDocument do the scripting that's needed and when XMLHTTP gets called on the next run - it's clear sailing.
Thanks for the education and providing all the options with the add-in.
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Some sites, like M*, used to redirect URLs on the first visit of the day. Mostly for advertising. The only way to clear that redirection was to get a cookie that indicated the first visit of the day had occurred. Since XMLHTTP just retrieves source code and doesn't do scripting, that cookie never got created/updated. So I would just use the EXCEL Web Query dialog (alt+d+d+w) to visit the web site and get that cookie established, cancel the dialog, and run the smfForceRecalculation macro to force the add-in to retrieve the web page again, post-cookie.
It could be you are running into something similar.
On Tue, Sep 12, 2017 at 11:32 AM, buckleca@ ...
wrote:
I've done more research into why this is happening.
I think by default your add-on calls XMLHTTP "Get" Request. There's options to call
1 = IE Object Request
2 = HTMLDocument Request
3 = XMLHTTP "Post" Request
I have a series of functions that have always worked with the default. Not that long ago it started to return 'error'.
I discovered that when I run the URL in the IE internal (data from web) and then close, then recalc - all the functions work as they should.
I started to test trying different "types", like 1 or 2
smfGetTagContent($C$7,$C$8, B12,$C$9,,,,,,2)
This seemed to work, but what I've noticed now is if I just do the first formula with type 2 (as above) - all the other default formulas now work. If the first formula is set to the default (type 0) then all fail.
So I'm assuming that if the first call is to XMLHTTP it fails for some reason (maybe access denied), then calling the first one only with HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie settings).
If I have the first function to call to IE or HTMLDocument then I don't seem to have to go through the (alt+d+d+w) - so that's an easier workaround for me.
One thing that I'm not clear on is XMLHTTP always seemed to work with the cookie in the past - so what you're saying is that it's not handling the first cookie that's now required because it doesn't do any scripting. Correct?
But IE or HTMLDocument do the scripting that's needed and when XMLHTTP gets called on the next run - it's clear sailing.
Thanks for the education and providing all the options with the add-in.
---In smf_addin@yahoogroups.com, <rharmelink@
Some sites, like M*, used to redirect URLs on the first visit of the day. Mostly for advertising. The only way to clear that redirection was to get a cookie that indicated the first visit of the day had occurred. Since XMLHTTP just retrieves source code and doesn't do scripting, that cookie never got created/updated. So I would just use the EXCEL Web Query dialog (alt+d+d+w) to visit the web site and get that cookie established, cancel the dialog, and run the smfForceRecalculati
It could be you are running into something similar.
On Tue, Sep 12, 2017 at 11:32 AM, buckleca@ ...
wrote:
I've done more research into why this is happening.
I think by default your add-on calls XMLHTTP "Get" Request. There's options to call
1 = IE Object Request
2 = HTMLDocument Request
3 = XMLHTTP "Post" Request
I have a series of functions that have always worked with the default. Not that long ago it started to return 'error&#
I discovered that when I run the URL in the IE internal (data from web) and then close, then recalc - all the functions work as they should.
I started to test trying different "types"
smfGetTagContent(
This seemed to work, but what I've noticed now is if I just do the first formula with type 2 (as above) - all the other default formulas now work. If the first formula is set to the default (type 0) then all fail.
So I'm assuming that if the first call is to XMLHTTP it fails for some reason (maybe access denied), then calling the first one only with HTMLDocument (2) resolves the issue and all the XMLHTTP formulas work.
Could this be a cookie issue with XMLHTTP (assume it uses IE's cookie settings).
Tue Sep 12, 2017 3:56 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Tue, Sep 12, 2017 at 3:08 PM, buckleca@
...
wrote:
>
> If I have the first function to call to IE or HTMLDocument then I don't
> seem to have to go through the (alt+d+d+w) - so that's an easier workaround
> for me.
>
Yes, that seems like it should work. I used to have the add-in do it
automatically for M*, but removed the code when the IE and HTMLDocument
objects were't working the same for everyone.
HOWEVER, you have to make sure your IE or HTMLDocument formula is done
first. In order to ensure that, you'd need to do it before you open your
workbook with the XMLHTTP retrievals. Otherwise, you'd need a way to force
EXCEL to do the IE/HTMLDocument formula first, before all the others.
Physical location of the formulas in the workbook is no guarantee of the
order they are done in.
One thing that I'm not clear on is XMLHTTP always seemed to work with the
> cookie in the past - so what you're saying is that it's not handling the
> first cookie that's now required because it doesn't do any scripting.
> Correct?
>
Correct. XMLHTTP just retrieves the source code of the web page. Both of
the other two options retrieve the source code and then build the HTML page
based on the source code. That includes running scripts, retrieving images,
the whole shebang. So it can be slower.
And, it can be unreliable between differing permutations of versions of
EXCEL, versions of IE, and versions of the operating system. I learned
that the hard way early on, and is one reason why XMLHTTP is used. But if
it is working on your machine, go for it. But keep in mind that your
process may not work on another machine, where the mix of software versions
might be different.
> But IE or HTMLDocument do the scripting that's needed and when XMLHTTP
> gets called on the next run - it's clear sailing.
>
With the above caveats, yes.
...
wrote:
>
> If I have the first function to call to IE or HTMLDocument then I don't
> seem to have to go through the (alt+d+d+w) - so that's an easier workaround
> for me.
>
Yes, that seems like it should work. I used to have the add-in do it
automatically for M*, but removed the code when the IE and HTMLDocument
objects were't working the same for everyone.
HOWEVER, you have to make sure your IE or HTMLDocument formula is done
first. In order to ensure that, you'd need to do it before you open your
workbook with the XMLHTTP retrievals. Otherwise, you'd need a way to force
EXCEL to do the IE/HTMLDocument formula first, before all the others.
Physical location of the formulas in the workbook is no guarantee of the
order they are done in.
One thing that I'm not clear on is XMLHTTP always seemed to work with the
> cookie in the past - so what you're saying is that it's not handling the
> first cookie that's now required because it doesn't do any scripting.
> Correct?
>
Correct. XMLHTTP just retrieves the source code of the web page. Both of
the other two options retrieve the source code and then build the HTML page
based on the source code. That includes running scripts, retrieving images,
the whole shebang. So it can be slower.
And, it can be unreliable between differing permutations of versions of
EXCEL, versions of IE, and versions of the operating system. I learned
that the hard way early on, and is one reason why XMLHTTP is used. But if
it is working on your machine, go for it. But keep in mind that your
process may not work on another machine, where the mix of software versions
might be different.
> But IE or HTMLDocument do the scripting that's needed and when XMLHTTP
> gets called on the next run - it's clear sailing.
>
With the above caveats, yes.
Tue Sep 12, 2017 4:19 pm (PDT) . Posted by:
buckleca
I could search on Google until I'm blue in the face and not get that explanation. Excellent and thanks.
You're a Master
You're a Master
Tue Sep 12, 2017 12:54 pm (PDT) . Posted by:
tonyestep
In the case of Yahoo, it appears that their database just didn't update correctly, and it will probably be ok tomorrow.
However, in the case of Google: they may have changed their access method. For the past several days the SMF call has returned a date series missing some days. Now it returns Nothing. No error, just emptiness. I am trying to figure out where their data has gone but so far no joy.
Tony
However, in the case of Google: they may have changed their access method. For the past several days the SMF call has returned a date series missing some days. Now it returns Nothing. No error, just emptiness. I am trying to figure out where their data has gone but so far no joy.
Tony
Tue Sep 12, 2017 2:24 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The Google Finance "Historical Quotes" link on the main pages for stocks
disappeared at the beginning of the month...
On Tue, Sep 12, 2017 at 12:54 PM, tonyestep@
...
wrote:
> In the case of Yahoo, it appears that their database just didn't update
> correctly, and it will probably be ok tomorrow.
>
> However, in the case of Google: they may have changed their access method.
> For the past several days the SMF call has returned a date series missing
> some days. Now it returns Nothing. No error, just emptiness. I am trying to
> figure out where their data has gone but so far no joy.
>
>
>
disappeared at the beginning of the month...
On Tue, Sep 12, 2017 at 12:54 PM, tonyestep@
...
wrote:
> In the case of Yahoo, it appears that their database just didn't update
> correctly, and it will probably be ok tomorrow.
>
> However, in the case of Google: they may have changed their access method.
> For the past several days the SMF call has returned a date series missing
> some days. Now it returns Nothing. No error, just emptiness. I am trying to
> figure out where their data has gone but so far no joy.
>
>
>
Tue Sep 12, 2017 2:37 pm (PDT) . Posted by:
tonyestep
Yep, but you can still get Google history via something like this:
https://www.google.com/finance/historical?q=NYSEARCA:GLD https://www.google.com/finance/historical?q=NYSEARCA:GLD
However, the price series that it returns today is missing prices for a couple of recent days. It has today's but not for Sep 6 & 7.
Meanwhile, Yahoo returns the prices for the recent 12 months including today but missing yesterday.
https://www.google.com/finance/historical?q=NYSEARCA:GLD https://www.google.com/finance/historical?q=NYSEARCA:GLD
However, the price series that it returns today is missing prices for a couple of recent days. It has today's but not for Sep 6 & 7.
Meanwhile, Yahoo returns the prices for the recent 12 months including today but missing yesterday.
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar