Kamis, 04 Agustus 2011

[smf_addin] Digest Number 1908

Messages In This Digest (19 Messages)

Messages

1a.

Re: Downloading financial statements from AdvFn

Posted by: "bushpilote" bushpilote@yahoo.ca   bushpilote

Wed Aug 3, 2011 6:41 am (PDT)



Thanks to both of you for the feedback.

--- In smf_addin@yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
>
> I have an external query macro that uses
> With ss.QueryTables(1)
> .Connection = _
> "URL;http://www.advfn.com/p.php?pid=financials&btn=s_ok&mode=&symbol=" & symbol & "&s_ok=OK"
> Yours also worked
> "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> to get this
> INDICATORS
> quarter end date 1994/03 1994/06 1994/09 1994/12 1995/03
> date preliminary data loaded 0000-00-00 0000-00-00 0000-00-00 0000-00-00 4/14/1995
> earnings period indicator N F Q S N
> quarterly indicator 3 4 1 2 3
> basic earnings indicator N N N N N
> template indicator
> preliminary full context ind N N N N N
> projected fiscal year date 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00
> number of months last report period 3 3 3 3 3
> INCOME STATEMENT
> operating revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> total revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
>
> From: bushpilote
> Sent: Tuesday, August 02, 2011 2:50 PM
> To: smf_addin@yahoogroups.com
> Subject: [smf_addin] Downloading financial statements from AdvFn
>
>
> Hi Randy,
> I'm trying to download financial statements from AdvFn.com through an excel macro (I am familiar with the smfGetAdvFNElement-Template-All-Line-Items.xls template).
> Can you or anyone else on this board help me understand why the following critical lines of coding are not working:
>
> "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> , Destination:=Range("$B$1"))
> .Name = _
> "p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70"
>
> Thanks.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
1b.

Re: Downloading financial statements from AdvFn

Posted by: "dguillett1" dguillett1@gmail.com   donaldb36

Wed Aug 3, 2011 6:50 am (PDT)



So how is it working for you now?

From: bushpilote
Sent: Wednesday, August 03, 2011 8:41 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] Re: Downloading financial statements from AdvFn

Thanks to both of you for the feedback.

--- In mailto:smf_addin%40yahoogroups.com, "dguillett1" <dguillett1@...> wrote:
>
>
> I have an external query macro that uses
> With ss.QueryTables(1)
> .Connection = _
> "URL;http://www.advfn.com/p.php?pid=financials&btn=s_ok&mode=&symbol=" & symbol & "&s_ok=OK"
> Yours also worked
> "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> to get this
> INDICATORS
> quarter end date 1994/03 1994/06 1994/09 1994/12 1995/03
> date preliminary data loaded 0000-00-00 0000-00-00 0000-00-00 0000-00-00 4/14/1995
> earnings period indicator N F Q S N
> quarterly indicator 3 4 1 2 3
> basic earnings indicator N N N N N
> template indicator
> preliminary full context ind N N N N N
> projected fiscal year date 0000-00-00 0000-00-00 0000-00-00 0000-00-00 0000-00-00
> number of months last report period 3 3 3 3 3
> INCOME STATEMENT
> operating revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
> total revenue 1,244.00 1,293.00 1,247.00 1,482.00 1,587.00
>
> From: bushpilote
> Sent: Tuesday, August 02, 2011 2:50 PM
> To: mailto:smf_addin%40yahoogroups.com
> Subject: [smf_addin] Downloading financial statements from AdvFn
>
>
> Hi Randy,
> I'm trying to download financial statements from AdvFn.com through an excel macro (I am familiar with the smfGetAdvFNElement-Template-All-Line-Items.xls template).
> Can you or anyone else on this board help me understand why the following critical lines of coding are not working:
>
> "URL;http://www.advfn.com/p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70" _
> , Destination:=Range("$B$1"))
> .Name = _
> "p.php?pid=financials&btn=istart_date&mode=quarterly_reports&symbol=MSFT&istart_date=70"
>
> Thanks.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
2a.

Importing an Image into Excel

Posted by: "GaryFera" gfera@shaw.ca   GaryFera

