Rabu, 20 November 2013

[smf_addin] Digest Number 2865

EXCEL Stock Market Functions Add-in

15 New Messages

Digest #2865
1a
url by wongkayau
1b
Re: url by "Randy Harmelink" rharmelink
1c
Re: url by wongkayau
3a
Close price on  a particular date !!! by af3512b8ef4166b5c33401271c05da3c
3b
Re: Close price on a particular date !!! by "Randy Harmelink" rharmelink
3c
Re: Close price on a particular date !!! by af3512b8ef4166b5c33401271c05da3c
8
Error / #Value! Problem by "Joe Casino" joe.casino

Messages

Wed Nov 20, 2013 1:11 am (PST) . Posted by:

wongkayau

Hi Randy,

Morning. Like smf so much.

I want to download the 2012 annual result anouncement date for all stocks from this website
http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx

I can enter stock code and click radian botton "Headline Category" -> "annoucement and notices" -> "financial information" -> "final result", and then "search". I get the result, but the shown url still the same
http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx

The same url forbids me from changing url by stock code so as to download the data in excel. How may I dig out the true url for smf?

Thank you so much!!!

Cheers,
Laurent


Wed Nov 20, 2013 1:24 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Can't help you with that one. I've not had much luck with determining a
bookmarkable URL with ASP web pages.

On Wed, Nov 20, 2013 at 2:11 AM, <wongkayau@yahoo.com> wrote:

> I want to download the 2012 annual result anouncement date for all stocks
> from this website
>
>
> http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx
>
> I can enter stock code and click radian botton "Headline Category" ->
> "annoucement and notices" -> "financial information" -> "final result", and
> then "search". I get the result, but the shown url still the same
>
>
> http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx
>
> The same url forbids me from changing url by stock code so as to download
> the data in excel. How may I dig out the true url for smf?
>

Wed Nov 20, 2013 7:52 am (PST) . Posted by:

wongkayau

no problem. thanks!
cheers, laurent


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

Can't help you with that one. I've not had much luck with determining a bookmarkable URL with ASP web pages.

On Wed, Nov 20, 2013 at 2:11 AM, <wongkayau@... mailto:wongkayau@...> wrote:
I want to download the 2012 annual result anouncement date for all stocks from this website
http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx
I can enter stock code and click radian botton "Headline Category" -> "annoucement and notices" -> "financial information" -> "final result", and then "search". I get the result, but the shown url still the same
http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx http://www.hkexnews.hk/listedco/listconews/advancedsearch/search_active_main.aspx
The same url forbids me from changing url by stock code so as to download the data in excel. How may I dig out the true url for smf?








Wed Nov 20, 2013 1:20 am (PST) . Posted by:

vasim_07

Thanks....it actually works...


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

You won't be able to use the RCHGetHTMLTable() function there. They don't have the table properly coded in HTML. They have spacer rows with no ending tag for the row. It looks like RCHGetTableCell() would work, to get the individual cells. For example:

=RCHGetTableCell("http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02 http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02",1,">Sales Turnover")


There is an additional parameter for RCHGetHTMLTable() that I had played around with at one point, to extract *rows* of a table instead of whole tables. Then, the "pDir1" and "pDir2" parameters skip backwards and forwards by rows instead of tables. If you use that parameter, you can extract sections of the table by avoiding the improperly coded rows.

For example, you could array-enter this over a 6-column by 15-row range:

=RCHGetHTMLTable("http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02 http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02",">Sales Turnover",-1,"",15,1)


...and this over a 6-column by 23-row range:

=RCHGetHTMLTable("http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02 http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02",">Operating Profit",-1,"",23,1)


On Tue, Nov 19, 2013 at 7:12 AM, <vasim_07@... mailto:vasim_07@...> wrote:

I wanted to get the profit and lose table (whole table) from the following link..


http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02 http://www.moneycontrol.com/financials/libertyshoes/consolidated-profit-loss/LS02



I tried,


=RCHGetHTMLTable(A1,A2,-1,"",1)

A1 = Link & A2 = Sales Turnover.



which results display on first row (not the entire table)
| Mar '12 | Mar '11 | Mar '10 |....


