Rabu, 25 Juni 2014

[smf_addin] Digest Number 3102

13 Messages

Digest #3102
1a
Morning Star by who1012000
1b
Re: Morning Star by "Randy Harmelink" rharmelink
1c
Re: Morning Star by "David" who1012000
1d
Re: Morning Star by "Randy Harmelink" rharmelink
1e
Re: Morning Star by "David" who1012000
1f
Re: Morning Star by "Randy Harmelink" rharmelink
1g
Re: Morning Star by "David" who1012000
1h
Re: Morning Star by "Randy Harmelink" rharmelink
1i
Re: Morning Star by "David" who1012000
1j
Re: Morning Star by "Randy Harmelink" rharmelink
1k
Re: Morning Star by "David" who1012000

Messages

Tue Jun 24, 2014 9:00 am (PDT) . Posted by:

who1012000

Hello,


I am going to Morning Star and looking at 10 years of data.
http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA



In the site there is a way to export the data and it works fine, but I would like to brig the data in using a query via VBA, but I was unable to accomplish this. Can anyone help me with this?


Thanks, David

Tue Jun 24, 2014 9:17 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

You'd have to better define "query via VBA".

You can use the smfGetCSVFile() function to pull in the data if you're
using the add-in, either in the worksheet, or in VBA. The definitions of
the CSV files can be found in an item on the "Tips and FAQs" web page:

http://ogres-crypt.com/SMF/Tips-and-FAQs/

On Tue, Jun 24, 2014 at 9:00 AM, who1012000@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> I am going to Morning Star and looking at 10 years of data.
>
>
> http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA
> <http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA>
>
> In the site there is a way to export the data and it works fine, but I
> would like to brig the data in using a query via VBA, but I was unable to
> accomplish this. Can anyone help me with this?
>

Tue Jun 24, 2014 9:31 am (PDT) . Posted by:

"David" who1012000

Hello,

I use queries a lot and really do not want to put the addin on my computer, so is there a way to accomplish this with out the addin?

Thanks, David

On Tuesday, June 24, 2014 9:17 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


>
>
>
>
>
>
>You'd have to better define "query via VBA".
>
>You can use the smfGetCSVFile() function to pull in the data if you're using the add-in, either in the worksheet, or in VBA. The definitions of the CSV files can be found in an item on the "Tips and FAQs" web page:
>
>http://ogres-crypt.com/SMF/Tips-and-FAQs/
>
>
>
>On Tue, Jun 24, 2014 at 9:00 AM, who1012000@yahoo.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>I am going to Morning Star and looking at 10 years of data.
>>http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA
>>
>>In the site there is a way to export the data and it works fine, but I would like to brig the data in using a query via VBA, but I was unable to accomplish this. Can anyone help me with this?
>>
>
>
>
>
>
>

Tue Jun 24, 2014 9:42 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Just do a query on the CSV files...

On Tue, Jun 24, 2014 at 9:28 AM, David who1012000@...wrote:

>
> I use queries a lot and really do not want to put the addin on my
> computer, so is there a way to accomplish this with out the addin?
>

Tue Jun 24, 2014 9:56 am (PDT) . Posted by:

"David" who1012000

Hello,

This is what I get when I copy the link "javascript:exportKeyStat2CSV();" and this is the address http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA

When I record and try and import it simply fails, but this is how I would normally get the address to do the query. 

Thanks, David
 

On Tuesday, June 24, 2014 9:42 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


>
>
>
>
>
>Just do a query on the CSV files...
>
>
>
>On Tue, Jun 24, 2014 at 9:28 AM, David who1012000@...wrote:
>
>
>>
>>I use queries a lot and really do not want to put the addin on my computer, so is there a way to accomplish this with out the addin?
>>
>
>
>
>
>
>

Tue Jun 24, 2014 10:08 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Go to the "Tips and FAQs" link I sent you. The MorningStar item on that
page has the KeyStats CSV file and explanations of how to create the CSV
file for the various financial statements.

Note that you WILL need to have an IE security cookie in place to get 10
years of data. EXCEL's queries use the same Internet engine that IE uses,
because both are MicroSoft products.

On Tue, Jun 24, 2014 at 9:53 AM, David who1012000@... wrote:

