Sabtu, 18 Oktober 2014

[smf_addin] Digest Number 3211

7 Messages

Digest #3211

Messages

Fri Oct 17, 2014 1:28 pm (PDT) . Posted by:

shiraegi

I am constructing my own screens in Excel, e.g.:


https://www.google.com/finance/stockscreener?&c0=BookValuePerShareYear&min0=10&c1=ReturnOnInvestment5Years&min1=10&c2=RevenueGrowthRate10Years&min2=10&c3=EPSGrowthRate10Years&min3=10 https://www.google.com/finance/stockscreener?&c0=BookValuePerShareYear&min0=10&c1=ReturnOnInvestment5Years&min1=10&c2=RevenueGrowthRate10Years&min2=10&c3=EPSGrowthRate10Years&min3=10

Now instead of clicking it and going to the Google screener and browsing through the tickers on the various pages, I would like to just download a list of the tickers to Excel. Is that possible?


I know the data is not in the source code of the page, but thought maybe someone knows a way of getting the data somehow...

Fri Oct 17, 2014 2:07 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It is possible to grab Google screening output with the add-in, but you'd
need to convert your URL into the JSON request that does the actual screen.
I was able to find that with a URL sniffer:

https://www.google.com/finance?output=json&start=0&num=999&noIL=1&q=[currency%20%3D%3D%20%22USD%22%20%26%20%28%28exchange%20%3D%3D%20%22OTCMKTS%22%29%20%7C%20%28exchange%20%3D%3D%20%22OTCBB%22%29%20%7C%20%28exchange%20%3D%3D%20%22NYSEMKT%22%29%20%7C%20%28exchange%20%3D%3D%20%22NYSEARCA%22%29%20%7C%20%28exchange%20%3D%3D%20%22NYSE%22%29%20%7C%20%28exchange%20%3D%3D%20%22NASDAQ%22%29%29%20%26%20%28book_value_per_share_year%20%3E%3D%2010%29%20%26%20%28book_value_per_share_year%20%3C%3D%2081720000%29%20%26%20%28return_on_investment_5years%20%3E%3D%2010%29%20%26%20%28return_on_investment_5years%20%3C%3D%2028991%29%20%26%20%28revenue_growth_rate_10years%20%3E%3D%2010%29%20%26%20%28revenue_growth_rate_10years%20%3C%3D%20197%29%20%26%20%28eps_growth_rate_10years%20%3E%3D%2010%29%20%26%20%28eps_growth_rate_10years%20%3C%3D%2095.24%29]&restype=company

Data can be extracted from that result.

On Fri, Oct 17, 2014 at 10:21 AM, shiraegi@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

> I am constructing my own screens in Excel, e.g.:
>
>
> https://www.google.com/finance/stockscreener?&c0=BookValuePerShareYear&min0=10&c1=ReturnOnInvestment5Years&min1=10&c2=RevenueGrowthRate10Years&min2=10&c3=EPSGrowthRate10Years&min3=10
>
> Now instead of clicking it and going to the Google screener and browsing
> through the tickers on the various pages, I would like to just download a
> list of the tickers to Excel. Is that possible?
>
> I know the data is not in the source code of the page, but thought maybe
> someone knows a way of getting the data somehow...
>

Fri Oct 17, 2014 10:20 pm (PDT) . Posted by:

shiraegi

Hi Randy, thanks much for your input.

Wow, this looks like it might be quite a task to figure out how to convert to and piece together the JSON request, and then to extract the tickers from the resulting page. And it looks like the screener criteria in the JSON are named differently from the URL, although it would not be that big a deal to get those. Definitely very interesting to know that this is possible. Maybe when i have some time i might try my luck at this.

Thanks again, also for the add-in in general, it's very helpful!

