Kamis, 08 Oktober 2015

[smf_addin] Digest Number 3531

14 Messages

Digest #3531
2a
2b
Re: Stock Buyback Lookup Formula by "Randy Harmelink" rharmelink
2d
Re: Stock Buyback Lookup Formula by "Randy Harmelink" rharmelink
2f
Re: Stock Buyback Lookup Formula by "Randy Harmelink" rharmelink
3a
YCharts by yolande_tulasse
3b
Re: YCharts by yolande_tulasse

Messages

Wed Oct 7, 2015 2:43 am (PDT) . Posted by:

andy_c_chan

Dear Randy, I tried using this spreadsheet but I don't seem to be getting anything from it. it's probably the ticker format. can u give me an example for a US stock in MSN ticker format and a HK ticker format if possible? say HSBC which is 0005.HK in reuters ric, many thanks.


P.S. do you have an excel template for the MSN summary pages? like the Key Stats Page etc?

Wed Oct 7, 2015 7:05 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

Andy, if you go to the website ( http://ogres-crypt.com/SMF ) which is listed at the bottom of each post and search the Templates folder for MSN you will find 10 hits.

One of them may have what you are looking for. But there are really only 3 hits. For some reason they are listed in 3 places



SMF-Template-MSN-10-Year-Summaries.xls <http://ogres-crypt.com/SMF/Templates/SMF-Template-MSN-10-Year-Summaries.xls>
26624
2014-03-02 01:16 AM
A template that shows the MSN 10-Year summaries of Key Ratio, Income Statement, and Balance Sheet items. Basically, this is just element numbers 153 through 352 shown in their web page context.

http://ogres-crypt.com/icons/xls.icoSMF-Template-MSN-Analyst-Opinions.xls <http://ogres-crypt.com/SMF/Templates/SMF-Template-MSN-Analyst-Opinions.xls>
22016
2014-03-02 01:10 AM
A template that shows the MSN Analyst Estimates and Recommendations. Basically, this is just element numbers 353 through 478 shown in their web page context.

http://ogres-crypt.com/icons/xls.icoSMF-Template-MSN-Key-Ratio-Comparisons.xls <http://ogres-crypt.com/SMF/Templates/SMF-Template-MSN-Key-Ratio-Comparisons.xls>
25088
2014-03-02 01:09 AM
A template that shows the MSN comparison of Key Ratios between a company, their associated industry, and the S&P 500. Basically, this is just element numbers 48 through 152 shown in their web page context.


From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, October 07, 2015 5:43 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] RCHGetTableCell-Template-MSN-Financial-Statements.xls


Dear Randy, I tried using this spreadsheet but I don't seem to be getting anything from it. it's probably the ticker format. can u give me an example for a US stock in MSN ticker format and a HK ticker format if possible? say HSBC which is 0005.HK in reuters ric, many thanks.

P.S. do you have an excel template for the MSN summary pages? like the Key Stats Page etc?

Wed Oct 7, 2015 7:21 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sorry, but all of the MSN templates are now obsolete. They dropped so much
data when they revamped their web site last year...

The few times I've gone back to look at it, the new web page data wasn't
even available for the add-in to extract it. So I just kind of gave up on
them.

I just looked at the available web pages on MMM, and saw nothing of value.
Why "Key Stats" page are you referring to? I didn't see one.

On Wed, Oct 7, 2015 at 2:43 AM, andy_c_chan@... wrote:

> Dear Randy, I tried using this spreadsheet but I don't seem to be getting
> anything from it. it's probably the ticker format. can u give me an
> example for a US stock in MSN ticker format and a HK ticker format if
> possible? say HSBC which is 0005.HK in reuters ric, many thanks.
>
> P.S. do you have an excel template for the MSN summary pages? like the Key
> Stats Page etc?
>

Wed Oct 7, 2015 8:04 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

If the MSN templates are obsolete, shouldn't you remove them?

Wouldn't it be a good idea to remove all obsolete files?

