Kamis, 28 Februari 2013

[belajar-excel] Digest Number 2124

1 New Message

Digest #2124
1
insert photo ke template excel by "pengadapan surbakti" aph4n5

Message

Thu Feb 28, 2013 7:09 am (PST) . Posted by:

"pengadapan surbakti" aph4n5

dear excel

mohon bantuannya cara masukkan photo ke template excel secara otomatis 
saya punya case data nya di sheet 1 dan keterangan di sheet 1 ditampilkan dengan photo di sheet 2.
dimana photonya tersimpan dalam 1 folder.
data terlampir

 
thank and regards    
GROUP FOOTER MESSAGE
---------------------------------------------------------------------
bergabung ke milis (subscribe), kirim mail kosong ke:
belajar-excel-subscribe@yahoogroups.com

posting ke milis, kirimkan ke:
belajar-excel@yahoogroups.com

berkunjung ke web milis
http://tech.groups.yahoo.com/group/belajar-excel/messages

melihat file archive / mendownload lampiran
http://www.mail-archive.com/belajar-excel@yahoogroups.com/
atau (sejak 25-Apr-2011) bisa juga di :
http://milis-belajar-excel.1048464.n5.nabble.com/

menghubungi moderators & owners: belajar-excel-owner@yahoogroups.com

keluar dari membership milis (UnSubscribe):
kirim mail kosong ke  belajar-excel-unsubscribe@yahoogroups.com
---------------------------------------------------------------------
READ MORE....

[smf_addin] Digest Number 2526

15 New Messages

Digest #2526
1a
Need help to capture data to a spreadsheet by "Kermit W. Prather" kermitpra
1b
Re: Need help to capture data to a spreadsheet by "Randy Harmelink" rharmelink
2a
Morningstar Template by "borja_delcampo" borja_delcampo
2b
Re: Morningstar Template by "Randy Harmelink" rharmelink
4b
Re: Next earnings date from excel cell? by "Randy Harmelink" rharmelink
5a
What is can’t ? by "almondtree" almondtree
5b
6a
SMF Add in Errors by "Chris Ball" peggysue281
6b
Re: SMF Add in Errors by "Randy Harmelink" rharmelink

Messages

Thu Feb 28, 2013 7:45 am (PST) . Posted by:

"Kermit W. Prather" kermitpra


Randy, I hate to ask but I have no idea how to capture the data I need from
this webpage http://dividata.com/stock/AGNC/dividend

I'm hoping you can show me how.

I want to capture just the Dividend Payment History from the webpage.
I can't figure out how to parse this into a spreadsheet. I have looked thru
the documentation
But can't figure out which function to use as this is in a list not in a
table.

Can you point me to the function or provide an example?

Thank You,
Kermit

Data in Webpage

Dividend Payment History

>

Ex-Dividend Date


Dividend Amount



    >
  • Dec 24, 2012

    $1.25


  • Sep 19, 2012

    $1.25


  • Jun 19, 2012

    $1.25


  • Mar 05, 2012

    $1.25


  • Dec 20, 2011

    $1.40


  • Sep 21, 2011

    $1.40


  • Jun 21, 2011

    $1.40


  • Mar 21, 2011

    $1.40


  • Dec 29, 2010

    $1.40


  • Sep 24, 2010

    $1.40


  • Jun 28, 2010

    $1.40


  • Mar 29, 2010

    $1.40


  • Dec 29, 2009

    $1.40


  • Sep 30, 2009

    $1.40


  • Jun 30, 2009

    $1.50


  • Mar 31, 2009

    $0.85


  • Dec 29, 2008

    $1.20


  • Sep 25, 2008

    $1.00


  • Jun 30, 2008

    $0.31




>


Thu Feb 28, 2013 8:47 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Just use:

=smfGetTagContent("http://dividata.com/stock/AGNC/dividend","p",...n...,">Ex-Dividend
Date")

...where "...n..." is 2, 4, 6, 8, ... for the dates and 3, 5, 7, 9, ... for
the amounts.

On Thu, Feb 28, 2013 at 8:45 AM, Kermit W. Prather
kermitp@tampabay.rr.com>wrote:

>
> Randy, I hate to ask but I have no idea how to capture the data I need
> from this webpage http://dividata.com/stock/AGNC/dividend ****
>
> ** **
>
> I’m hoping you can show me how.****
>
> ** **
>
> I want to capture just the Dividend Payment History from the webpage.****
>
> I can’t figure out how to parse this into a spreadsheet. I have looked
> thru the documentation****
>
> But can’t figure out which function to use as this is in a list not in a
> table. ****
>
> ** **
>
> Can you point me to the function or provide an example?****
>
> ** **
>
> Thank You,****
>
> Kermit****
>
> ** **
>
> *Data in Webpage*
>
> ** **
>
> Dividend Payment History ****
>
> ** **
>
>
>****
>
>

Ex-Dividend Date

****
>
>

Dividend Amount

****
>
>
****
>
>
    >****
    >
    >
  • Dec 24, 2012

    $1.25

  • ****
    >
    >
  • Sep 19, 2012

    $1.25

  • ****
    >
    >
  • Jun 19, 2012

    $1.25

  • ****
    >
    >
  • Mar 05, 2012

    $1.25

  • ****
    >
    >
  • Dec 20, 2011

    $1.40

  • ****
    >
    >
  • Sep 21, 2011

    $1.40

  • ****
    >
    >
  • Jun 21, 2011

    $1.40

  • ****
    >
    >
  • Mar 21, 2011

    $1.40

  • ****
    >
    >
  • Dec 29, 2010

    $1.40

  • ****
    >
    >
  • Sep 24, 2010

    $1.40

  • ****
    >
    >
  • Jun 28, 2010

    $1.40

  • ****
    >
    >
  • Mar 29, 2010

    $1.40

  • ****
    >
    >
  • Dec 29, 2009

    $1.40

  • ****
    >
    >
  • Sep 30, 2009

    $1.40

  • ****
    >
    >
  • Jun 30, 2009

    $1.50

  • ****
    >
    >
  • Mar 31, 2009

    $0.85

  • ****
    >
    >
  • Dec 29, 2008

    $1.20

  • ****
    >
    >
  • Sep 25, 2008

    $1.00

  • ****
    >
    >
  • Jun 30, 2008

    $0.31

  • ****
    >
    >
****
>
> ****
>
>
>

>

Thu Feb 28, 2013 8:26 am (PST) . Posted by:

"borja_delcampo" borja_delcampo

Hi. This is the first time I write in this site. I woud like to share with you a template I have made, that recovers from the morningstar webpage the information relative to the keyratios, and the financial, cash flow, and income sheets.
Does anybody know what i have to do to extract the valuation information from the morningstar webpage?
The url is as follows:

http://financials.morningstar.com/valuation/price-ratio.html?t=GOOG®ion=USA&culture=en-us

By the way, i will try to upload the template to the files directory, under the name "Morningstar.xlsx".
Thanks in advance

Thu Feb 28, 2013 8:53 am (PST) . Posted by:

"Randy Harmelink" rharmelink

That web page is dynamically generated, so there are no data items in the
source code of the web page for the add-in to extract.

If what you want is on this page, you can extract it:

http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=GOOG

For example, to get the 5-year average of P/E:

=RCHGetTableCell("
http://quicktake.morningstar.com/stocknet/printreport.aspx?symbol=GOOG",4,">Valuation
Ratios","Price/Earnings")

On Thu, Feb 28, 2013 at 9:11 AM, borja_delcampo borja_delcampo@yahoo.com>wrote:

> Hi. This is the first time I write in this site. I woud like to share with
> you a template I have made, that recovers from the morningstar webpage the
> information relative to the keyratios, and the financial, cash flow, and
> income sheets.
> Does anybody know what i have to do to extract the valuation information
> from the morningstar webpage?
> The url is as follows:
>
>
> http://financials.morningstar.com/valuation/price-ratio.html?t=GOOG®ion=USA&culture=en-us
>
> By the way, i will try to upload the template to the files directory,
> under the name "Morningstar.xlsx".
>

Thu Feb 28, 2013 8:27 am (PST) . Posted by:

"mdouglashaas" mdouglashaas


Randy,

I am using the formulas below to obtain ratings from thestreet.com. I
note that values received do not always compare exactly with those same
stock symbols when obtained directly through my browser going directly
to the web site and entering stock symbols manually.

Examples are:

From Web site using Browser compared to formula in Excel using SMF
add-in:

AAPL = B, Buy ; formula shows A-, Buy

WM = B+, buy ; formula shows A-, buy

AMP = A-, buy ; formula shows B, buy

BIN = C, Hold ; formula shows D, Sell

Some Stocks Symbols work OK, examples are:

AMPL = E+, sell formula shows the same

Has something changed on the site that is causing the
inaccuracies/variations?

Thanks for your great work,

Doug Haas

>
=TRIM(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/inc/basicjs\
/
"&A1&".js","LetterGradeRating",,LEN("lettergraderating")+3),5
http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","LetterGradeR\
ating",,LEN("lettergraderating")+3),5> ))
>
=TRIM(SUBSTITUTE(LEFT(RCHGetWebData("http://ratings.thestreet.com/tools/\
inc/basicjs/
"&A1&".js","CurrentRating"":",,LEN("currentrating")+3),4
http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","CurrentRatin\
g"":",,LEN("currentrating")+3),4> ),"""",""))

Thu Feb 28, 2013 9:03 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Two possibilities come to mind immediately:

1. The add-in has an old copy of the web item, either because it's
stored within the add-in, or because your Internet Options are not set up
to get a fresh copy of the web page each time it is visited. To learn more
about these issues, see the two smfForceRecalculation entries in the LINKS
area of the group. When you were seeing different values, were you using IE
as a browser? If not, your other browser may be set up to always get a
fresh copy of a web page, which may differ from a cached copy in IE.

2. The values update frequently, and it takes time to propagate to all
servers. I do note that AAPL is an A+ this morning...

On Thu, Feb 28, 2013 at 8:21 AM, mdouglashaas douglashaas4974@earthlink.net
> wrote:

> I am using the formulas below to obtain ratings from thestreet.com. I
> note that values received do not always compare exactly with those same
> stock symbols when obtained directly through my browser going directly to
> the web site and entering stock symbols manually.
>
> Examples are:
>
> From Web site using Browser compared to formula in Excel using SMF add-in:
>
> AAPL = B, Buy ; formula shows A-, Buy
>
> WM = B+, buy ; formula shows A-, buy
>
> AMP = A-, buy ; formula shows B, buy
>
> BIN = C, Hold ; formula shows D, Sell
>
> Some Stocks Symbols work OK, examples are:
>
> AMPL = E+, sell formula shows the same
>
> Has something changed on the site that is causing the
> inaccuracies/variations?
>
> Thanks for your great work,
>
> Doug Haas
>
> > =TRIM(LEFT(RCHGetWebData("
> http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","LetterGradeRating",,LEN("lettergraderating")+3),5http://ratings.thestreet.com/tools/inc/basicjs/%22&A1&%22.js%22,%22LetterGradeRating%22,,LEN(%22lettergraderating%22)+3),5>
> ))
> > =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("
> http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","CurrentRating"":",,LEN("currentrating")+3),4http://ratings.thestreet.com/tools/inc/basicjs/%22&A1&%22.js%22,%22CurrentRating%22%22:%22,,LEN(%22currentrating%22)+3),4>
> ),"""",""))
>

Thu Feb 28, 2013 10:47 am (PST) . Posted by:

"mdouglashaas" mdouglashaas

Randy,

I have manually visited the thestreet.com to obtain a rating for AAPL using 3 different PC's , two different browsers, (all with clean caches). In each case I show AAPL as rating = B, Buy.

I normally use IE10 as my default browser.

AM I still missing something?

Thanks Again.

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> Two possibilities come to mind immediately:
>
> 1. The add-in has an old copy of the web item, either because it's
> stored within the add-in, or because your Internet Options are not set up
> to get a fresh copy of the web page each time it is visited. To learn more
> about these issues, see the two smfForceRecalculation entries in the LINKS
> area of the group. When you were seeing different values, were you using IE
> as a browser? If not, your other browser may be set up to always get a
> fresh copy of a web page, which may differ from a cached copy in IE.
>
> 2. The values update frequently, and it takes time to propagate to all
> servers. I do note that AAPL is an A+ this morning...
>
> On Thu, Feb 28, 2013 at 8:21 AM, mdouglashaas
> wrote:
>
> > I am using the formulas below to obtain ratings from thestreet.com. I
> > note that values received do not always compare exactly with those same
> > stock symbols when obtained directly through my browser going directly to
> > the web site and entering stock symbols manually.
> >
> > Examples are:
> >
> > From Web site using Browser compared to formula in Excel using SMF add-in:
> >
> > AAPL = B, Buy ; formula shows A-, Buy
> >
> > WM = B+, buy ; formula shows A-, buy
> >
> > AMP = A-, buy ; formula shows B, buy
> >
> > BIN = C, Hold ; formula shows D, Sell
> >
> > Some Stocks Symbols work OK, examples are:
> >
> > AMPL = E+, sell formula shows the same
> >
> > Has something changed on the site that is causing the
> > inaccuracies/variations?
> >
> > Thanks for your great work,
> >
> > Doug Haas
> >
> > > =TRIM(LEFT(RCHGetWebData("
> > http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","LetterGradeRating",,LEN("lettergraderating")+3),5http://ratings.thestreet.com/tools/inc/basicjs/%22&A1&%22.js%22,%22LetterGradeRating%22,,LEN(%22lettergraderating%22)+3),5>
> > ))
> > > =TRIM(SUBSTITUTE(LEFT(RCHGetWebData("
> > http://ratings.thestreet.com/tools/inc/basicjs/"&A1&".js","CurrentRating"":",,LEN("currentrating")+3),4http://ratings.thestreet.com/tools/inc/basicjs/%22&A1&%22.js%22,%22CurrentRating%22%22:%22,,LEN(%22currentrating%22)+3),4>
> > ),"""",""))
> >
>

Thu Feb 28, 2013 1:13 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

When I go to:

http://ratings.thestreet.com/tools/inc/basicjs/AAPL.js

...I see A+. Maybe this is what you need?

=smfStrExtr(RCHGetWebData("
http://www.thestreet.com/quote/AAPL/details/analyst-ratings.html
","LetterGradeRating",100),":""","""")
=smfStrExtr(RCHGetWebData("
http://www.thestreet.com/quote/AAPL/details/analyst-ratings.html
","CurrentRating",100),":""","""")
On Thu, Feb 28, 2013 at 11:47 AM, mdouglashaas <
douglashaas4974@earthlink.net> wrote:

>
> I have manually visited the thestreet.com to obtain a rating for AAPL
> using 3 different PC's , two different browsers, (all with clean caches).
> In each case I show AAPL as rating = B, Buy.
>
> I normally use IE10 as my default browser.
>
> AM I still missing something?
>

Thu Feb 28, 2013 1:58 pm (PST) . Posted by:

"mdouglashaas" mdouglashaas

Yes, That is what I needed. Now I am confused -- what's the difference ?

Thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> When I go to:
>
> http://ratings.thestreet.com/tools/inc/basicjs/AAPL.js
>
> ...I see A+. Maybe this is what you need?
>
> =smfStrExtr(RCHGetWebData("
> http://www.thestreet.com/quote/AAPL/details/analyst-ratings.html
> ","LetterGradeRating",100),":""","""")
> =smfStrExtr(RCHGetWebData("
> http://www.thestreet.com/quote/AAPL/details/analyst-ratings.html
> ","CurrentRating&quot;,100),":""","""")
> On Thu, Feb 28, 2013 at 11:47 AM, mdouglashaas <
> douglashaas4974@...> wrote:
>
> >
> > I have manually visited the thestreet.com to obtain a rating for AAPL
> > using 3 different PC's , two different browsers, (all with clean caches).
> > In each case I show AAPL as rating = B, Buy.
> >
> > I normally use IE10 as my default browser.
> >
> > AM I still missing something?
> >
>

Thu Feb 28, 2013 8:51 am (PST) . Posted by:

"SCHRES" SCHRES

Randy can you use RCHGetYahooQuotes to optain the earnings date, and if you can what is the data code? Thanks

--- In smf_addin@yahoogroups.com, Randy Harmelink wrote:
>
> This is just a normal EXCEL operation of concatenation:
>
> =RCHGetTableCell("http://finance.yahoo.com/q?s="&A4,1,"Next Earnings
> Date:",,,,0)
>
> On Wed, Feb 27, 2013 at 11:13 AM, andyha8 wrote:
>
> >
> > I want to get the next earnings date for a list of stocks.
> >
> > In excel, I have a list of tickers in cells A1-A12
> >
> > Is there a way to use this formula:
> >
> > =RCHGetTableCell("http://finance.yahoo.com/q?s=BAC&ql=1",1,"Next Earnings
> > Date:",,,,0)
> >
> > to get the ticker from the cells instead of having to write the ticker
> > into the formula every time?
> >
>

Thu Feb 28, 2013 9:03 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Yahoo doesn't have a data code set up for it, as far as I know...

On Thu, Feb 28, 2013 at 9:51 AM, SCHRES schres@cox.net> wrote:

> Randy can you use RCHGetYahooQuotes to optain the earnings date, and if
> you can what is the data code?
>

Thu Feb 28, 2013 10:55 am (PST) . Posted by:

"almondtree" almondtree

#21481 and #21482

can’t
Â
    Â
>
> >
Is there a reason for for all this extra stuff in messages?
What is can’t ?

Thu Feb 28, 2013 1:16 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

It's usually the result of misinterpretation of characters by various email
clients and/or browsers. Not all of them used the same coding protocols.

On Thu, Feb 28, 2013 at 11:55 AM, almondtree almondtree@yahoo.com> wrote:

> #21481 and #21482
>
> can’t
> Â
> Â Â Â Â Â
> >
> > >
> Is there a reason for for all this extra stuff in messages?
> What is can’t ?
>
>
>
>
> ------------------------------------
>
> Yahoo! Groups Links
>
>
>
>

Thu Feb 28, 2013 11:50 am (PST) . Posted by:

"Chris Ball" peggysue281

The other day my computer crashed and I had to reboot my hard drive. Since
rebooting I have been receiving these errors when running the add in:

=RCHGetElementNumber(Ticker,25)

=RCHGetElementNumber(Ticker,48)

=RCHGetElementNumber(Ticker,84)

=RCHGetElementNumber(Ticker,AN12)

I know the issue is I need to download new element definitions but it has
been so long since I had to do it I am not sure what element definitions I
need to download. Any help is appreciated.

Thu Feb 28, 2013 1:19 pm (PST) . Posted by:

"Randy Harmelink" rharmelink

I would suggest just using the latest official release. Exit EXCEL,
download it from the group, and then extract all of the files into your SMF
add-in folder, replacing files as needed. Then restart EXCEL and you should
have that version of the add-in, and the latest element definitions.

You can check which version the add-in is with:

=RCHGetElementNumber("Version&quot;)

On Thu, Feb 28, 2013 at 12:50 PM, Chris Ball crball02@gmail.com> wrote:

>
> The other day my computer crashed and I had to reboot my hard drive. Since
> rebooting I have been receiving these errors when running the add in:
>
> =RCHGetElementNumber(Ticker,25)
>
> =RCHGetElementNumber(Ticker,48)
>
> =RCHGetElementNumber(Ticker,84)
>
> =RCHGetElementNumber(Ticker,AN12)
>
> I know the issue is I need to download new element definitions but it has
> been so long since I had to do it I am not sure what element definitions I
> need to download. Any help is appreciated.
>
>
>
READ MORE....