Wed Aug 3, 2011 7:47 am (PDT)



Randy, given a web address for a chart like the one below, is there a way to import that chart into Excel with SMF?

Gary

http://chart.finance.yahoo.com/t?s=AAPL&lang=en-US&region=US&width=300&height=180

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2b.

Re: Importing an Image into Excel

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Aug 3, 2011 10:06 am (PDT)



Just use the RCHCreateComment() function:

=RCHCreateComment("
http://chart.finance.yahoo.com/t?s=AAPL&lang=en-US&region=US&width=300&height=180
",99)

On Wed, Aug 3, 2011 at 7:47 AM, GaryFera <gfera@shaw.ca> wrote:

> Randy, given a web address for a chart like the one below, is there a way
> to import that chart into Excel with SMF?
>
> Gary
>
>
> http://chart.finance.yahoo.com/t?s=AAPL&lang=en-US&region=US&width=300&height=180
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
2c.

Re: Importing an Image into Excel

Posted by: "GaryFera" gfera@shaw.ca   GaryFera

Wed Aug 3, 2011 12:38 pm (PDT)



Wow, so easy. Thanks much.

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> Just use the RCHCreateComment() function:
>
> =RCHCreateComment("
> http://chart.finance.yahoo.com/t?s=AAPL&lang=en-US&region=US&width=300&height=180
> ",99)
>
> On Wed, Aug 3, 2011 at 7:47 AM, GaryFera <gfera@...> wrote:
>
> > Randy, given a web address for a chart like the one below, is there a way
> > to import that chart into Excel with SMF?
> >
> > Gary
> >
> >
> > http://chart.finance.yahoo.com/t?s=AAPL&lang=en-US&region=US&width=300&height=180
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
3a.

Re: Inaccurate yahoo finance dividend data?

Posted by: "Carl Wells" cgrwells@yahoo.co.uk   cgrwells

Wed Aug 3, 2011 8:17 am (PDT)



ps thanks for that quotes.pm suggestion ... I had come across it before some months ago but it left my stream of conciousness ... I think it could save me a bit of time buy just getting that split and divi data ... no need for me to write perl code to do it! :)

Cheers,

Carl

________________________________
From: Gary Funck <gary@intrepid.com>
To: smf_addin@yahoogroups.com
Sent: Monday, 1 August 2011, 1:15
Subject: Re: [smf_addin] Inaccurate yahoo finance dividend data?

 
On 07/31/11 16:16:25, cgrwells wrote:
> My problem is that for e.g. BATS.L (British american tobacco,
> a major UK-listed international tobacco company), the dividend
> data appears to be incorrect; just eyeballing, its far too
> large! e.g. 60% of the share price in March 2010! If you look at
> http://uk.finance.yahoo.com/q/hps=BATS.L&b=1&a=00&c=2003&e=31&d=06&f=2011&g=v,
> you can see that the dividends in the yahoo system are enormous
> and incorrect (if you compare with the company's website
> you can see the divis they actually paid).

They do look out-of-whack. If you try the ADR, the numbers
seem to be more in line, with an annual div. rate of 3.8%/so.
For the heck of it, you might ask BT's investor department
if they can explain the anomaly.

> This means that the yahoo adjusted price is way out, and if they
> got it wrong for a major FTSE-100 company like BATS then it will
> surely be wrong for many smaller companies and in other markets;

It might be worth trying some of those other companies to see
if this issue is unique to BT.

One thought comes to mind, is there perhaps a "group dividend"
that includes all the separate tobacco companies held by the
holding company that holds BT (and it is this dividend that
is being incorrectly for BT)? (please note that I don't know
anything about the details of BT as a company, so this idea
could be completely off base).

> unfortunately, this means that the aggregate price performance of
> any selection of stocks will also be wrong, and therefore I can't
> backtest any strategies with yahoo data. Has anyone here got any
> views on this? if I don't use the adjusted price then the price
> won't be adjusted for stock-splits ... I could manually scrape out
> stock-splits from the yahoo page for each stock and do the price
> adjustments myself (assuming the stock-split history is accurate),
> and I could use the reuters P&L to calculate the dividends paid
> in any accounting period using yahoo's payment dates but that
> would be somewhat tedious!

Note that the Yahoo! adjusted price can be inaccurate over
long time periods because it is reported only to two decimal places.

IIRC, there is an "R" package that fetches stock prices and
captures both split and dividends separately and makes its
own adjustments. For example,
http://www.r-chart.com/2010/06/stock-analysis-using-r.html

If you're hacking in Perl, this CPAN module may help:
http://search.cpan.org/~msisk/Finance-QuoteHist-1.16/lib/Finance/QuoteHist.pm

- Gary

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
3b.

Re: Inaccurate yahoo finance dividend data?

Posted by: "Carl Wells" cgrwells@yahoo.co.uk   cgrwells

Wed Aug 3, 2011 8:18 am (PDT)



Thanks; I can become comfortable at programming in R, I gather its pretty simple (was thinking of exporting my outputs into R and generating regression charts etc. there ... my output is month-end algorithm values partly for this reason :).

Best,

Carl
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
4a.

Re: creating a yahoo ticker search

Posted by: "Carl Wells" cgrwells@yahoo.co.uk   cgrwells

Wed Aug 3, 2011 8:17 am (PDT)



Hi Bartjan,

I had to do exactly the same ... how did you go about it? I used stock exchange websites for my local exchange codes, then tried the exchange code on the reuters website, and if it threw up a company in a different jurisdiction (i.e. not e.g. '.L' on the end for London), then I tried again using the company name (with 'Group', 'Holding' etc. lopped off), and if it found a match I used the ric code with '.L' in it ... this works >95% of the time but of course the 5% are going to be extremely annoying to weed out manually over thousands of companies!  how did you go about building your ticker list?  I have ISINs, if there was a page that translated the ISINs into RIC codes for nothing then I would be sorted ...

Best,

Carl

________________________________
From: Bartjan Van Hulten <vanhulten@yahoo.com>
To: smf_addin@yahoogroups.com
Sent: Thursday, 28 July 2011, 12:58
Subject: [smf_addin] creating a yahoo ticker search

 
Hi Randy,
Using your add-in i have now successfully created a bloomberg ticker search which lists the tickers and the exchanges. I have found bloomberg data more consistent across various countries. Also, using the bloomberg tickers, i have been able to deduct the tickers to use with Barrons, which provides good international financials data.

For stock quotes, yahoo is the best source. For this i am now trying to create a yahoo ticker search - and with a table linking the main exchange codes, i will be able to automatically reconcile bloomberg with yahoo tickers. However, i have been having some trouble with getting the table out in excel. The link is

http://finance.search.yahoo.com/search?p=meda&ei=UTF-8&fr=fin-v1&section=quot&fltr=type:Stock when i look with the term "meda" but i can replace that for example with "pfe"

I have been using the smftagcontent and the rchgettablecell, as well as using smfstrextr and also excel functions substitute, right, mid, left etc. However, to no avail as i cant seem to get the table in excel the way it is presented in yahoo. Would you have any suggestions? I am interested in getting "name" "symbol" and "exchange" out from that page.

Many thanks!
Bartjan

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (5)
5a.

Re: forward EBITDA element?

Posted by: "Carl Wells" cgrwells@yahoo.co.uk   cgrwells

Wed Aug 3, 2011 8:18 am (PDT)



That would certainly be of interest to me too ... but suspect the information isn't freely available?  certainly not for mid-small cap companies and surely not internationally ... my experience of sell-side analysts is that data quality is mixed, especially as you move away from the mainstream.  You can hope for a good consensus eps estimated, followed by sales (but is that net sales, gross sales etc.? for some stocks analysts are uncertain which figure to upload!), and then as you move away from the 'headline' figures, you find there is less agreement as to what definitions to use, and less consistency in uploading of info ... SAB Miller in the UK was a classic case of analyst confusion (company declared sales and EBIT with and without associates consolidated ...).  I used to have to ring them all up, get their models, and manually key that data in and sometimes the market clearly didn't bother! grunt work ...
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (3)
6a.

Google Finance data retrieval not working as of 8/3/2011

Posted by: "adam_sommers" adam_sommers@yahoo.com   adam_sommers

Wed Aug 3, 2011 9:05 am (PDT)



I just opened my spreadsheet with the add-in linked data, and all of sudden a ton of blank internet explorer windows (I pull over 100 pieces of data on 100 companies) started opening up indicating a google finance URL, and the data does not come into the spreadsheet. Anyone else having this problem, or know how I can fix it?

I'm thinking I might go back to Yahoo data, but any suggestions would be appreciated.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6b.

Re: Google Finance data retrieval not working as of 8/3/2011

Posted by: "adam_sommers" adam@sommersfinancial.com   adam_sommers

Wed Aug 3, 2011 9:47 am (PDT)



I am using the RCHGetElement function in Excel 2010, with Windows 7. An example of a popped up window: about:blank#http://finance.google.com/finance?fstype=ii&q=MHP

Also, now my spreadsheet doesn't ever finish calculating, so I'm hung up. Even when I select "Do Not Update" upon opening the workbook, it still tries to get the data.

HELP!

Adam Sommers

--- In smf_addin@yahoogroups.com, "adam_sommers" <adam_sommers@...> wrote:
>
> I just opened my spreadsheet with the add-in linked data, and all of sudden a ton of blank internet explorer windows (I pull over 100 pieces of data on 100 companies) started opening up indicating a google finance URL, and the data does not come into the spreadsheet. Anyone else having this problem, or know how I can fix it?
>
> I'm thinking I might go back to Yahoo data, but any suggestions would be appreciated.
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6c.

Re: Google Finance data retrieval not working as of 8/3/2011

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Aug 3, 2011 10:12 am (PDT)



In theory, I wouldn't think that should be the add-in -- the add-in uses the
XMLHTTP protocol, which just retrieves the source code of the web page. No
scripts are executed. No images are retrieved. Etc.

On Wed, Aug 3, 2011 at 8:40 AM, adam_sommers <adam_sommers@yahoo.com> wrote:

> I just opened my spreadsheet with the add-in linked data, and all of sudden
> a ton of blank internet explorer windows (I pull over 100 pieces of data on
> 100 companies) started opening up indicating a google finance URL, and the
> data does not come into the spreadsheet. Anyone else having this problem,
> or know how I can fix it?
>
> I'm thinking I might go back to Yahoo data, but any suggestions would be
> appreciated.
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
6d.

Re: Google Finance data retrieval not working as of 8/3/2011

Posted by: "adam_sommers" adam@sommersfinancial.com   adam_sommers

Wed Aug 3, 2011 1:18 pm (PDT)



Randy,
I finally got it to go through the calcs, and had to remove all of my RCHGetElementNumber formulas linked to Google Finance elements. I am now using ADFN element numbers, which seems to work just fine. Not sure why Google Finance flipped out, but it was returning only errors in the cells requested.

Adam

--- In smf_addin@yahoogroups.com, Randy Harmelink <rharmelink@...> wrote:
>
> In theory, I wouldn't think that should be the add-in -- the add-in uses the
> XMLHTTP protocol, which just retrieves the source code of the web page. No
> scripts are executed. No images are retrieved. Etc.
>
> On Wed, Aug 3, 2011 at 8:40 AM, adam_sommers <adam_sommers@...> wrote:
>
> > I just opened my spreadsheet with the add-in linked data, and all of sudden
> > a ton of blank internet explorer windows (I pull over 100 pieces of data on
> > 100 companies) started opening up indicating a google finance URL, and the
> > data does not come into the spreadsheet. Anyone else having this problem,
> > or know how I can fix it?
> >
> > I'm thinking I might go back to Yahoo data, but any suggestions would be
> > appreciated.
> >
>

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (4)
7a.

downloading free bulk historical stock data

Posted by: "ciroverdi77" ciroverdi77@yahoo.com   ciroverdi77

Wed Aug 3, 2011 1:44 pm (PDT)



Just signed up and unclear about navigating this service. I simply want to download historical data in ASCII or text format. Help welcome. I can be contacted via this address or ciroverdi2@aol.com. Ciro

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
7b.

Re: downloading free bulk historical stock data

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Aug 3, 2011 1:46 pm (PDT)



Documentation is in the "Documentation" folder of the files area of the
group. You'd need the Overview/Installation document and then the document
on the RCHGetYahooHistory() function.

On Wed, Aug 3, 2011 at 12:59 PM, ciroverdi77 <ciroverdi77@yahoo.com> wrote:

> Just signed up and unclear about navigating this service. I simply want to
> download historical data in ASCII or text format. Help welcome. I can be
> contacted via this address or ciroverdi2@aol.com. Ciro
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
8a.

IF and AND formula

Posted by: "futants46and2" futants46and2@yahoo.com   futants46and2

Wed Aug 3, 2011 6:49 pm (PDT)



Hey Randy,

I know this is off topic, I have been trying to create an If and AND formula that has 3 arguments and a different outcome for each argument. I think my syntax is wrong, any help or guidance you can provide I will of course greatly appreciate. Below is the formula I constructed.

=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))