Fri Oct 17, 2014 10:46 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Hmmm. Just noticed that if I check the URL on the "Next page" arrow (at the
bottom of the list of ticker symbols), it does give the JSON request. You'd
just need to change the "&num=" to 999 (for 999 tickers to be returned) and
"&start=" to 0 (to have it start at the top, instead of on the 2nd page.

So once you've created your screen, you could just copy that and modify
those two parameters.

On Fri, Oct 17, 2014 at 10:20 PM, shiraegi@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Hi Randy, thanks much for your input.
>
> Wow, this looks like it might be quite a task to figure out how to convert
> to and piece together the JSON request, and then to extract the tickers
> from the resulting page. And it looks like the screener criteria in the
> JSON are named differently from the URL, although it would not be that big
> a deal to get those. Definitely very interesting to know that this is
> possible. Maybe when i have some time i might try my luck at this.
>
> Thanks again, also for the add-in in general, it's very helpful!
>
>

Fri Oct 17, 2014 2:10 pm (PDT) . Posted by:

egerda

First of all thank you Randy for the wonderful add-in and the continued support.

I have the following code (which is based on Randy's):

' Get historical monthly prices for all stocks on Stock Summary sheet
Sub getHistory()
Dim sItems As String
Dim oCell As Range

' Insert some text in A1 cell. This is needed for the formula to work
Range("A1").Value = "Date"

' Get data for each ticker in row 1
' For the first run of the formula (first column) get the dates (D)
sItems = "D"
For Each oCell In [A1:AB1]
If oCell.Value2 = "" Then Exit For
Range(oCell.Offset(1, 0), oCell.Offset(1000, 0)) = _
RCHGetYahooHistory(oCell.Value2, pPeriod:="m", _
pResort:=0, pItems:=sItems, pDim1:=1000, pDim2:=1)

' For all further loops of the formula get the adjusted closing price (A)
sItems = "A"
Next oCell

' Remove text in A1 cell, it is not needed anymore
Range("A1").Value = ""

End Sub

Everything works fine, except for the Dates which, going backwards, somehow stop at 2011-05-11 (which is not even the first trading day on the month). I do get the data for all dates, it's just that the dates themselves are missing - all dates prior to 2001-05-11 that is.

Any ideas as to what might be happening?

Fri Oct 17, 2014 2:19 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The problem occurs because you put the value "Date" in cell A1. That is an
actual equity, which only has quotes history going back to 2011-05-11. So
your first request, in column A, says to extract the monthly dates for the
data available for that equity.

On Fri, Oct 17, 2014 at 2:07 PM, egerda@hotmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> First of all thank you Randy for the wonderful add-in and the continued
> support.
>
> I have the following code (which is based on Randy's):
>
> ' Get historical monthly prices for all stocks on Stock Summary sheet
> Sub getHistory()
> Dim sItems As String
> Dim oCell As Range
>
> ' Insert some text in A1 cell. This is needed for the formula to work
> Range("A1").Value = "Date"
>
> ' Get data for each ticker in row 1
> ' For the first run of the formula (first column) get the dates (D)
> sItems = "D"
> For Each oCell In [A1:AB1]
> If oCell.Value2 = "" Then Exit For
> Range(oCell.Offset(1, 0), oCell.Offset(1000, 0)) = _
> RCHGetYahooHistory(oCell.Value2, pPeriod:="m", _
> pResort:=0, pItems:=sItems, pDim1:=1000, pDim2:=1)
>
> ' For all further loops of the formula get the adjusted closing price (A)
> sItems = "A"
> Next oCell
>
> ' Remove text in A1 cell, it is not needed anymore
> Range("A1").Value = ""
>
> End Sub
>
> Everything works fine, except for the Dates which, going backwards,
> somehow stop at 2011-05-11 (which is not even the first trading day on the
> month). I do get the data for all dates, it's just that the dates
> themselves are missing - all dates prior to 2001-05-11 that is.
>
> Any ideas as to what might be happening?
>

Fri Oct 17, 2014 2:29 pm (PDT) . Posted by:

egerda

Thanks Randy. What a funny little mistake...
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Posting Komentar