Any help to get the entire table please.









Wed Nov 20, 2013 7:32 am (PST) . Posted by:

"Randy Harmelink" rharmelink

Three possibilities:

=smfPricesByDates("SPY","11/15/13")
=RCHGetYahooHistory("SPY",2013,11,15,2013,11,15,,"A",0)
=RCHGetYahooHistory("SPY",2013,11,15,2013,11,15,,"C",0)

The first two return adjusted closing prices.

On Wed, Nov 20, 2013 at 7:12 AM, <johkouf@hotmail.com> wrote:

>
> Can I do that ?? how ?
>

Wed Nov 20, 2013 10:19 am (PST) . Posted by:

af3512b8ef4166b5c33401271c05da3c

Thank you !!


Do you also know whicj element returns the Country ?









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

Three possibilities:

=smfPricesByDates("SPY","11/15/13")
=RCHGetYahooHistory("SPY",2013,11,15,2013,11,15,,"A",0)
=RCHGetYahooHistory("SPY",2013,11,15,2013,11,15,,"C",0)


The first two return adjusted closing prices.


On Wed, Nov 20, 2013 at 7:12 AM, <johkouf@... mailto:johkouf@...> wrote:

Can I do that ?? how ?







Wed Nov 20, 2013 8:01 am (PST) . Posted by:

nuno.nogueira

Hello,


I'm using =RCHGetYahooQuotes("IBM","j1") to get the market cap of IBM.
Now I need to get other data, like:
ROE ROA Revenue Total Debt etc I'm using the lite version of RHCGetYahooQuotes, so no add-in. Where can I find the complete list of symbols to get these data points?


Thanks for helping!

Wed Nov 20, 2013 8:18 am (PST) . Posted by:

"Kermit W. Prather" kermitpra

Sounds like a question for yahoo.

As Randy has stated many times in the past, the SMF ADDIN is not meant to pull "a complete list of stock"

The last thing we users need is for sites to stat blocking data extraction.

Please do not abuse the tool?

Kermit

From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of nuno.nogueira35@gmail.com
Sent: Wednesday, November 20, 2013 11:01 AM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] How to get ROE and other fundamental data from Yahoo Finance statistics page


Hello,

I'm using =RCHGetYahooQuotes("IBM","j1") to get the market cap of IBM.
Now I need to get other data, like:
* ROE
* ROA
* Revenue
* Total Debt
* etc
I'm using the lite version of RHCGetYahooQuotes, so no add-in. Where can I find the complete list of symbols to get these data points?

Thanks for helping!

Wed Nov 20, 2013 9:00 am (PST) . Posted by:

dcharlotte99

Thanks for this summary!



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

Since I am only marginally computer literate, I had a challenge implementing one of the Stock Market Functions. Once I got it working (with Randy's help), I decided to document the steps I went through in case it might be of some value to someone else. Here is the step-by-step procedure I used.
Go to C:\Program Files
Right click on a blank spot on the screen
Left click on "New" "Folder"
Create a new folder called "SMF Add In"
Use your browser to go to www.Yahoo.com
Assuming you have added "smf_addin group" to your list of groups … select that group.
Left click on "Files"
Read all the documentation associated with the Stock Market Functions!
Left click on "Add-In Files"
Left click on "RCH_Stock_Market_Functions_2.1.2012.12.29.zip"
The file will download. It is still zipped and in the "Downloads&quot; directory
Right click on it and see the options
Left click on "Extract All"
Enter the destination file as the "C:\Program Files\SMF Add In" previously created
The computer will extract the zipped file from the "Downloads&quot; directory to the destination file.
Start Excel
Open a new spreadsheet
Left click on "File", "Options", "Add Ins"
Where it says "Manage Add ins" left click on "Go"
Left click on "Browse" and go to C:\Program Files\SMF Add In
Select the "RCH_Stock_Market_Functions" file and left click "OK"
Left click "OK" on the Add Ins screen
At this point the add in should be ready to use.
To test the add in:
Enter "l1" in B1 (request for current stock price)
Enter "T" in A2 (stock market symbol for AT&T)
Enter "=RCHGetYahooQuotes(a2,b1)" in B2
Key in "Control + Shift + Enter"
The current price of AT&T stock should appear in B2
If it does not, a possible cause is that macros are not enabled for the spreadsheet.
In Excel 2013, if the spreadsheet involves macros and the macros are not enabled, a yellow alert bar will appear and offer the option to enable the macros. It will be necessary to click on "Enable".

Wed Nov 20, 2013 10:58 am (PST) . Posted by:

dcharlotte99

A big thank you to dancarroll/hamisthedenizen and of course Randy for the GuruFocus 10-year model. I've QC'ed the version on the 'Uploads&#39; section and have the following relatively minor comments:

Income Statement: Added a row to collect 'EPS (Basic)'
Income Statement: Corrected first few columns of row 8 to have it pull from website
Cashflow Statement: for Net Income changed columns 2 and 3 to pull from other ss cell
Cashflow Statement: Changed title in Column B of 'Deferred Taxes' to 'Sale of Property, Plant, Equipment' to match website and pull data

I QC'd much of the data also. Thanks again!

Wed Nov 20, 2013 11:15 am (PST) . Posted by:

joe.casino

Thanks for the quick response last time. Continuously impressed by the quality of this group!


I was able to fix the errors by downloading the most recent files from the work in progress folder. However, now I am having a problem with the google financial statement data. Because I saved the my plug in on a network folder, I have to manually edit the source every time I open the file. However for some reason, with the google finanical statement files, I am being blocked from changing the source. Any suggestions?


Thanks

Wed Nov 20, 2013 11:18 am (PST) . Posted by:

joe.casino

Nevermind able to figure it out myself!!! All I had to do was unprotect the workbook so I could edit it. Oops


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

Thanks for the quick response last time. Continuously impressed by the quality of this group!

I was able to fix the errors by downloading the most recent files from the work in progress folder. However, now I am having a problem with the google financial statement data. Because I saved the my plug in on a network folder, I have to manually edit the source every time I open the file. However for some reason, with the google finanical statement files, I am being blocked from changing the source. Any suggestions?


Thanks


Wed Nov 20, 2013 11:28 am (PST) . Posted by:

joe.casino

However, I do notice that the data only goes four years back, and then I get an error message. Is that as far back as Google will go, or is that something you are still working on in the element definitions?


Thanks


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

Nevermind able to figure it out myself!!! All I had to do was unprotect the workbook so I could edit it. Oops


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

Thanks for the quick response last time. Continuously impressed by the quality of this group!

I was able to fix the errors by downloading the most recent files from the work in progress folder. However, now I am having a problem with the google financial statement data. Because I saved the my plug in on a network folder, I have to manually edit the source every time I open the file. However for some reason, with the google finanical statement files, I am being blocked from changing the source. Any suggestions?


Thanks




Wed Nov 20, 2013 3:42 pm (PST) . Posted by:

"Joe Casino" joe.casino

Hi,

Just found this add-in today and it is absolutely incredible.  Thank you for allowing me to take advantage of this remarkable resource.  I've been working with it all day and I've been having a few problems that I cant solve.  

I downloaded the add-in and placed them in a network folder (I attend a university with shared computers so I couldn't save it directly on the computer).  I was able to manually place the add-in into excel as well as change the data link source on all of the templates I have tried to open.  However on most, after data is done refreshing I get an "error" reading in all/most cells (ex. AdvFN - Financial Statements).   Interestingly the Reuters Ratio file gave me some correct values, however I still got some "errors" and some "#value!" in the S&P column. Lastly, Yahoo financials was free of errors. 

At this point I am just really confused and would appreciate any help/advice.  I've tried changing the destination folder around / redownloaded it / restarted excel and nothing works.  I even tried the version formula and got this as an answer: 

Stock
Market Functions add-in, Version 2.1.2012.12.29
(N:\RCH_Stock_Market_Functions-2.1.2012.12.29; 1)

Thank you all again for the incredible program and hopefully I'll be able to get this whole thing sorted out soon!

-Joe
Diposting oleh EXCEL FEED LEARNING di 15.42

Tidak ada komentar:

Posting Komentar

Langganan: Posting Komentar (Atom)