Thank You,
Elliot.

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
8b.

Re: IF and AND formula

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Aug 3, 2011 7:12 pm (PDT)



You need to figure out your parentheses properly. As is, this is the
statement:

=IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171)

...that matches up the last paren to the first paren. This is what I came up
with, but I have no idea if it is what you want:

=IF(AND(C171>D171,D171>0,(C171-D171)/D171),1,IF(AND(C1710,(D171-C171)/D171),2,IF(AND(C171>=0,D171<0,(H165-I165)/-I165),3,0)))

On Wed, Aug 3, 2011 at 6:49 PM, futants46and2 <futants46and2@yahoo.com>wrote:

>
> I know this is off topic, I have been trying to create an If and AND
> formula that has 3 arguments and a different outcome for each argument. I
> think my syntax is wrong, any help or guidance you can provide I will of
> course greatly appreciate. Below is the formula I constructed.
>
>
> =IF(AND(C171>D171,D171>0),((D171-C171)*(-1))/D171),1,if(and(C1710,((C171-D171)*(-1))/D171),2,if(and(c171>=0,d171<0,((H165)+(I165*-1))/(I165*-1),3,0,)))
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
9a.

Foreign Stocks

Posted by: "Sarvajit" sarvajits@yahoo.com   sarvajits

Wed Aug 3, 2011 9:54 pm (PDT)



