14 Messages
Digest #3109
6b
Re: How do I Extract PE Ratio Data from Yahoo Industry Summary Page by "Randy Harmelink" rharmelink
Messages
Tue Jul 1, 2014 3:47 am (PDT) . Posted by:
drminvest
Thanks for the observation. Now I can get all the data.
Cheers,
Diego
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :
Your later formulas are working properly, just not the way you expect, because they are using a different URL than the first formula. If you go to that web page in your last three formulas, it only has one period of data. If you want more, you have to click a link for more detail. That link brings you to the URL in your first formula -- note the "/detail" suffix on the URL prior to the parameters.
On Mon, Jun 30, 2014 at 6:38 AM, diegoreal@... wrote:
Now I can get the data from Reuters.
Now I have a different problem, to get the data from the table:
Using the command =RCHGetWebData("http://www.reuters.com/finance/stocks/incomeStatement/detail?stmtType=INC&perType=ANN&symbol=KO http://www.reuters.com/finance/stocks/incomeStatement/detail?stmtType=INC&perType=ANN&symbol=KO",51133,,,) I see that there are the data of 5 years, the ones that I want to get.
The problem appears when I use the RCHGetTableCell
=RCHGetTableCell("http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO",1,"dataTable financial",,,,2,"</TABLE",,) works fine -> ("Revenue")
=RCHGetTableCell("http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO",1,"dataTable financial",,,,2,"</TABLE",,) also works fine and I get the Revenues of the first year -> (46854)
but
=RCHGetTableCell("http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO http://www.reuters.com/finance/stocks/incomeStatement?perType=ANN&symbol=KO",3,"
-> (error)
Thanks in advance for your help
Tue Jul 1, 2014 7:59 am (PDT) . Posted by:
r_rada
In doing a Piotroski-type valuation, last year I had been using smf-addin MSN Money FY1 and FY2 to mean current and previous year. When I now check against the data on MSN Money, I see that FY1 is 2004 and FY2 is 2005 and that I should use FY10 for current year and FY9 for previous year. Would someone please help me understand what smf-addin FYi means relative to MSN Money? Has MSN Money changed since last year or was I always using the wrong FYi?
Thank you, Roy
Tue Jul 1, 2014 8:09 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Sigh. MSN changed it up AGAIN!
Back in March, I had to reverse all of the 10-year summary data
extractions, because they changed the sort of the table.
Now it appears they've gone back to the original sort. Permanently?
Temporarily?
But, the big question -- is it sorted differently for different equities?
To answer your first question, it's been my intention that FY1 is the most
recent year available.
I assume you're asking about the 10-year summaries? Or do they have other
data tables that are also being extracted incorrectly? You didn't really
specify which data is incorrect.
On Tue, Jul 1, 2014 at 7:14 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> In doing a Piotroski-type valuation, last year I had been using
> smf-addin MSN Money FY1 and FY2 to mean current and previous year. When
> I now check against the data on MSN Money, I see that FY1 is 2004 and FY2
> is 2005 and that I should use FY10 for current year and FY9 for previous
> year. Would someone please help me understand what smf-addin FYi means
> relative to MSN Money? Has MSN Money changed since last year or was I
> always using the wrong FYi?
>
Back in March, I had to reverse all of the 10-year summary data
extractions, because they changed the sort of the table.
Now it appears they've gone back to the original sort. Permanently?
Temporarily?
But, the big question -- is it sorted differently for different equities?
To answer your first question, it's been my intention that FY1 is the most
recent year available.
I assume you're asking about the 10-year summaries? Or do they have other
data tables that are also being extracted incorrectly? You didn't really
specify which data is incorrect.
On Tue, Jul 1, 2014 at 7:14 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> In doing a Piotroski-type valuation, last year I had been using
> smf-addin MSN Money FY1 and FY2 to mean current and previous year. When
> I now check against the data on MSN Money, I see that FY1 is 2004 and FY2
> is 2005 and that I should use FY10 for current year and FY9 for previous
> year. Would someone please help me understand what smf-addin FYi means
> relative to MSN Money? Has MSN Money changed since last year or was I
> always using the wrong FYi?
>
Tue Jul 1, 2014 9:25 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Aha! I remember what happened now. If you look here:
http://investing.money.msn.com/investments/stock-report?symbol=MMM&QD=1&OP=1&Y1=1&CR=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&TYS=1&ITT=1&ITP=1
The Key Ratios 10-year summaries are sorted in ascending order, but the
financial statement 10-year summaries are sorted in descending order. So I
fixed the financial statement elements to match the key ratios:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/23840
What I'm going to do is change the DESCRIPTIONS of the elements, rather
than the extraction of the elements. That way, people that are using them
the way they are won't have any changes to make. But people may have to
examine if what they are using is what they want.
Anyone that grabbed the data elements from the template should have the
correct elements.
On Tue, Jul 1, 2014 at 7:14 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> In doing a Piotroski-type valuation, last year I had been using
> smf-addin MSN Money FY1 and FY2 to mean current and previous year. When
> I now check against the data on MSN Money, I see that FY1 is 2004 and FY2
> is 2005 and that I should use FY10 for current year and FY9 for previous
> year. Would someone please help me understand what smf-addin FYi means
> relative to MSN Money? Has MSN Money changed since last year or was I
> always using the wrong FYi?
>
http://investing.money.msn.com/investments/stock-report?symbol=MMM&QD=1&OP=1&Y1=1&CR=1&IH=1&AIE=1&AIR=1&FRH=1&FRK=1&TYS=1&ITT=1&ITP=1
The Key Ratios 10-year summaries are sorted in ascending order, but the
financial statement 10-year summaries are sorted in descending order. So I
fixed the financial statement elements to match the key ratios:
https://groups.yahoo.com/neo/groups/smf_addin/conversations/messages/23840
What I'm going to do is change the DESCRIPTIONS of the elements, rather
than the extraction of the elements. That way, people that are using them
the way they are won't have any changes to make. But people may have to
examine if what they are using is what they want.
Anyone that grabbed the data elements from the template should have the
correct elements.
On Tue, Jul 1, 2014 at 7:14 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
> In doing a Piotroski-type valuation, last year I had been using
> smf-addin MSN Money FY1 and FY2 to mean current and previous year. When
> I now check against the data on MSN Money, I see that FY1 is 2004 and FY2
> is 2005 and that I should use FY10 for current year and FY9 for previous
> year. Would someone please help me understand what smf-addin FYi means
> relative to MSN Money? Has MSN Money changed since last year or was I
> always using the wrong FYi?
>
Tue Jul 1, 2014 10:57 am (PDT) . Posted by:
r_rada
Randy, I am focusing for the moment on the MSN Money 10-year summary income statement and balance sheet. I am this week using your xla from RCH_Stock_Market_Functions-2.1.2014.05.25.zip. When I did quality control a year ago, the smf-addin FY1 corresponded to the MSN most recent year. When I did quality control this weekend, I got often that the smf-addin FY1 corresponded to 2004. At noon EST today July 1, I took an arbitrary sample of ten tickers from my data, and checked visually on MSN Money, all ten showed descending years (with the top row the most recent). Let me take one ticker ABTL by example. Visually, on MSN Money its 10-year financials are presented with most recent year at top. When I run smf-addin, I get for FY1 the data from 2004. I gather that you are suggesting that to accommodate MSN, you modified the xla to take the bottom row because MSN was presenting years in ascending order? If that is the case, then for today should I switch my workbook to ask for smf-addin FY10 for MSN Money to get the most recent year data, so long as MSN presents the most recent year at the top and I continue to use the smf-addin xla now on my machine? (I acknowledge that I have only done a tiny data sample and might be seeing or interpreting data incorrectly).
Thank you, Roy
Thank you, Roy
Tue Jul 1, 2014 11:20 am (PDT) . Posted by:
r_rada
Thank you, but I remain confused. When I look at your new definitions xls of today July 1,2014, I sense that all the FYi have been switched from row 153 to 352. However, did you intend that the definitions table rows 153 (10 yr summary date FY1) thru 252 (interest coverage FY10) will remain unchanged, but rows 253 (10 yr summary current assets FY1) through 352 (10 yr summary % tax rate FY10) will change so that each FYi reflects the opposite ordering?
Tue Jul 1, 2014 11:21 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
The problem is that there are two pages of 10-year summaries:
http://investing.money.msn.com/investments/key-ratios?symbol=ABTL&page=TenYearSummary
http://investing.money.msn.com/investments/financial-statements?symbol=ABTL
One has oldest year at the top. The other has most recent year at the top.
They used to be the same. I modified the financial statement element
definitions when they changed. It now appears I should have changed the key
ratios.
In any case, the 10-year summary template shows the correct usage of the
elements. I have now modified the element descriptions to match that usage.
http://ogres-crypt.com/SMF/Templates/SMF-Template-MSN-10-Year-Summaries.xls
On Tue, Jul 1, 2014 at 10:57 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Randy, I am focusing for the moment on the MSN Money 10-year summary
> income statement and balance sheet. I am this week using your xla from
> RCH_Stock_Market_Functions-2.1.2014.05.25.zip. When I did quality control
> a year ago, the smf-addin FY1 corresponded to the MSN most recent year.
> When I did quality control this weekend, I got often that the smf-addin FY1
> corresponded to 2004. At noon EST today July 1, I took an arbitrary sample
> of ten tickers from my data, and checked visually on MSN Money, all ten
> showed descending years (with the top row the most recent). Let me take
> one ticker ABTL by example. Visually, on MSN Money its 10-year financials
> are presented with most recent year at top. When I run smf-addin, I get
> for FY1 the data from 2004. I gather that you are suggesting that to
> accommodate MSN, you modified the xla to take the bottom row because MSN
> was presenting years in ascending order? If that is the case, then for
> today should I switch my workbook to ask for smf-addin FY10 for MSN Money
> to get the most recent year data, so long as MSN presents the most recent
> year at the top and I continue to use the smf-addin xla now on my machine?
> (I acknowledge that I have only done a tiny data sample and might be seeing
> or interpreting data incorrectly).
>
>
http://investing.money.msn.com/investments/key-ratios?symbol=ABTL&page=TenYearSummary
http://investing.money.msn.com/investments/financial-statements?symbol=ABTL
One has oldest year at the top. The other has most recent year at the top.
They used to be the same. I modified the financial statement element
definitions when they changed. It now appears I should have changed the key
ratios.
In any case, the 10-year summary template shows the correct usage of the
elements. I have now modified the element descriptions to match that usage.
http://ogres-crypt.com/SMF/Templates/SMF-Template-MSN-10-Year-Summaries.xls
On Tue, Jul 1, 2014 at 10:57 AM, royrada@gmail.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:
>
> Randy, I am focusing for the moment on the MSN Money 10-year summary
> income statement and balance sheet. I am this week using your xla from
> RCH_Stock_Market_
> a year ago, the smf-addin FY1 corresponded to the MSN most recent year.
> When I did quality control this weekend, I got often that the smf-addin FY1
> corresponded to 2004. At noon EST today July 1, I took an arbitrary sample
> of ten tickers from my data, and checked visually on MSN Money, all ten
> showed descending years (with the top row the most recent). Let me take
> one ticker ABTL by example. Visually, on MSN Money its 10-year financials
> are presented with most recent year at top. When I run smf-addin, I get
> for FY1 the data from 2004. I gather that you are suggesting that to
> accommodate MSN, you modified the xla to take the bottom row because MSN
> was presenting years in ascending order? If that is the case, then for
> today should I switch my workbook to ask for smf-addin FY10 for MSN Money
> to get the most recent year data, so long as MSN presents the most recent
> year at the top and I continue to use the smf-addin xla now on my machine?
> (I acknowledge that I have only done a tiny data sample and might be seeing
> or interpreting data incorrectly)
>
>
Tue Jul 1, 2014 9:43 am (PDT) . Posted by:
rharmelink
Recent File updates on the SMF Add-in Website http://ogres-crypt.com/SMF/: Folder Filename Description Works-In-Progress 0-Change-log.html Log of changes made to the add-in or other files. Works-In-Progress RCHGetElementNumber-Element-Definitions.xls Documentation of element definitions for the RCHGetElementNumber() function Works-In-Progress smf-elements-1.txt Updated FY1 thru FY10 labels to be FY10 thru FY1 for 10-year summary elements Works-In-Progress RCH_Stock_Market_Functions-2.1.2014.05.25.zip Beta copy of the add-in. This contains all the add-in files that I am currently using in my day-to-day processing.
Tue Jul 1, 2014 1:14 pm (PDT) . Posted by:
Buck_69_69
i think the emma is converting the cusip to the below crazy syntax
http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391 http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391
Tue Jul 1, 2014 1:49 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The data you want is on that page, but within JavaScript. For example, the
first row of trade data is:
=smfWord(RCHGetWebData("
http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391","tradeData
= "),3,"{")
...which returns:
"SID":"251129R61","TDT":1365625339000.0,"TD":"2013-04-10T00:00:00","PX":100.0,"YX":4.505,"TA":15000.0,"TT":"D","WI":null,"BI":null,"PI":null,"LI":null,"UI":null,"STL":"2013-04-15T00:00:00","AI":null},
So the yield from the first row would be:
=smfConvertData(smfstrExtr(smfWord(RCHGetWebData("
http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391","tradeData
= "),3,"{"),"""YX"":",","))
And it looks like the "crazy value" below can be retrieved with:
=smfstrExtr(RCHGetWebData("
http://emma.msrb.org/SecurityView/SecurityDetails.aspx?cusip=251129R61
","SecurityDetailsRatings.aspx?cusip=",100),"=","""")
So, everything should be extractable. I think.
On Tue, Jul 1, 2014 at 1:14 PM, buck_69_69@... wrote:
>
> i think the emma is converting the cusip to the below crazy syntax
>
>
> http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391
>
first row of trade data is:
=smfWord(RCHGetWebData("
http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391","tradeData
= "),3,"{")
...which returns:
"SID":"251129R61","TDT":1365625339000.0,"TD":"2013-04-10T00:00:00","PX":100.0,"YX":4.505,"TA":15000.0,"TT":"D","WI":null,"BI":null,"PI":null,"LI":null,"UI":null,"STL":"2013-04-15T00:00:00","AI":null},
So the yield from the first row would be:
=smfConvertData(smfstrExtr(smfWord(RCHGetWebData("
http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391","tradeData
= "),3,"{"),"""YX"":",","))
And it looks like the "crazy value" below can be retrieved with:
=smfstrExtr(RCHGetWebData("
http://emma.msrb.org/SecurityView/SecurityDetails.aspx?cusip=251129R61
","SecurityDetailsRatings.aspx?cusip=",100),"=","""")
So, everything should be extractable. I think.
On Tue, Jul 1, 2014 at 1:14 PM, buck_69_69@... wrote:
>
> i think the emma is converting the cusip to the below crazy syntax
>
>
> http://emma.msrb.org/SecurityDetails/TradeActivity/A5949D72EE0586CF35F624E775785F391
>
Tue Jul 1, 2014 2:31 pm (PDT) . Posted by:
"अनन्त:" ritchemie
You can try out this website,
oldschoolvalue.com
He is giving Ben Graham spreadsheet for free which uses SMF Add-in for a
while
On Mon, Jun 30, 2014 at 12:45 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Sounds to me like you're more interesting in a screening tool. I'd suggest
> Portfolio123.com or AAII's Stock Investor Pro.
>
> On Mon, Jun 30, 2014 at 9:12 AM, electronicjason@...wrote:
>
>
>> What is the best value investing template for the SMF Add In? Something
>> along Ben Graham's Intelligent Investor or Security Analysis.
>>
>
>
>
oldschoolvalue.com
He is giving Ben Graham spreadsheet for free which uses SMF Add-in for a
while
On Mon, Jun 30, 2014 at 12:45 PM, Randy Harmelink rharmelink@gmail.com
[smf_addin] <smf_addin@yahoogroups.com> wrote:
>
>
> Sounds to me like you're more interesting in a screening tool. I'd suggest
> Portfolio123.
>
> On Mon, Jun 30, 2014 at 9:12 AM, electronicjason@
>
>
>> What is the best value investing template for the SMF Add In? Something
>> along Ben Graham's Intelligent Investor or Security Analysis.
>>
>
>
>
Tue Jul 1, 2014 4:10 pm (PDT) . Posted by:
"Jim Ranum" amt2100
Interesting that someone would go through the time and money to set all that up, when Finviz and Gurufocus do the same thing for free.
And there are probably others also.
Save your money.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Tuesday, July 01, 2014 5:31 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Best Ben Graham Value Investing Template
You can try out this website,
oldschoolvalue.com
He is giving Ben Graham spreadsheet for free which uses SMF Add-in for a while
On Mon, Jun 30, 2014 at 12:45 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Sounds to me like you're more interesting in a screening tool. I'd suggest Portfolio123.com or AAII's Stock Investor Pro.
On Mon, Jun 30, 2014 at 9:12 AM, electronicjason@...wrote:
What is the best value investing template for the SMF Add In? Something along Ben Graham's Intelligent Investor or Security Analysis.
And there are probably others also.
Save your money.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Tuesday, July 01, 2014 5:31 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Best Ben Graham Value Investing Template
You can try out this website,
oldschoolvalue.com
He is giving Ben Graham spreadsheet for free which uses SMF Add-in for a while
On Mon, Jun 30, 2014 at 12:45 PM, Randy Harmelink rharmelink@gmail.com [smf_addin] <smf_addin@yahoogroups.com> wrote:
Sounds to me like you're more interesting in a screening tool. I'd suggest Portfolio123.
On Mon, Jun 30, 2014 at 9:12 AM, electronicjason@
What is the best value investing template for the SMF Add In? Something along Ben Graham's Intelligent Investor or Security Analysis.
Wed Jul 2, 2014 12:08 am (PDT) . Posted by:
ridgebacksexcel
Randy,
What is the best formula to extract the PE Ratio Data from this web page on Yahoo:
http://biz.yahoo.com/p/industries.html http://biz.yahoo.com/p/industries.html
Ideally I would like to reference the Industry Title from a cell and then have it extract the PE Ratio Data for that Industry.
For Instance, if cell A1 has "Information Technology Services" I would like it to search all the way to the last industry title, then over 3 columns and pull the PE Ratio of 18.80 currently.
I noticed that it is actually "InformationTechnology Services" in the source code with no space between Information and Technology so that might mess up what I am trying to do. Not sure.
Thanks.
What is the best formula to extract the PE Ratio Data from this web page on Yahoo:
http://biz.yahoo.com/p/industries.html http://biz.yahoo.com/p/industries.html
Ideally I would like to reference the Industry Title from a cell and then have it extract the PE Ratio Data for that Industry.
For Instance, if cell A1 has "Information Technology Services" I would like it to search all the way to the last industry title, then over 3 columns and pull the PE Ratio of 18.80 currently.
I noticed that it is actually "InformationTe
Thanks.
Wed Jul 2, 2014 12:44 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I think your best method would be to use their industry group number. In
the case of "Information Technology Services", that would be 824. For
example:
=RCHGetTableCell("http://biz.yahoo.com/p/industries.html",3,"=824conameu")
On Wed, Jul 2, 2014 at 12:08 AM, tmallen2@... wrote:
> What is the best formula to extract the PE Ratio Data from this web page
> on Yahoo:
>
> http://biz.yahoo.com/p/industries.html
>
> Ideally I would like to reference the Industry Title from a cell and then
> have it extract the PE Ratio Data for that Industry.
>
> For Instance, if cell A1 has "Information Technology Services" I would
> like it to search all the way to the last industry title, then over 3
> columns and pull the PE Ratio of 18.80 currently.
>
> I noticed that it is actually "InformationTechnology Services" in the
> source code with no space between Information and Technology so that might
> mess up what I am trying to do. Not sure.
>
the case of "Information Technology Services", that would be 824. For
example:
=RCHGetTableCell("http://biz.yahoo.com/p/industries.html",3,"=824conameu")
On Wed, Jul 2, 2014 at 12:08 AM, tmallen2@... wrote:
> What is the best formula to extract the PE Ratio Data from this web page
> on Yahoo:
>
> http://biz.yahoo.com/p/industries.html
>
> Ideally I would like to reference the Industry Title from a cell and then
> have it extract the PE Ratio Data for that Industry.
>
> For Instance, if cell A1 has "Information Technology Services" I would
> like it to search all the way to the last industry title, then over 3
> columns and pull the PE Ratio of 18.80 currently.
>
> I noticed that it is actually "InformationTe
> source code with no space between Information and Technology so that might
> mess up what I am trying to do. Not sure.
>
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar