15 New Messages
Digest #2859
Messages
Fri Nov 15, 2013 4:39 am (PST) . Posted by:
"Jim Ranum" amt2100
OK, thanks Randy.
I might have mentioned I'm pretty VBA illiterate.
After opening the Developer tab and the Visual Basic button, where would I enter this code you gave me?
Thanks,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 12:49 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Stopped working after web query
You don't need to CREATE a web query. Just visit the site with the Web Query dialog, log in, then cancel out. I've not seen the symptoms you describe.
You could run some VBA code to delete anything that might exist for a query:
Sub RemoveQueryNames()
Dim nQuery As Name
With ThisWorkbook
For Each nQuery In Names
nQuery.Delete
Next nQuery
End With
End Sub
On Thu, Nov 14, 2013 at 9:21 PM, Jim Ranum <amt2100@gmail.com> wrote:
Well I tried to create an Excel Web Query as you mentioned yesterday to get the Shiller PE Ratio off of Gurufocus and now my spreadsheet has stopped working when I change the ticker.
I have too much stuff on here to just start over. So maybe you can help me sort it out?
After I tried to create the Web Query, getting the data for the Shiller PE cell never worked. I couldn't find where to delete the web Query and I ended up shutting down excel and the computer for a few hours while away. Came back and opened the file and it hangs up, not responding, with "trying to contact server" in the lower right corner of excel. After hitting ESC a couple times, I can get back to the spreadsheet, but as I mentioned, changing tickers won't update the data like it did before I did this Web Query thing.
Now when I open the Web Query button, nothing is there to delete. But obviously it lives on.
Where do I go from here?
I might have mentioned I'm pretty VBA illiterate.
After opening the Developer tab and the Visual Basic button, where would I enter this code you gave me?
Thanks,
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 12:49 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Stopped working after web query
You don't need to CREATE a web query. Just visit the site with the Web Query dialog, log in, then cancel out. I've not seen the symptoms you describe.
You could run some VBA code to delete anything that might exist for a query:
Sub RemoveQueryNames(
Dim nQuery As Name
With ThisWorkbook
For Each nQuery In Names
nQuery.Delete
Next nQuery
End With
End Sub
On Thu, Nov 14, 2013 at 9:21 PM, Jim Ranum <amt2100@gmail.
Well I tried to create an Excel Web Query as you mentioned yesterday to get the Shiller PE Ratio off of Gurufocus and now my spreadsheet has stopped working when I change the ticker.
I have too much stuff on here to just start over. So maybe you can help me sort it out?
After I tried to create the Web Query, getting the data for the Shiller PE cell never worked. I couldn't find where to delete the web Query and I ended up shutting down excel and the computer for a few hours while away. Came back and opened the file and it hangs up, not responding, with "trying to contact server" in the lower right corner of excel. After hitting ESC a couple times, I can get back to the spreadsheet, but as I mentioned, changing tickers won't update the data like it did before I did this Web Query thing.
Now when I open the Web Query button, nothing is there to delete. But obviously it lives on.
Where do I go from here?
Fri Nov 15, 2013 4:48 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Try:
- Do a right-click on the VBAProject for your workbook and choose the
Insert > Module
- Cut and paste that code into the newly created module
- Press F5 to run the code, or F8 to run it on line at a time. With F8,
you can see if it actually does execute that DELETE statement. If you try
running it a second time, it should never reach that statement, because all
queries should have been deleted.
- When done, right-click on that new module listed in the VBAProject and
choose Remove Module.
On Fri, Nov 15, 2013 at 5:39 AM, Jim Ranum <amt2100@gmail.com> wrote:
>
> OK, thanks Randy.
>
> I might have mentioned I'm pretty VBA illiterate.
>
> After opening the Developer tab and the Visual Basic button, where would I
> enter this code you gave me?
>
>
>
> Thanks,
>
> Jim
>
>
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] *On
> Behalf Of *Randy Harmelink
> *Sent:* Friday, November 15, 2013 12:49 AM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] Stopped working after web query
>
>
>
>
>
> You don't need to CREATE a web query. Just visit the site with the Web
> Query dialog, log in, then cancel out. I've not seen the symptoms you
> describe.
>
> You could run some VBA code to delete anything that might exist for a
> query:
>
> Sub RemoveQueryNames()
> Dim nQuery As Name
> With ThisWorkbook
> For Each nQuery In Names
> nQuery.Delete
> Next nQuery
> End With
> End Sub
>
>
>
- Do a right-click on the VBAProject for your workbook and choose the
Insert > Module
- Cut and paste that code into the newly created module
- Press F5 to run the code, or F8 to run it on line at a time. With F8,
you can see if it actually does execute that DELETE statement. If you try
running it a second time, it should never reach that statement, because all
queries should have been deleted.
- When done, right-click on that new module listed in the VBAProject and
choose Remove Module.
On Fri, Nov 15, 2013 at 5:39 AM, Jim Ranum <amt2100@gmail.
>
> OK, thanks Randy.
>
> I might have mentioned I'm pretty VBA illiterate.
>
> After opening the Developer tab and the Visual Basic button, where would I
> enter this code you gave me?
>
>
>
> Thanks,
>
> Jim
>
>
>
>
>
> *From:* smf_addin@yahoogrou
> Behalf Of *Randy Harmelink
> *Sent:* Friday, November 15, 2013 12:49 AM
> *To:* smf_addin@yahoogrou
> *Subject:* Re: [smf_addin] Stopped working after web query
>
>
>
>
>
> You don't need to CREATE a web query. Just visit the site with the Web
> Query dialog, log in, then cancel out. I've not seen the symptoms you
> describe.
>
> You could run some VBA code to delete anything that might exist for a
> query:
>
> Sub RemoveQueryNames(
> Dim nQuery As Name
> With ThisWorkbook
> For Each nQuery In Names
> nQuery.Delete
> Next nQuery
> End With
> End Sub
>
>
>
Fri Nov 15, 2013 5:14 am (PST) . Posted by:
"Jim Ranum" amt2100
Great. That has it working again.
Huge help. Thanks very much Randy.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 7:49 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Stopped working after web query
Try:
* Do a right-click on the VBAProject for your workbook and choose the Insert > Module
* Cut and paste that code into the newly created module
* Press F5 to run the code, or F8 to run it on line at a time. With F8, you can see if it actually does execute that DELETE statement. If you try running it a second time, it should never reach that statement, because all queries should have been deleted.
* When done, right-click on that new module listed in the VBAProject and choose Remove Module.
On Fri, Nov 15, 2013 at 5:39 AM, Jim Ranum <amt2100@gmail.com> wrote:
OK, thanks Randy.
I might have mentioned I'm pretty VBA illiterate.
After opening the Developer tab and the Visual Basic button, where would I enter this code you gave me?
Thanks,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 12:49 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Stopped working after web query
You don't need to CREATE a web query. Just visit the site with the Web Query dialog, log in, then cancel out. I've not seen the symptoms you describe.
You could run some VBA code to delete anything that might exist for a query:
Sub RemoveQueryNames()
Dim nQuery As Name
With ThisWorkbook
For Each nQuery In Names
nQuery.Delete
Next nQuery
End With
End Sub
Huge help. Thanks very much Randy.
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 7:49 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Stopped working after web query
Try:
* Do a right-click on the VBAProject for your workbook and choose the Insert > Module
* Cut and paste that code into the newly created module
* Press F5 to run the code, or F8 to run it on line at a time. With F8, you can see if it actually does execute that DELETE statement. If you try running it a second time, it should never reach that statement, because all queries should have been deleted.
* When done, right-click on that new module listed in the VBAProject and choose Remove Module.
On Fri, Nov 15, 2013 at 5:39 AM, Jim Ranum <amt2100@gmail.
OK, thanks Randy.
I might have mentioned I'm pretty VBA illiterate.
After opening the Developer tab and the Visual Basic button, where would I enter this code you gave me?
Thanks,
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 12:49 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Stopped working after web query
You don't need to CREATE a web query. Just visit the site with the Web Query dialog, log in, then cancel out. I've not seen the symptoms you describe.
You could run some VBA code to delete anything that might exist for a query:
Sub RemoveQueryNames(
Dim nQuery As Name
With ThisWorkbook
For Each nQuery In Names
nQuery.Delete
Next nQuery
End With
End Sub
Fri Nov 15, 2013 5:32 am (PST) . Posted by:
holmberg_tommy
Hi Randy,
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
3. 2012 Total long term debt (36706 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
4. 2012 Selling, general and admin. expenses, total from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
Thanks.
- Tommy
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.
2. Bring 2012 Notes payable/short-
http://markets.
3. 2012 Total long term debt (36706 million DKK) from
http://markets.
4. 2012 Selling, general and admin. expenses, total from
http://markets.
Thanks.
- Tommy
Fri Nov 15, 2013 5:45 am (PST) . Posted by:
"Randy Harmelink" rharmelink
It's not easy to get the 2012 data, but it is easy to get the first column
of data (the date at the head of the column is the fiscal year and will
vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Diluted
eps excl. extra items")
All the others are very similar:
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Notes
payable/short-term debt")
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Total
long term debt")
=RCHGetTableCell("
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Selling,
general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The
">" is used to make it unique because it is the end of the HTML tag within
the source code of the web page. It's a technique that often works, but not
always. You sometimes do need to examine the source code to see how they've
coded things. Then, the "1" parameter just says to get the next table cell
after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@yahoo.com> wrote:
>
> I would need your help with couple of items. Hopefully you could give
> instructions how to:
>
> 1. Bring 2012 annual EPS from
>
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
>
> 2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
>
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
>
> 3. 2012 Total long term debt (36706 million DKK) from
>
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
>
> 4. 2012 Selling, general and admin. expenses, total from
>
> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
>
>
of data (the date at the head of the column is the fiscal year and will
vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell(
http://markets.
eps excl. extra items")
All the others are very similar:
=RCHGetTableCell(
http://markets.
payable/short-
=RCHGetTableCell(
http://markets.
long term debt")
=RCHGetTableCell(
http://markets.
general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The
">" is used to make it unique because it is the end of the HTML tag within
the source code of the web page. It's a technique that often works, but not
always. You sometimes do need to examine the source code to see how they've
coded things. Then, the "1" parameter just says to get the next table cell
after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@
>
> I would need your help with couple of items. Hopefully you could give
> instructions how to:
>
> 1. Bring 2012 annual EPS from
>
> http://markets.
>
> 2. Bring 2012 Notes payable/short-
>
> http://markets.
>
> 3. 2012 Total long term debt (36706 million DKK) from
>
> http://markets.
>
> 4. 2012 Selling, general and admin. expenses, total from
>
> http://markets.
>
>
Fri Nov 15, 2013 6:09 am (PST) . Posted by:
"Jim Ranum" amt2100
Hi Randy,
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
Might save you some questions to answer…
Thanks again,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 8:45 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Data from Reuters
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement> &subview=IncomeStatement",1,">Diluted eps excl. extra items")
All the others are very similar:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet> &subview=BalanceSheet",1,">Notes payable/short-term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet> &subview=BalanceSheet",1,">Total long term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement> &subview=IncomeStatement",1,">Selling, general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@yahoo.com> wrote:
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement> &subview=IncomeStatement
2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet> &subview=BalanceSheet
3. 2012 Total long term debt (36706 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet> &subview=BalanceSheet
4. 2012 Selling, general and admin. expenses, total from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH <http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement> &subview=IncomeStatement
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
Might save you some questions to answer…
Thanks again,
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 8:45 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Data from Reuters
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell(
All the others are very similar:
=RCHGetTableCell(
=RCHGetTableCell(
=RCHGetTableCell(
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.
2. Bring 2012 Notes payable/short-
http://markets.
3. 2012 Total long term debt (36706 million DKK) from
http://markets.
4. 2012 Selling, general and admin. expenses, total from
http://markets.
Fri Nov 15, 2013 6:44 am (PST) . Posted by:
"Randy Harmelink" rharmelink
In FireFox, I just right-click on the web page, and "View Source" is on of
the options on that menu...
And then you search for what you see on the web page, to get the context of
the surrounding source code. And, when you search, you'll see how unique a
search term is.
For example, if you search just for "EPS", you'll probably find it all off
a financial web page.
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.com> wrote:
>
> You mention this quite frequently. How does one "examine the source code"?
>
> How do you access it to examine it?
>
> And where would you see what you're talking about?
>
>
>
the options on that menu...
And then you search for what you see on the web page, to get the context of
the surrounding source code. And, when you search, you'll see how unique a
search term is.
For example, if you search just for "EPS"
a financial web page.
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.
>
> You mention this quite frequently. How does one "examine the source code"?
>
> How do you access it to examine it?
>
> And where would you see what you're talking about?
>
>
>
Fri Nov 15, 2013 7:10 am (PST) . Posted by:
"Jim Ranum" amt2100
Sorry to hijack this thread.
I found the source code referencing the Shiller PE Ratio on Guru:
href="#" class="subMenu" title="Shiller PE Ratio" id="ShillerPE">Shiller PE Ratio</a>
And interestingly, one time that I opened my spreadsheet, that cell did populate.
What do you make of that/this?
Does this source code suggest anything specific I should use in the formula?
Thanks,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 9:44 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Data from Reuters
In FireFox, I just right-click on the web page, and "View Source" is on of the options on that menu...
And then you search for what you see on the web page, to get the context of the surrounding source code. And, when you search, you'll see how unique a search term is.
For example, if you search just for "EPS", you'll probably find it all off a financial web page.
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.com> wrote:
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
I found the source code referencing the Shiller PE Ratio on Guru:
href="#
And interestingly, one time that I opened my spreadsheet, that cell did populate.
What do you make of that/this?
Does this source code suggest anything specific I should use in the formula?
Thanks,
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 9:44 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Data from Reuters
In FireFox, I just right-click on the web page, and "View Source" is on of the options on that menu...
And then you search for what you see on the web page, to get the context of the surrounding source code. And, when you search, you'll see how unique a search term is.
For example, if you search just for "EPS"
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
Fri Nov 15, 2013 7:16 am (PST) . Posted by:
"Jim Ranum" amt2100
Oops, sent too soon.
I did find the data buried in the code.
How would I pull this out of here?
The number I'm looking for is the 'tdnormalttm' title='12.50'
12.50 for last TTM.
<tr onClick="javascript:tablerowajaxpage('/modules/chart/stock/stock_iframe.php??symbol=HES&series[]=ShillerPE&trendline[]=1&showq=true&quarterlydata','Rf',this,'Shiller PE Ratio1230475692');" onmouseover = "this.className='highlight highlightwithicon';" onMouseOut="this.className='';"><td class='th_normal' title='Shiller PE Ratio'><a href='/term/ShillerPE/HES/Shiller%252BPE%252BRatio/Hess%2BCorp' class='nav'>Shiller PE Ratio</a></td>
<td class='style4' title='11.82'><div class='td_normal1' title='11.820'>11.82</div></td>
<td class='style4' title='11.37'><div class='td_normal2' title='11.370'>11.37</div></td>
<td class='style4' title='14.85'><div class='td_normal3' title='14.850'>14.85</div></td>
<td class='style4' title='15.51'><div class='td_normal4' title='15.510'>15.51</div></td>
<td class='style4' title='28.00'><div class='td_normal5' title='28.000'>28.00</div></td>
<td class='style4' title='10.90'><div class='td_normal6' title='10.900'>10.90</div></td>
<td class='style4' title='12.16'><div class='td_normal7' title='12.160'>12.16</div></td>
<td class='style4' title='13.82'><div class='td_normal8' title='13.820'>13.82</div></td>
<td class='style4' title='9.84'><div class='td_normal9' title='9.840'>9.84</div></td>
<td class='style4' title='9.26'><div class='td_normal10' title='9.260'>9.26</div></td>
<td class='style4'><div class='td_normalttm' title='12.50' >12.50</div></td>
<td class='style5' title='9.25'><div class='td_normal11' title='9.250'>9.25</div></td>
<td class='style5' title='9.26'><div class='td_normal12' title='9.260'>9.26</div></td>
<td class='style5' title='11.59'><div class='td_normal13' title='11.590'>11.59</div></td>
<td class='style5' title='10.16'><div class='td_normal14' title='10.160'>10.16</div></td>
<td class='style5' title='11.75'><div class='td_normal15' title='11.750'>11.75</div></td>
Thanks,
Jim
From: Jim Ranum [mailto:amt2100@gmail.com]
Sent: Friday, November 15, 2013 10:10 AM
To: 'smf_addin@yahoogroups.com'
Subject: RE: [smf_addin] Data from Reuters
Sorry to hijack this thread.
I found the source code referencing the Shiller PE Ratio on Guru:
href="#" class="subMenu" title="Shiller PE Ratio" id="ShillerPE">Shiller PE Ratio</a>
And interestingly, one time that I opened my spreadsheet, that cell did populate.
What do you make of that/this?
Does this source code suggest anything specific I should use in the formula?
Thanks,
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Friday, November 15, 2013 9:44 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Data from Reuters
In FireFox, I just right-click on the web page, and "View Source" is on of the options on that menu...
And then you search for what you see on the web page, to get the context of the surrounding source code. And, when you search, you'll see how unique a search term is.
For example, if you search just for "EPS", you'll probably find it all off a financial web page.
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.com> wrote:
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
I did find the data buried in the code.
How would I pull this out of here?
The number I'm looking for is the 'tdnormalttm' title='12.50'
12.50 for last TTM.
<tr onClick="
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style4&#
<td class='style5&#
<td class='style5&#
<td class='style5&#
<td class='style5&#
<td class='style5&#
Thanks,
Jim
From: Jim Ranum [mailto:amt2100@gmail.
Sent: Friday, November 15, 2013 10:10 AM
To: 'smf_addin@
Subject: RE: [smf_addin] Data from Reuters
Sorry to hijack this thread.
I found the source code referencing the Shiller PE Ratio on Guru:
href="#
And interestingly, one time that I opened my spreadsheet, that cell did populate.
What do you make of that/this?
Does this source code suggest anything specific I should use in the formula?
Thanks,
Jim
From: smf_addin@yahoogrou
Sent: Friday, November 15, 2013 9:44 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Data from Reuters
In FireFox, I just right-click on the web page, and "View Source" is on of the options on that menu...
And then you search for what you see on the web page, to get the context of the surrounding source code. And, when you search, you'll see how unique a search term is.
For example, if you search just for "EPS"
On Fri, Nov 15, 2013 at 7:09 AM, Jim Ranum <amt2100@gmail.
You mention this quite frequently. How does one "examine the source code"?
How do you access it to examine it?
And where would you see what you're talking about?
Fri Nov 15, 2013 7:52 am (PST) . Posted by:
holmberg_tommy
Hi Randy,
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCell" brings in to spreadsheet is in text format and can't therefore be used in any math formula? or is there any way to convert it to number format?
- Tommy
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Diluted eps excl. extra items")
All the others are very similar:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Notes payable/short-term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Total long term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Selling, general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@... mailto:holmberg_tommy@...> wrote:
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
3. 2012 Total long term debt (36706 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
4. 2012 Selling, general and admin. expenses, total from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCe
- Tommy
---In smf_addin@yahoogrou
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell(
All the others are very similar:
=RCHGetTableCell(
=RCHGetTableCell(
=RCHGetTableCell(
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.
2. Bring 2012 Notes payable/short-
http://markets.
3. 2012 Total long term debt (36706 million DKK) from
http://markets.
4. 2012 Selling, general and admin. expenses, total from
http://markets.
Fri Nov 15, 2013 8:17 am (PST) . Posted by:
holmberg_tommy
And one more :)
Can I replace reference cell for instance in :
=smfConvertData(smfGetTagContent("http://www.reuters.com/finance/stocks/overview?symbol=CARLb.CO";"span";2;"sectionQuoteDetail"))
And how it's carried out?
Purpose for this is that I would not need to manually update all the cells when I'm creating new rows for different companies, instead they would automatically update themselves if they are referring to cell that includes correct ticker code.
- Tommy
---In smf_addin@yahoogroups.com, <holmberg_tommy@...> wrote:
Hi Randy,
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCell" brings in to spreadsheet is in text format and can't therefore be used in any math formula? or is there any way to convert it to number format?
- Tommy
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Diluted eps excl. extra items")
All the others are very similar:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Notes payable/short-term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Total long term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Selling, general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@... mailto:holmberg_tommy@...> wrote:
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
3. 2012 Total long term debt (36706 million DKK) from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
4. 2012 Selling, general and admin. expenses, total from
http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
Can I replace reference cell for instance in :
=smfConvertData(
And how it's carried out?
Purpose for this is that I would not need to manually update all the cells when I'm creating new rows for different companies, instead they would automatically update themselves if they are referring to cell that includes correct ticker code.
- Tommy
---In smf_addin@yahoogrou
Hi Randy,
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCe
- Tommy
---In smf_addin@yahoogrou
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell(
All the others are very similar:
=RCHGetTableCell(
=RCHGetTableCell(
=RCHGetTableCell(
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_
I would need your help with couple of items. Hopefully you could give instructions how to:
1. Bring 2012 annual EPS from
http://markets.
2. Bring 2012 Notes payable/short-
http://markets.
3. 2012 Total long term debt (36706 million DKK) from
http://markets.
4. 2012 Selling, general and admin. expenses, total from
http://markets.
Fri Nov 15, 2013 11:40 am (PST) . Posted by:
"AMIT GORE" amitgore2000
Hi Randy,
I have just joined the group yesterday, and my email is getting flooded with all the postings.
Is there some way I can choose not to receive the postings, but yet remain a part of the group and access it when I want?
Thanks,
Amit Gore
On Friday, November 15, 2013 10:58:41 AM, "holmberg_tommy@yahoo.com" <holmberg_tommy@yahoo.com> wrote:
Hi Randy,
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCell" brings in to spreadsheet is in text format and can't therefore be used in any math formula? or is there any way to convert it to number format?
- Tommy
---In smf_addin@yahoogroups.com, <rharmelink@...> wrote:
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Diluted eps excl. extra items")
All the others are very similar:
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Notes payable/short-term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet",1,">Total long term debt")
=RCHGetTableCell("http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement",1,">Selling, general and admin. expenses, total")
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_tommy@...> wrote:
>
>
>>
>>I would need your help with couple of items. Hopefully you could give instructions how to:
>>
>>
>>1. Bring 2012 annual EPS from
>> http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
>>
>>
>>2. Bring 2012 Notes payable/short-term debt (3352 million DKK) from
>>http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
>>
>>
>>
>>3. 2012 Total long term debt (36706 million DKK) from
>>http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=BalanceSheet
>>
>>
>>
>>4. 2012 Selling, general and admin. expenses, total from
>>http://markets.ft.com/research/Markets/Tearsheets/Financials?s=CARL+B:CPH&subview=IncomeStatement
>>
>>
>
I have just joined the group yesterday, and my email is getting flooded with all the postings.
Is there some way I can choose not to receive the postings, but yet remain a part of the group and access it when I want?
Thanks,
Amit Gore
On Friday, November 15, 2013 10:58:41 AM, "holmberg_tommy@
Hi Randy,
Thanks you very much for the instructions, they seem to be working great.
I would however have one further question. Is it so that the data that "RCHGetTableCe
- Tommy
---In smf_addin@yahoogrou
It's not easy to get the 2012 data, but it is easy to get the first column of data (the date at the head of the column is the fiscal year and will vary over time).
For (1), I don't see a line item called "annual EPS". How about:
=RCHGetTableCell(
All the others are very similar:
=RCHGetTableCell(
=RCHGetTableCell(
=RCHGetTableCell(
Basically, the first search term is the line item, since it is unique. The ">" is used to make it unique because it is the end of the HTML tag within the source code of the web page. It's a technique that often works, but not always. You sometimes do need to examine the source code to see how they've coded things. Then, the "1" parameter just says to get the next table cell after that search term.
On Fri, Nov 15, 2013 at 6:32 AM, <holmberg_
>
>
>>
>>I would need your help with couple of items. Hopefully you could give instructions how to:
>>
>>
>>1. Bring 2012 annual EPS from
>> http://markets.
>>
>>
>>2. Bring 2012 Notes payable/short-
>>http://markets.
>>
>>
>>
>>3. 2012 Total long term debt (36706 million DKK) from
>>http://markets.
>>
>>
>>
>>4. 2012 Selling, general and admin. expenses, total from
>>http://markets.
>>
>>
>
Fri Nov 15, 2013 9:06 am (PST) . Posted by:
"Jacob Jose" pepecan47
I am trying to extract data from http://quotes.morningstar.com/stock/c-financials?&t=adi
I need some help to get the "last year date " in Annual. The value is 2012-10
Randy, are you planning to define elements from this page?.
Regards,
Jose L. Jacob
I need some help to get the "last year date " in Annual. The value is 2012-10
Randy, are you planning to define elements from this page?.
Regards,
Jose L. Jacob
Fri Nov 15, 2013 12:54 pm (PST) . Posted by:
"Jacob Jose" pepecan47
I use the following formula to extract the Economic Moat:
=smfGetTagContent("http://quotes.morningstar.com/stock/c-morningstar-take?&t="&A3,"span",1,"Economic Moat")
It works with several tickers, ADI, BAX, CTL, GIS
But, for AFL and MCD I get: Read full Analyst Report
I have a premium membership and I am loggged on to Morningstar.
Regards,
Jose L. Jacob
=smfGetTagContent(
It works with several tickers, ADI, BAX, CTL, GIS
But, for AFL and MCD I get: Read full Analyst Report
I have a premium membership and I am loggged on to Morningstar.
Regards,
Jose L. Jacob
Fri Nov 15, 2013 1:12 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That's because "Economic Moat" isn't unique enough as a search string. For
McDonald's, the first place that string appears on the page is in the
description of the company. And the next "span" tag contains "Read Full
Analyst Report". This is why I often use the ">" suffix from the preceding
HTML tag to make a string unique.
For example, this is the formula used in the template in the FILES area of
the group:
=RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol="&B$2,
2,">Economic Moat",,,,1)
On Fri, Nov 15, 2013 at 1:53 PM, Jacob Jose <pepecan47@yahoo.ca> wrote:
>
> I use the following formula to extract the Economic Moat:
>
> =smfGetTagContent("
> http://quotes.morningstar.com/stock/c-morningstar-take?&t="&A3,"span",1,"EconomicMoat")
>
> It works with several tickers, ADI, BAX, CTL, GIS
>
> But, for AFL and MCD I get: Read full Analyst Report
>
> I have a premium membership and I am loggged on to Morningstar.
>
>
McDonald'
description of the company. And the next "span" tag contains "Read Full
Analyst Report"
HTML tag to make a string unique.
For example, this is the formula used in the template in the FILES area of
the group:
=RCHGetTableCell(
http://quicktake.
2,">
On Fri, Nov 15, 2013 at 1:53 PM, Jacob Jose <pepecan47@yahoo.
>
> I use the following formula to extract the Economic Moat:
>
> =smfGetTagContent(
> http://quotes.
>
> It works with several tickers, ADI, BAX, CTL, GIS
>
> But, for AFL and MCD I get: Read full Analyst Report
>
> I have a premium membership and I am loggged on to Morningstar.
>
>
Tidak ada komentar:
Posting Komentar