I'm sure this has been answered before- could you point me in the right direction.....
How do I get detailed information about foreign stocks in foreign stock exchanges- eg. Quarterly sales for Continental AG (ETR:CON) or Toyoda-Gosei (TYO:7282).
Functions that work for US ticker symbols do not work for international ones.
Any help appreciated...
Thanks
Sarvajit

Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
9b.

Re: Foreign Stocks

Posted by: "Randy Harmelink" rharmelink@gmail.com   rharmelink

Wed Aug 3, 2011 9:58 pm (PDT)



I don't do anything with foreign stocks, so I can't really answer.
Hopefully, someone else will chime in.

However, you do need to know the ticker symbols appropriate for the data
source you are using, since each data source can use different ticker
symbols. The add-in can extract data from various data sources. That doesn't
mean you don't have to know anything about those data sources.

On Wed, Aug 3, 2011 at 9:39 PM, Sarvajit <sarvajits@yahoo.com> wrote:

> I'm sure this has been answered before- could you point me in the right
> direction.....
> How do I get detailed information about foreign stocks in foreign stock
> exchanges- eg. Quarterly sales for Continental AG (ETR:CON) or Toyoda-Gosei
> (TYO:7282).
> Functions that work for US ticker symbols do not work for international
> ones.
> Any help appreciated...
>
Back to top
Reply to sender | Reply to group | Reply via web post
Messages in this topic (2)
Recent Activity
Visit Your Group
Yahoo! Finance

It's Now Personal

Guides, news,

advice & more.

Yahoo! News

Odd News

You won't believe

it, but it's true

Need traffic?

Drive customers

With search ads

on Yahoo!

Need to Reply?

Click one of the "Reply" links to respond to a specific message in the Daily Digest.

Create New Topic | Visit Your Group on the Web
Diposting oleh EXCEL FEED LEARNING di 03.51

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)