Kamis, 25 Juli 2013

[smf_addin] Digest Number 2709

15 New Messages

Digest #2709
1a
1b
2a
trouble with RCHGetHTMLTable by "mbadr911" mbadr911
2b
Re: trouble with RCHGetHTMLTable by "Randy Harmelink" rharmelink
3a
Re: Problem RCHGetYahooHistory date result ... by "Kermit W. Prather" kermitpra
4a
4b
4c
5a
Re: 10 Year dates; Historic Price to Sales Ratios? by "jtravisano2002" jtravisano2002
6
Acquisition Revenue by "TomD" tcdriscoll

Messages

Thu Jul 25, 2013 1:29 am (PDT) . Posted by:

"ajtalukdar" ajtalukdar


Thanks, this is very helpful..

--- In smf_addin@yahoogroups.com, "Russell M" <ru88ell@...> wrote:
>
> I too was worried about the ADVFN feeds so I emailed them...here is their response:
>
> "Our financials for USA listed companies are using an old outdated feed from our service provider. Last week we began updating our financials to use the latest feed and hope to have it working soon as possible. Likely next week will be the earliest we can make the new financials available on the website."
>
> We should be ok soon....
>
>
> --- In smf_addin@yahoogroups.com, "ajtalukdar&quot; <ajtalukdar@> wrote:
> >
> > Hi Randy,
> >
> > It is now amply clear that Advfn is not updating quarterly earnings data. Is there any other data source that can be used as an alternative, apart from Google Finance?
> >
> > I have tried Google Finance, but have found many errors in their data.
> >
> >
> >
> > --- In smf_addin@yahoogroups.com, "ajtalukdar&quot; <ajtalukdar@> wrote:
> > >
> > > Hello friends,
> > >
> > > I have noticed that lately advfn quarterly data is not getting updated on their website. Intiially I thought it was a problem with the add-in, but found that the advfn site itself is where the problem is. For instance, the quarterly data for none of the companies that have declared results this quarter have beenn updated - e.g. Alcoa, Yum Brands, wells fargo etc..
> > >
> > > The quarterly reports of some stock from the previous quarter are also pending - e.g. Oracle, Nike, Fedex, etc.
> > >
> > > Is anyone else facing this problem?
> > >
> >
>

Thu Jul 25, 2013 1:32 am (PDT) . Posted by:

"ajtalukdar" ajtalukdar

Thanks Randy... I will try using GuruFocus while Advfn get their act together.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> GuruFocus?
>
> http://www.gurufocus.com/financials_print.php?symbol=AA
>
> MorningStar? NASDAQ? Yahoo? MSN?
>
> I don't know what quarterly data you're looking for. Google:
>
> AA "income statement" quarterly
>
> ...and you'll find lots of sources of data.
>
> On Tue, Jul 23, 2013 at 11:34 PM, ajtalukdar <ajtalukdar@...> wrote:
>
> >
> > It is now amply clear that Advfn is not updating quarterly earnings data.
> > Is there any other data source that can be used as an alternative, apart
> > from Google Finance?
> >
> > I have tried Google Finance, but have found many errors in their data.
> >
>

Thu Jul 25, 2013 6:15 am (PDT) . Posted by:

"mbadr911" mbadr911

Hi,

I am trying to get a table but excel hangs every time i enter the following equation:

=RCHGetHTMLTable("http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport"
,"s name",-1,"",3)

Thanks in advance!

-Mo

Thu Jul 25, 2013 10:56 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

It has numerous tables within tables. Far too complicated for my little
table parsing routine. Sorry.

With all of those tables, any extraction with other functions would be
problematic as well.

You best bet might be to use the smfGetTagContent() function and retrieve
consecutive table cells. See if you can find a pattern that can extract the
data you want. For example, to get the filer name:

=smfstrExtr(smfGetTagContent("
http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport","td",18,"s
name"),"Report&quot;">","<")

...and then increment that 18 by 28 to get the next one:

=smfstrExtr(smfGetTagContent("
http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport","td",46,"s
name"),"Report&quot;">","<")

...and so forth. But that assumes the coding of the table will be
consistent from line to line, without exceptions.

On Thu, Jul 25, 2013 at 3:25 AM, mbadr911 <badrmh@hotmail.com> wrote:

>
> I am trying to get a table but excel hangs every time i enter the
> following equation:
>
> =RCHGetHTMLTable("
> http://www.j3sg.com/Reports/Stock-Insider/Generate.php?tickerLookUp=AAPL&pageNumber=1&descending=1&sortBy=PeriodOfReport
> "
> ,"s name",-1,"",3)
>

Thu Jul 25, 2013 6:32 am (PDT) . Posted by:

"Kermit W. Prather" kermitpra

I had what appears to be the same problem yesterday. Took me awhile to
figure out the array was not large enoughto handle all the days I was
attempting to retrieve. Excel truncates the number of dates to fit the
array.

1. Select the array, starting in the upper left corner and down to the
lower right corner for the new number of rows you need
2. Press F2
3. Then select Control, Shift, enter to create the larger array

I hope that fixes your problem.

-----Original Message-----
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf
Of rob_gries
Sent: Wednesday, July 24, 2013 6:44 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Problem RCHGetYahooHistory date result ...


Dear all,

I have a workbook organized in different sheets, every sheet represents a
specific symbol.

I am having a problem with some symbols, in some cases the complete period
of historic data is retrieved and in some cases the recent past is not
retrieved and the values start from some weeks ago only. The parameters
though do not change from sheet to sheet.

What might be the reason for this?

Please see the images within the Photos section (I have created a specific
Album for this), that should illustrate the formulas I am using and the
wrong result they bring.

The symbol PETR4.SA brings the right values and the screenshot is taken for
LAME4.SA, which shows the wrong history.

What shall I take care of?

Thanks a lot for your ideas!

RG




------------------------------------

Yahoo! Groups Links


(Yahoo! ID required)

<mailto:smf_addin-fullfeatured@yahoogroups.com>
smf_addin-fullfeatured@yahoogroups.com


Thu Jul 25, 2013 8:24 am (PDT) . Posted by:

"waterboy5555" waterboy5555

Sorry. I meant the URL is in cell AF4 and I am referencing AF4 in the formula. I have refreshed everything and done smfforcerecalculation and am still getting an error readout. I have even tried adding " " to the link. What else do you think the issue could be? It would be great to be able to have the link in a cell and have the formula reference the cell.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> If A4 has your URL, why are you referring to cell AF4?
>
> Cell references as parameters are fine. Just as in normal EXCEL formulas.
>
> On Wed, Jul 24, 2013 at 3:00 PM, waterboy5555 <bopha99@...> wrote:
>
> > Is there a way to put the web link in a cell on excel and have the formula
> > read the link from the cell? I have:
> >
> > =RCHGetTableCell(AF4,1,"Long%","Foreign Stocks")
> >
> > where A4 is
> > http://portfolio.morningstar.com/Rtport/Reg/XRayOverview.aspx?ViewPage=11&dt=851
> >
> > But, with the formula I have with AF4, I get an error readout. I would
> > like to be able to put the link in a cell so I would not have to go back
> > and manually re enter the links.
> >
>

Thu Jul 25, 2013 8:38 am (PDT) . Posted by:

"waterboy5555" waterboy5555

Ok. I've got it to work while referencing a cell. For some reason it took me a couple times reloading the page in IE before the formula kicked in in Excel. Do you know why this could be the problem?

--- In smf_addin@yahoogroups.com, "waterboy5555&quot; <bopha99@...> wrote:
>
> Sorry. I meant the URL is in cell AF4 and I am referencing AF4 in the formula. I have refreshed everything and done smfforcerecalculation and am still getting an error readout. I have even tried adding " " to the link. What else do you think the issue could be? It would be great to be able to have the link in a cell and have the formula reference the cell.
>
> --- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@> wrote:
> >
> > If A4 has your URL, why are you referring to cell AF4?
> >
> > Cell references as parameters are fine. Just as in normal EXCEL formulas.
> >
> > On Wed, Jul 24, 2013 at 3:00 PM, waterboy5555 <bopha99@> wrote:
> >
> > > Is there a way to put the web link in a cell on excel and have the formula
> > > read the link from the cell? I have:
> > >
> > > =RCHGetTableCell(AF4,1,"Long%","Foreign Stocks")
> > >
> > > where A4 is
> > > http://portfolio.morningstar.com/Rtport/Reg/XRayOverview.aspx?ViewPage=11&dt=851
> > >
> > > But, with the formula I have with AF4, I get an error readout. I would
> > > like to be able to put the link in a cell so I would not have to go back
> > > and manually re enter the links.
> > >
> >
>

Thu Jul 25, 2013 10:59 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

Not a clue. Sorry.

On Thu, Jul 25, 2013 at 8:38 AM, waterboy5555 <bopha99@aol.com> wrote:

> Ok. I've got it to work while referencing a cell. For some reason it
> took me a couple times reloading the page in IE before the formula kicked
> in in Excel. Do you know why this could be the problem?
>

Thu Jul 25, 2013 8:37 am (PDT) . Posted by:

"jtravisano2002" jtravisano2002

Thanks Randy! You nailed it, I guess I had an old version of the plug-in (although it appears my definitions were from the correct date). So I think I'll be able to use the 10 year P/S and dates from MSN10.
.. When the Source = MSN codes worked and the Source = MSN10 codes didn't work I thought MSN killed the info.

Thank you for all the work you've put in on both the add-in and the excellent support of it that you provide! Very much appreciated!

Jake

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Which 10-year MSN items are you talking about? I just tried this workbook
> from the FILES area and it worked fine for me:
>
> SMF-Template-MSN-10-Year-Summaries.xls
>
> Are you using an older version of the add-in, or have old element
> definitions for MSN? I did have to change MSN element definitions last year
> (they seem to go through some type of redesign EVERY dang year).
>
> I believe AdvFN has price to sale ratios back 10 years.
>
> I'm not sure what date values you are referring to.
>
> On Mon, Jul 22, 2013 at 8:01 PM, jtravisano2002
> <jacob.travisano@...>wrote:
>
> >
> > I'm using the RCHGetElementNumber()function to retrieve values. It
> > appears that the MSN elements are dead. I read in one of the threads there
> > was a site redesign. Is that why these elements no longer work?
> >
> > I was hoping to use the MSN elements because I was interested in some of
> > the 10 year data values.
> >
> > Is there another way to retrieve the last 10 years of price to sales data?
> >
> > Is there a way to grab the date values for each year so the dates
> > dynamically update?
> >
>

Thu Jul 25, 2013 12:44 pm (PDT) . Posted by:

"TomD" tcdriscoll

Hi,

I'm trying to run a report that automatically calculates organic growth, top line, by subtracting fx and revenue from acquisitions. I can use GetElementNumber(Ticker,3282) for fx adjustments, but I can't find an entry that will capture revenue from acquisitions. Any thoughts or suggestions appreciated.

Regards,

tom

Thu Jul 25, 2013 4:56 pm (PDT) . Posted by:

"quasiwasp" quasiwasp

Hi there,

I just started using the Add In and it's been going very well, until we installed it on my bosses computer.

We installed the add in and deployed it on mine and one supervisors computer. We are both running Windows 7, but he has Office 10 and I have Office 2003.

We used the "RCHGetYahooQuotes" primarily to update current stock quotes, changes, and to get Stock names. Even the Refresh Macro (smfForceRecalculation) worked fine.

When I installed and ran the add in on my bosses computer, I got nothing but the "NAME?" and "VALUE". Then I realized that the add in was running off of Dropbox (the online document sharing).

I routed the add in through C/:Program Files and it worked great. but when I tried to open the same spreadsheet on mine or my supervisors computer, we would get the "NAME?" and "VALUE". I realized that instead of the "RCHGetYahooQuotes" Function, there was an "RCHGetElement" Function. Even though I type, "RCHGetYahooQuotes" if I close the excel sheet and open it on my bosses computer, he receives the "NAME" and "VALUE" and the functions are trying to get answers from the Dropbox File where we originally put the SMF Add In folder.

Are there any thoughts on why this is happening/how to fix? I tried deleting the add in on my bosses computer. Perhaps just deleting and reinstalling on all computers?

My sincerest apologies if any of this is confusing, i'm not very technically inclined.

Thu Jul 25, 2013 5:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

That happens because whenever you save a workbook, EXCEL saves the
hard-coded locations of anything external, including the add-in.

Then, when you open up the workbook, it looks for the add-in in that
hard-coded location. If it can't find it there, it marks it as an
"unresolved link" (you should be getting an error message about it when you
open the workbook). That means you have to manually fix all of those
unresolved links. However, you can try the smfFixLinks macro that is part
of the add-in. I use it whenever I have these location problems.

Take a look at the "Location Errors" item in the LINKS area of the Yahoo
group. It has some additional information.

I don't know what is going on with your RCHGetYahooQuotes() and
RCHGetElementNumber() transposition. That behavior makes no sense to me.

Someone else recently mentions problems with locating the add-in on
DropBox. It must not act like a local drive.

On Thu, Jul 25, 2013 at 4:54 PM, quasiwasp <joannapalmerjunk@gmail.com>wrote:

> Hi there,
>
> I just started using the Add In and it's been going very well, until we
> installed it on my bosses computer.
>
> We installed the add in and deployed it on mine and one supervisors
> computer. We are both running Windows 7, but he has Office 10 and I have
> Office 2003.
>
> We used the "RCHGetYahooQuotes" primarily to update current stock quotes,
> changes, and to get Stock names. Even the Refresh Macro
> (smfForceRecalculation) worked fine.
>
> When I installed and ran the add in on my bosses computer, I got nothing
> but the "NAME?" and "VALUE". Then I realized that the add in was running
> off of Dropbox (the online document sharing).
>
> I routed the add in through C/:Program Files and it worked great. but when
> I tried to open the same spreadsheet on mine or my supervisors computer, we
> would get the "NAME?" and "VALUE". I realized that instead of the
> "RCHGetYahooQuotes" Function, there was an "RCHGetElement" Function. Even
> though I type, "RCHGetYahooQuotes" if I close the excel sheet and open it
> on my bosses computer, he receives the "NAME" and "VALUE" and the functions
> are trying to get answers from the Dropbox File where we originally put the
> SMF Add In folder.
>
> Are there any thoughts on why this is happening/how to fix? I tried
> deleting the add in on my bosses computer. Perhaps just deleting and
> reinstalling on all computers?
>
> My sincerest apologies if any of this is confusing, i'm not very
> technically inclined.
>

Thu Jul 25, 2013 5:37 pm (PDT) . Posted by:

"Gururaj Rao" gurujapan

Hi Randy,

On Thu, Jul 25, 2013 at 10:24 AM, Randy Harmelink <rharmelink@gmail.com>wrote:

>
>
> Actually, element #63 is from MSN, not MorningStar. On the MSN company
> name lookup, I don't see an option for a country of India. So I don't know
> if they carry data on them.
>
> You can get the yield from Morningstar with:
>
> =smfConvertData(smfGetTagContent("
> http://quotes.morningstar.com/stock/c-header?&t=XNSE:WIPRO",
> "span",0,"vkey=""ProjectedYield"""))
>
> So I have to use the URL each time to capture data and just entering the
TICKER SYMBOL to use the functions will not work with MSN or Morningstar."
This tells me the enormous time and you must have put in to help us
capture various data by just entering the TICKER SYMBOL!

Incidentally, I tried using the Yahoo Finance Functions by entering Indian
Ticker Symbols and they work fine!

Thanks for the prompt assistance and for the amazing work you have put in!

With best regards,
Gururaj Rao
Gardening Blog: http://japanesegarden.wordpress.com

Thu Jul 25, 2013 6:01 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

You wouldn't need to re-enter the URL each time, with a new ticker symbol.
You could just use normal EXCEL processing to create a concatenated URL.
For example, if your ticker symbol is in cell A2, the formula would be:

=smfConvertData(smfGetTagContent("
http://quotes.morningstar.com/stock/c-header?&t="&A2,
"span",0,"vkey=""ProjectedYield"""))

On Thu, Jul 25, 2013 at 5:37 PM, Gururaj Rao <honyakusha@gmail.com> wrote:

>
> On Thu, Jul 25, 2013 at 10:24 AM, Randy Harmelink <rharmelink@gmail.com>wrote:
>
>>
>> Actually, element #63 is from MSN, not MorningStar. On the MSN company
>> name lookup, I don't see an option for a country of India. So I don't know
>> if they carry data on them.
>>
>> You can get the yield from Morningstar with:
>>
>> =smfConvertData(smfGetTagContent("
>> http://quotes.morningstar.com/stock/c-header?&t=XNSE:WIPRO",
>> "span",0,"vkey=""ProjectedYield"""))
>>
>> So I have to use the URL each time to capture data and just entering the
> TICKER SYMBOL to use the functions will not work with MSN or Morningstar."
> This tells me the enormous time and you must have put in to help us
> capture various data by just entering the TICKER SYMBOL!
>
> Incidentally, I tried using the Yahoo Finance Functions by entering Indian
> Ticker Symbols and they work fine!
>
> Thanks for the prompt assistance and for the amazing work you have put in!
>

Thu Jul 25, 2013 8:07 pm (PDT) . Posted by:

"Joel" joelfp2000

Randy,

Thanks so much for all of your help with this great add-in!

Question, I am using the quicktake print report (=RCHGetTableCell("http://quicktake.morningstar.com/fundnet/printreport.aspx?symbol="&$A2,1,"average eff maturity"))to try to get the Bond Quality (eg. AAA, AA, A, BBB, etc) and also the Bond Sector Ratings (eg. Government, treasuries, tips, us agency; Corporate, mortgage pass through, etc.). Do you have any idea how I can do this?

Thanks in advance!

Joel

Diposting oleh EXCEL FEED LEARNING di 20.07

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)