>
> This is what I get when I copy the link "javascript:exportKeyStat2CSV();"
> and this is the address
> http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA
>
> When I record and try and import it simply fails, but this is how I would
> normally get the address to do the query.
>
>

Tue Jun 24, 2014 10:18 am (PDT) . Posted by:

"David" who1012000

So I read what was there and it appears that it can not be done because it is "dynamic page." Thanks, David

On Tuesday, June 24, 2014 9:53 AM, "David who1012000@yahoo.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


>
>
>
>
>
>Hello,
>
>
>This is what I get when I copy the link "javascript:exportKeyStat2CSV();" and this is the address http://financials.morningstar.com/ratios/r.html?t=SDRL®ion=usa&culture=en-US&ownerCountry=USA
>
>
>When I record and try and import it simply fails, but this is how I would normally get the address to do the query. 
>
>
>Thanks, David
> 
>
>
>
>On Tuesday, June 24, 2014 9:42 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:
>
>
>>
>>
>>
>>
>>
>>Just do a query on the CSV files...
>>
>>
>>
>>On Tue, Jun 24, 2014 at 9:28 AM, David who1012000@...wrote:
>>
>>
>>>
>>>I use queries a lot and really do not want to put the addin on my computer, so is there a way to accomplish this with out the addin?
>>>
>>
>>
>>
>>
>>
>>
>
>
>
>

Tue Jun 24, 2014 10:30 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

No. The web page was dynamic and inaccessible to screen scraping, but the
CSV files are still available. That's why I said an "alternative" method
was available. There were even two examples given:

http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG

http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw

On Tue, Jun 24, 2014 at 10:14 AM, David who1012000@... wrote:

>
> So I read what was there and it appears that it can not be done because it
> is "dynamic page." Thanks, David
>
>

Tue Jun 24, 2014 11:29 am (PDT) . Posted by:

"David" who1012000

Randy,

Thank you. I was able to use the top link and get a cvs file via a query. If you do not mind, how did you get from the address to:

http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG

Like I said before I usually record something then pull the recording apart to create the query. 

Thanks for you help, David

On Tuesday, June 24, 2014 10:30 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


>
>
>
>
>
>No. The web page was dynamic and inaccessible to screen scraping, but the CSV files are still available. That's why I said an "alternative" method was available. There were even two examples given:
>
>http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG
>
>http://financials.morningstar.com/ajax/ReportProcess4CSV.html?t=GOOG&reportType=is&period=12&dataType=A&order=asc&columnYear=10&rounding=3&denominatorView=raw
>
>
>
>On Tue, Jun 24, 2014 at 10:14 AM, David who1012000@... wrote:
>
>
>>
>>So I read what was there and it appears that it can not be done because it is "dynamic page." Thanks, David
>>
>
>
>
>
>

Tue Jun 24, 2014 11:56 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't recall the exact process I used to get that particular file name.
It's usually one of three methods:

1. Examining the source code of the web page and associated JavaScript
files.

2. Using the "Download Progress" button in FireFox, after the fact, to
check what was actually downloaded.

3. Using HTTPFox (a FireFox add-in) to monitor which HTTP requests were
made from FireFox.

What works may depend on how the website does their processing. And, of
course, sometimes it can't be done, because the downloaded file is actually
created as part of a request that can't be duplicated by a bookmarkable URL
(which is what both my add-in and the EXCEL Web Query process need).

On Tue, Jun 24, 2014 at 11:29 AM, David who1012000@... wrote:

>
> Randy,
>
> Thank you. I was able to use the top link and get a cvs file via a query.
> If you do not mind, how did you get from the address to:
>
> http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG
>
> Like I said before I usually record something then pull the recording
> apart to create the query.
>
> Thanks for you help, David
>

Tue Jun 24, 2014 12:03 pm (PDT) . Posted by:

"David" who1012000

Thanks Randy. I guess i will just have a hard time of it sometimes, since I don't have the programs you have mentioned. I do appreciate your time and efforts.

David

On Tuesday, June 24, 2014 11:56 AM, "Randy Harmelink rharmelink@gmail.com [smf_addin]" <smf_addin@yahoogroups.com> wrote:


>
>
>
>
>
>I don't recall the exact process I used to get that particular file name. It's usually one of three methods:
>
>1. Examining the source code of the web page and associated JavaScript files.
>
>2. Using the "Download Progress" button in FireFox, after the fact, to check what was actually downloaded.
>
>3. Using HTTPFox (a FireFox add-in) to monitor which HTTP requests were made from FireFox.
>
>
>
>What works may depend on how the website does their processing. And, of course, sometimes it can't be done, because the downloaded file is actually created as part of a request that can't be duplicated by a bookmarkable URL (which is what both my add-in and the EXCEL Web Query process need).
>
>
>
>On Tue, Jun 24, 2014 at 11:29 AM, David who1012000@... wrote:
>
>
>>
>>Randy,
>>
>>
>>Thank you. I was able to use the top link and get a cvs file via a query. If you do not mind, how did you get from the address to:
>>
>>
>>http://financials.morningstar.com/ajax/exportKR2CSV.html?t=GOOG
>>
>>
>>
>>Like I said before I usually record something then pull the recording apart to create the query. 
>>
>>
>>Thanks for you help, David
>>
>
>
>
>
>

Tue Jun 24, 2014 5:32 pm (PDT) . Posted by:

"Ed Insel" insele

I've been wrestling with this problem and cannot figure out whether it's me
or the data feed. I'm using the SMFGetOptionExpirations function in an array
formula to retrieve the next fifteen expiration dates for some of my
holdings. I'll use Apple (AAPL) as an example. When I use the formula

smfGetOptionExpirations("AAPL","OX"))

I get these dates back:

06/28/18

07/04/18

07/12/18

07/20/18

07/26/18

08/02/18

08/17/18

09/21/18

10/19/18

01/18/19

04/18/19

01/16/20



It's not just four years off. I've tried correcting by subtracting (365.25
days and 4 years), (365x4), (366x4), etc. but cannot get a meaningful list.
If I was getting the right answer it would be

6/28/14

7/5/14

7/12/14

7/19/14

7/26/14

8/2/14

8/16/14

9/20/14

10/18/14

1/17/15

4/18/15

1/16/16

Has anyone else seen this problem or have other ideas I can try to fix it?
Thanks for any help! Ed

Tue Jun 24, 2014 5:33 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

I'm getting:

2014-06-27
2014-07-03
2014-07-11
2014-07-19
2014-07-25
2014-08-01
2014-08-16
2014-09-20
2014-10-18
2015-01-17
2015-04-17
2016-01-15

Based on the 4-year difference, it sounds like it has to do with EXCEL's
default of the 1900 date system vs. the 1904 Date System. That is, VBA may
be generating the dates based on the 1900 date system (i.e. number of days
since Jan 1, 1900) but EXCEL is using the 1904 date system (i.e. number of
days since Jan 1, 1904). So it's thinking the dates are 4 years into the
future.

http://support.microsoft.com/kb/214330

On Tue, Jun 24, 2014 at 5:17 PM, 'Ed Insel' insele@...wrote:

>
>
> I've been wrestling with this problem and cannot figure out whether it's
> me or the data feed. I'm using the SMFGetOptionExpirations function in an
> array formula to retrieve the next fifteen expiration dates for some of my
> holdings. I'll use Apple (AAPL) as an example. When I use the formula
>
>
>
> smfGetOptionExpirations("AAPL","OX"))
>
>
>
> I get these dates back:
>
>
>
> 06/28/18
>
> 07/04/18
>
> 07/12/18
>
> 07/20/18
>
> 07/26/18
>
> 08/02/18
>
> 08/17/18
>
> 09/21/18
>
> 10/19/18
>
> 01/18/19
>
> 04/18/19
>
> 01/16/20
>
> It's not just four years off. I've tried correcting by subtracting (365.25
> days and 4 years), (365x4), (366x4), etc. but cannot get a meaningful list.
> If I was getting the right answer it would be
>
>
>
> 6/28/14
>
> 7/5/14
>
> 7/12/14
>
> 7/19/14
>
> 7/26/14
>
> 8/2/14
>
> 8/16/14
>
> 9/20/14
>
> 10/18/14
>
> 1/17/15
>
> 4/18/15
>
> 1/16/16
>
>
>
> Has anyone else seen this problem or have other ideas I can try to fix it?
> Thanks for any help! Ed
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Diposting oleh EXCEL FEED LEARNING di 01.24

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)