Everyone have a great time, I'm heading to Helen GA for the Oktoberfest. Had to use a timeshare week or lose it.

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com]
Sent: Wednesday, October 07, 2015 10:21 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] RCHGetTableCell-Template-MSN-Financial-Statements.xls


Sorry, but all of the MSN templates are now obsolete. They dropped so much data when they revamped their web site last year...
The few times I've gone back to look at it, the new web page data wasn't even available for the add-in to extract it. So I just kind of gave up on them.
I just looked at the available web pages on MMM, and saw nothing of value. Why "Key Stats" page are you referring to? I didn't see one.

On Wed, Oct 7, 2015 at 2:43 AM, andy_c_chan@... wrote:
Dear Randy, I tried using this spreadsheet but I don't seem to be getting anything from it. it's probably the ticker format. can u give me an example for a US stock in MSN ticker format and a HK ticker format if possible? say HSBC which is 0005.HK in reuters ric, many thanks.
P.S. do you have an excel template for the MSN summary pages? like the Key Stats Page etc?


Wed Oct 7, 2015 8:28 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I've tagged them as obsolete in the descriptions. I don't really want to
remove them, because there still may be some value in seeing the type of
data they used to have and how it was presented.

And I kind of hope they may bring them back, but...

On Wed, Oct 7, 2015 at 7:55 AM, 'Kermit W. Prather' kermitp@... wrote:

>
>
> If the MSN templates are obsolete, shouldn't you remove them?
>
>
>
> Wouldn't it be a good idea to remove all obsolete files?
>
>
>
> Everyone have a great time, I'm heading to Helen GA for the Oktoberfest.
> Had to use a timeshare week or lose it.
>

Wed Oct 7, 2015 8:34 am (PDT) . Posted by:

ridgebacksexcel

Randy,


I am having trouble getting a formula to work.


Here is the formula:


=RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/aapl-buyback/",1,"$Amount Authorized:")


That works fine for AAPL.


So does: =RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/gild-buyback/",1,"$Amount Authorized:")


Works good for GILD.


But if I use this for instance to find GLW:


=RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/glw-buyback/",1,"$Amount Authorized:")


It does not. I am not sure why.


The only change is the stock ticker.


The page pulls up fine and the data is there but I can't seem to extract it.


I have tried other tickers (JNJ for instance) with the same result.


I know the case has to be lower in the URL to work. Uppercase does not.


I registered and can get unlimited page views.


Here is the source code if you need that as well.


For AAPL:


</table>
<div style="width:185px; border-top: 1px solid #706FEB; padding-top: 18px; padding-bottom: 18px; overflow: hidden;">
<center><SCRIPT LANGUAGE="JavaScript">
<!--
OAS_AD('Position4');
//-->
</SCRIPT></div></center>
</td>
<td colspan="2" valign="middle" width="816" height="93" style="width: 816px; background-color: #FFFFFF; height: 93px; border-bottom: 1px dotted #AAAAAA">
<table border="0" cellspacing="0" cellpadding="0" width="817">
<tr><td rowspan="3" align="center" style="background-color:#F6F6F6;" valign="middle" style="width: 728px;"><SCRIPT LANGUAGE="JavaScript">
<!--
OAS_AD('Top');
//-->
</SCRIPT></td>
<td width="88" height="31"><a href="https://www.portfoliochannel.com/"><img src="https://www.etfchannel.com/pc88x31.gif" width="88" height="31" border="0"></a></td></tr>
<tr><td width="88" height="31"><a href="https://www.chartzero.com/"><img alt="ChartZero" border="0" width="88" height="31" src="https://www.chartzero.com/chartzero88x31.gif"></a></td>
</tr><tr><td width="88" height="31"><a href="https://www.marketnewsvideo.com/"><img src="https://www.etfchannel.com/mnv88x31.gif" width="88" height="31" border="0"></a></td></tr></table>
<center>
<DIV style="border-top: 1px solid #EEEEBB; margin-left: 0px; margin-right: 0px; margin-bottom: 2px; margin-top: 0px; background: #FFFFDD; width:816px;">
<font face="Arial"><!-- TheOnlineInvestorTopTextLink816x22 --><div id='div-gpt-ad-1398882803287-0' style='width:816px; height:22px;'><script type='text/javascript'>
googletag.cmd.push(function() { googletag.display('div-gpt-ad-1398882803287-0'); });
</script></div></font></DIV>
</center>
</td>
</tr>
<tr>
<td align="left" valign="top" width="516" height="1424" style="width: 480px; height: background-color: #FFFFFF; padding: 18px;"><font face="Arial">
<font face="Trebuchet MS" size="3">
<div style="border-bottom: 1px dotted #666666; width:100%">
<img border="0" align="absmiddle" alt="AAPL Buyback image" style="margin-bottom: 4px;" src="/buybacks/aapl-buyback.png" width="300" height="40">
</div>

<p>
<h1>Apple Buyback</h1><p><small>
<i>OLI recorded this information on 4/27/2015</i>
<table border="0" cellspacing="0" cellpadding="2">

<tr bgcolor="#FFFFF2">
<td align="left"><font face="Arial" size="2">Company:</font></td>
<td align="left"><font face="Arial" size="2"><b>Apple</b></td>
</tr>
<tr bgcolor="#FFFFFF">
<td align="left"><font face="Arial" size="2">Buyback:</font></td>
<td align="left"><font face="Arial" size="2"><b>AAPL buyback</b></td>
</tr>
<tr bgcolor="#FFFFFF">
<td align="left" nowrap><font face="Arial" size="2">$Amount Authorized:</font></td>
<td align="left"><font face="Arial" size="2"><b>$200,000,000,000 </b></td>
</tr>
<tr bgcolor="#FFFFF2">
<td align="left" valign="top"><font face="Arial" size="2">Buyback Details:</font></td>
<td align="left"><font face="Arial" size="2"><b> Apple today announced that its Board of Directors has authorized an increase of more than 50 percent to the Company's program to return capital to shareholders. Under the expanded program, Apple plans to utilize a cumulative total of $200 billion of cash by the end of March 2017. </b></td>
</tr>
</table><p>




For GLW:


</table>
<div style="width:185px; border-top: 1px solid #706FEB; padding-top: 18px; padding-bottom: 18px; overflow: hidden;">
<center><SCRIPT LANGUAGE="JavaScript">
<!--
OAS_AD('Position4');
//-->
</SCRIPT></div></center>
</td>
<td colspan="2" valign="middle" width="816" height="93" style="width: 816px; background-color: #FFFFFF; height: 93px; border-bottom: 1px dotted #AAAAAA">
<table border="0" cellspacing="0" cellpadding="0" width="817">
<tr><td rowspan="3" align="center" style="background-color:#F6F6F6;" valign="middle" style="width: 728px;"><SCRIPT LANGUAGE="JavaScript">
<!--
OAS_AD('Top');
//-->
</SCRIPT></td>
<td width="88" height="31"><a href="https://www.portfoliochannel.com/"><img src="https://www.etfchannel.com/pc88x31.gif" width="88" height="31" border="0"></a></td></tr>
<tr><td width="88" height="31"><a href="https://www.chartzero.com/"><img alt="ChartZero" border="0" width="88" height="31" src="https://www.chartzero.com/chartzero88x31.gif"></a></td>
</tr><tr><td width="88" height="31"><a href="https://www.marketnewsvideo.com/"><img src="https://www.etfchannel.com/mnv88x31.gif" width="88" height="31" border="0"></a></td></tr></table>
<center>
<DIV style="border-top: 1px solid #EEEEBB; margin-left: 0px; margin-right: 0px; margin-bottom: 2px; margin-top: 0px; background: #FFFFDD; width:816px;">
<font face="Arial&quot;><!-- TheOnlineInvestorTopTextLink816x22 --><div id='div-gpt-ad-1398882803287-0' style='width:816px; height:22px;'><script type='text/javascript&#39;>
googletag.cmd.push(function() { googletag.display('div-gpt-ad-1398882803287-0'); });
</script></div></font></DIV>
</center>
</td>
</tr>
<tr>
<td align="left" valign="top" width="516" height="1424" style="width: 480px; height: background-color: #FFFFFF; padding: 18px;"><font face="Arial&quot;>
<font face="Trebuchet MS" size="3">
<div style="border-bottom: 1px dotted #666666; width:100%">
<img border="0" align="absmiddle" alt="GLW Buyback image" style="margin-bottom: 4px;" src="/buybacks/glw-buyback.png" width="300" height="40">
</div>

<p>
<h1>Corning Incorporated Buyback</h1><p><small>
<i>OLI recorded this information on 7/15/2015</i>
<table border="0" cellspacing="0" cellpadding="2">

<tr bgcolor="#FFFFF2">
<td align="left"><font face="Arial&quot; size="2">Company:</font></td>
<td align="left"><font face="Arial&quot; size="2"><b>Corning Incorporated</b></td>
</tr>
<tr bgcolor="#FFFFFF">
<td align="left"><font face="Arial&quot; size="2">Buyback:</font></td>
<td align="left"><font face="Arial&quot; size="2"><b>GLW buyback</b></td>
</tr>
<tr bgcolor="#FFFFFF">
<td align="left" nowrap><font face="Arial&quot; size="2">$Amount Authorized:</font></td>
<td align="left"><font face="Arial&quot; size="2"><b>$2,000,000,000 </b></td>
</tr>
<tr bgcolor="#FFFFF2">
<td align="left" valign="top"><font face="Arial&quot; size="2">Buyback Details:</font></td>
<td align="left"><font face="Arial&quot; size="2"><b>Corning Incorporated (NYSE:GLW) today announced that its Board of Directors has authorized a new $2 billion share repurchase program of the company's common stock. The program will expire on December 31, 2016. The board also declared a quarterly dividend on the company's common stock of $0.12 per share, payable on September 30, 2015, to holders of record on August 31, 2015.</b></td>
</tr>
</table><p>




Thanks for your help.








Wed Oct 7, 2015 9:38 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

I don't know what to tell you. All are working fine here:

*Ticker* *Buyback Amount* AAPL $200,000,000,000.00 GILD $15,000,000,000.00
GLW $2,000,000,000.00 JNJ $5,000,000,000.00
I used this formula:

=RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/"&LOWER(C5)&"-buyback/",1,"$Amount
Authorized:")

...and copied down. I did get errors when I refreshed, but it was because a
page was popping up to tell me I had a limit of 6 free page views.

On Wed, Oct 7, 2015 at 8:34 AM, tmallen2@... wrote:

>
> I am having trouble getting a formula to work.
>
> Here is the formula:
>
> =RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/aapl-buyback/",1,"$Amount
> Authorized:")
>
> That works fine for AAPL.
>
> So does: =RCHGetTableCell("
> https://www.theonlineinvestor.com/buybacks/gild-buyback/",1,"$Amount
> Authorized:")
>
> Works good for GILD.
>
> But if I use this for instance to find GLW:
>
> =RCHGetTableCell("https://www.theonlineinvestor.com/buybacks/glw-buyback/",1,"$Amount
> Authorized:")
>
> It does not. I am not sure why.
>
> The only change is the stock ticker.
>
> The page pulls up fine and the data is there but I can't seem to extract
> it.
>
> I have tried other tickers (JNJ for instance) with the same result.
>
> I know the case has to be lower in the URL to work. Uppercase does not.
>
> I registered and can get unlimited page views.
>
>
>

Wed Oct 7, 2015 10:46 am (PDT) . Posted by:

ridgebacksexcel

Ok Thanks Randy.


Somehow the site puts a limit on 6 page views in Excel even though I have registered and can look at the website's pages as much as I want. Excel must not be able to get around the limit. Not sure why.


I will keep playing with it but I wanted to make sure I had the right formula.

Wed Oct 7, 2015 10:51 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Sounds like you may not have the security cookie created properly for EXCEL
and the add-in to use. Try logging in with the EXCEL Web Query dialog
(keyboard shortcut alt+d+d+w).

On Wed, Oct 7, 2015 at 10:46 AM, tmallen2@... wrote:

>
> Ok Thanks Randy.
>
> Somehow the site puts a limit on 6 page views in Excel even though I have
> registered and can look at the website's pages as much as I want. Excel
> must not be able to get around the limit. Not sure why.
>
> I will keep playing with it but I wanted to make sure I had the right
> formula.
>

Wed Oct 7, 2015 2:25 pm (PDT) . Posted by:

ridgebacksexcel

Yes I tried the web query sign in and that seems to help.


It doesn't work for all my tickers but most of them.


Do you know a good way to extract the date from the line above that says: OLI recorded this information on xx/xx/xxxx ?


I just want the date if possible.


I have this right now but it is not perfect:


=RIGHT(smfstrExtr(smfGetTagContent("https://www.theonlineinvestor.com/buybacks/"&LOWER($C4)&"-buyback/","tr",-2,"Company:"),"on ","<"),9)


It would be easier if I could just find a better site with buyback data to extract. Otherwise I might just have to keep a manual record of the web pages.



---In smf_addin@yahoogroups.com, <rharmelink@...> wrote :

Sounds like you may not have the security cookie created properly for EXCEL and the add-in to use. Try logging in with the EXCEL Web Query dialog (keyboard shortcut alt+d+d+w).

On Wed, Oct 7, 2015 at 10:46 AM, tmallen2@... wrote:

Ok Thanks Randy.


Somehow the site puts a limit on 6 page views in Excel even though I have registered and can look at the website's pages as much as I want. Excel must not be able to get around the limit. Not sure why.


I will keep playing with it but I wanted to make sure I had the right formula.








Wed Oct 7, 2015 4:46 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Although the results are going to be the same, I would have gone with:

=smfStrExtr(smfGetTagContent("https://www.theonlineinvestor.com/buybacks/"&LOWER($C4)&"-buyback/","i",-1,"recorded
this information")," on ","~")

The "~" is used to indicate either the start of the string (as the 2nd
parameter) or the end of the string (as the 3rd parameter).

You could also put a DATEVALUE() function around it to convert it to an
EXCEL serial date.

On Wed, Oct 7, 2015 at 2:25 PM, tmallen2@bellsouth.net [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
>
> Yes I tried the web query sign in and that seems to help.
>
> It doesn't work for all my tickers but most of them.
>
> Do you know a good way to extract the date from the line above that says:
> OLI recorded this information on xx/xx/xxxx ?
>
> I just want the date if possible.
>
> I have this right now but it is not perfect:
>
> =RIGHT(smfstrExtr(smfGetTagContent("
> https://www.theonlineinvestor.com/buybacks/"&LOWER($C4)&"-buyback/","tr",-2,"Company:"),"on
> ","<"),9)
>
> It would be easier if I could just find a better site with buyback data to
> extract. Otherwise I might just have to keep a manual record of the web
> pages.
>

Wed Oct 7, 2015 9:08 pm (PDT) . Posted by:

yolande_tulasse

Hi Randy,

First off thanks for this wonderful Add In.

I'm having some problems today with YCharts. I'm extracting quarterly data from YCharts to an Excel table
using the following (where A7 obviously is the ticker):

=RCHGetHTMLTable("http://ycharts.com/financials/"&$A$7&"/income_statement/quarterly";,"Income (Quarterly)",-1,"",1)


It's worked perfectly until today... Now it doesn't return anything anymore. Any idea? Does the same thing happens to you?


Thanks a lot,
Yolande

Wed Oct 7, 2015 11:18 pm (PDT) . Posted by:

yolande_tulasse

No Worries, dumb me finally has got it. They've changed to a fully HTTPS website... Just had to change http by https...

Cheers Randy, keep up the awesome work
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar