Rabu, 18 Mei 2016

[smf_addin] Digest Number 3701[3 Attachments]

15 Messages

Digest #3701
1a
Re: Investing.com by bushpilote
2a
Reinstall by nicolaslee1992
2b
Re: Reinstall by "Randy Harmelink" rharmelink
3b
Re: GuruFocus financial grab VBA code issue by "Randy Harmelink" rharmelink

Messages

Tue May 17, 2016 11:51 am (PDT) . Posted by:

nicolaslee1992

Randy,

For some reason every time I exit Excel 2010 I have to readd the add in next time I log on vs Excel 2013 the add-in stays. Is there a simple fix to this?

Any help is appreciated.

Best,

-Nick

Tue May 17, 2016 1:02 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

The only thing I can think of is some outside product doing that. For
example, some corporations have software in place that make sure
unauthorized add-ins can't be (permanently) installed. Or maybe you have
some firewall in place that has a setting that does something similar?

On Tue, May 17, 2016 at 11:51 AM, nicolaslee1992@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> For some reason every time I exit Excel 2010 I have to readd the add in
> next time I log on vs Excel 2013 the add-in stays. Is there a simple fix to
> this?
>
> Any help is appreciated.
>
>

Tue May 17, 2016 3:43 pm (PDT) . Posted by:

vkleniko

Randy - I have been using your VBA code successfully for a long time, but since a recent upgrade to Excel 2016/Win 10 I have now been receiving this error <meta http-equiv="refresh" content="0; URL=http://www.gurufocus.com/membership/upgrade.php?redir=/download_dataset.php?symbol="any symbol"">

I am a premium subscriber to Guru and have my login credentials saved under both Firefox and Edge. But the error continues. Is this Excel 2016 problem? Are you aware of any workaround?

Thanks
Vlad

Tue May 17, 2016 3:47 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Logging in with Edge or FireFox won't do anything for EXCEL or the add-in.
You need to log in with either IE or use the EXCEL Web Query dialog. The
add-in uses Windows routines that are common to IE and EXCEL when accessing
the Internet, so it needs an IE security cookie.

On Tue, May 17, 2016 at 2:45 PM, vkleniko@yahoo.com [smf_addin] <
smf_addin@yahoogroups.com> wrote:

>
> Randy - I have been using your VBA code successfully for a long time, but
> since a recent upgrade to Excel 2016/Win 10 I have now been receiving this
> error <meta http-equiv="refresh" content="0; URL=
> http://www.gurufocus.com/membership/upgrade.php?redir=/download_dataset.php?symbol="any
> symbol"">
>
> I am a premium subscriber to Guru and have my login credentials saved
> under both Firefox and Edge. But the error continues. Is this Excel 2016
> problem? Are you aware of any workaround?
>
>

Tue May 17, 2016 7:18 pm (PDT) . Posted by:

perseuslim11

As shown in photo above, the rchgettablecell stopped working after a number of cells, I've tried finding out the cause but to no avail as well

most formulas above uses RCHgettablecell on wsj.com address, for example:
http://quotes.wsj.com/MY/BJCORP/financials/annual/income-statement http://quotes.wsj.com/MY/BJCORP/financials/annual/income-statement http://quotes.wsj.com/MY/BJCORP/financials/annual/balance-sheet http://quotes.wsj.com/MY/BJCORP/financials/annual/balance-sheet http://quotes.wsj.com/MY/BJCORP/financials/annual/cash-flow http://quotes.wsj.com/MY/BJCORP/financials/annual/cash-flow


any way to fix this problem? Thanks =)





Attachment(s) from
1 of 1 Photo(s)

Tue May 17, 2016 10:07 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

It looks like you may have hit the 1000 web page limit.

The add-in is intended for ad hoc data retrieval and does have a built-in
limitation of 1000 web pages retrievals per "session". That's because the
add-in saves a copy of the web page into an array with 1000 slots, so that
if it needs to retrieve multiple data items from the same web page, it only
needs to retrieve the web page once. But the extractions are done from the
saved copy of the web page. So any further page retrievals have no slot
available and return "Error".

It looks like you are building a database of some kind. That's not what the
add-in is designed for and is not a burden I want to place on free data
sources. They have ways they could easily prevent something like the add-in
from collecting data. Can't you do more screening ahead of time to narrow
down the number of companies to retrieve data on?

An alternative would be to use the smfUpdateDownloadTable process. That
saves values into a table instead of having recalculating formulas. And you
can control how many rows of data to get in a "session", just by
highlighting those rows. It can be handy if getting financial statements
data, because you typically would only need to grab the data for any given
company once per quarter. Instead of reloading it every time you open the
spreadsheet because of recalculating formulas.

On Tue, May 17, 2016 at 7:18 PM, perseuslim11@
​...​
wrote:

>
> As shown in photo above, the rchgettablecell stopped working after a
> number of cells, I've tried finding out the cause but to no avail as well
>
> most formulas above uses RCHgettablecell on wsj.com address, for example:
> http://quotes.wsj.com/MY/BJCORP/financials/annual/income-statement
>
> http://quotes.wsj.com/MY/BJCORP/financials/annual/balance-sheet
> http://quotes.wsj.com/MY/BJCORP/financials/annual/cash-flow
>
> any way to fix this problem? Thanks =)
>
>

Tue May 17, 2016 10:55 pm (PDT) . Posted by:

perseuslim11

Nope, It's just the 95th stock before it stopped working...

What I intended to build was a ranking system for all stocks, like the Action score in Old School Value, but for my own country's market instead..

Tue May 17, 2016 11:05 pm (PDT) . Posted by:

perseuslim11

as shown in photo above, are the ratios/items needed for calculating the "Action score" http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/ http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/

as for smfUpdateDownloadTable , is it possible to get all those items that i need to build the action score ranking? If so, how is it used?


I don't quite understand this documentation: https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/3026 https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/3026



"I use the smfUpdateDownloadTable macro for such situations. This would actually place values into the table, so no recalculation occurs. When I want "fresh" values, I just rerun the macro. Here is the documentation for the table setup from the macro's module:
The upper left hand corner cell of the table needs to have a range name of "Ticker" The cells below the "Ticker" cell should be filled in with ticker symbols, one per cell The cells to the right of the "Ticker" cell should be filled with column titles The cells above the column titles need to be filled in with SMF add-in formulas or element numbers. Use five tildas as a substitute for a ticker symbol. For example, any of the following text strings could be used to get "Market Capitalization" from Yahoo:

941
RCHGetElementNumber("~~~~~", 941)
RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~ http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market Cap")A recent addition to the process is that if the cell entry above a column title is an "x", that column is skipped from any update -- I use this for any calculation columns I want in the middle of my table. In all of my workbooks, I assign the smfUpdateDownloadTable macro to a keyboard shortcut of Ctrl-Shift-J. "











Attachment(s) from
1 of 1 Photo(s)

Tue May 17, 2016 11:36 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

There are four links on the "FAQs and Tips" web page about the
smfUpdateDownloadTable process. One is a blog entry building one from
scratch:

http://seekingalpha.com/instablog/326478-derek-a-barrett/1836421-automating-alpha-optimize-large-data-sets-with-the-smfupdatedownloadtable-macro

...although it refers to some now obsolete fields.

I usually avoid all the setup work by just starting with an already built
sample, and modify it as needed. Some of mine get pretty complex, because
of referbacks and such. I've attached a copy of the basic sample. You
should just be able to run the smfUpdateDownloadTable macro from your
context menu, or via the macro dialog page. I usually run it from a button
I created on my toolbar.

I don't know if all the items you need are available, since you are talking
about foreign stocks. I don't work with them at all.

On Tue, May 17, 2016 at 11:04 PM, perseuslim11@
​...wrote:

>
>
​as shown in photo above, are the ratios/items needed for calculating the
> "Action score"
> http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/
>

> as for smfUpdateDownloadTable , is it possible to get all those items
> that i need to build the action score ranking? If so, how is it used?
>
> I don't quite understand this documentation:
> https://groups.yahoo.com/neo/groups/smf_addin/conversations/topics/3026
>
> "I use the smfUpdateDownloadTable macro for such situations. This would
> actually place values into the table, so no recalculation occurs. When I
> want "fresh" values, I just rerun the macro. Here is the documentation for
> the table setup from the macro's module:
>
> 1. The upper left hand corner cell of the table needs to have a range
> name of "Ticker"
> 2. The cells below the "Ticker" cell should be filled in with ticker
> symbols, one per cell
> 3. The cells to the right of the "Ticker" cell should be filled with
> column titles
> 4. The cells above the column titles need to be filled in with SMF
> add-in formulas or element numbers. Use five tildas as a substitute for a
> ticker symbol. For example, any of the following text strings could be
> used to get "Market Capitalization" from Yahoo:
>
> 941
> RCHGetElementNumber("~~~~~", 941)
> RCHGetTableCell("http://finance.yahoo.com/q/ks?s=~~~~~",1,">Market
> Cap")
>
> A recent addition to the process is that if the cell entry above a column
> title is an "x", that column is skipped from any update -- I use this for
> any calculation columns I want in the middle of my table. In all of my
> workbooks, I assign the smfUpdateDownloadTable macro to a keyboard shortcut
> of Ctrl-Shift-J. "
>
>
>
>
Attachment(s) from Randy Harmelink
1 of 1 File(s)

Tue May 17, 2016 11:45 pm (PDT) . Posted by:

"Randy Harmelink" rharmelink

Even if you can get all the necessary data items, how would you calculate
the various scores to rank the stocks? I don't see the details of those
calculations in the blog entry.

On Tue, May 17, 2016 at 11:04 PM, perseuslim11@
​...wrote:

>
> as shown in photo above, are the ratios/items needed for calculating the
> "Action score"
>
> http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/
>

Wed May 18, 2016 12:17 am (PDT) . Posted by:

"joo zhi" perseuslim11

I will rank it roughly based on what I think the author would be doing the ranking,  as there are 3 other articles where he briefly introduced what he emphasise on in each score (mainly quality,  value and growth scores) 
http://www.oldschoolvalue.com/blog/valuation-methods/osv-growth-rating-system/
http://www.oldschoolvalue.com/blog/valuation-methods/achieving-16-7-returns-with-the-value-score/

http://www.oldschoolvalue.com/blog/valuation-methods/quality-stocks-rating/

Sent from Yahoo Mail on Android

On Wed, 18 May, 2016 at 14:45, Randy Harmelink rharmelink@gmail.com [smf_addin]<smf_addin@yahoogroups.com> wrote:  

Even if you can get all the necessary data items, how would you calculate the various scores to rank the stocks? I don't see the details of those calculations in the blog entry.

On Tue, May 17, 2016 at 11:04 PM, perseuslim11@​...wrote:

as shown in photo above, are the ratios/items needed for calculating the "Action score"http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/

Wed May 18, 2016 1:12 am (PDT) . Posted by:

"joo zhi" perseuslim11

The data I would need are as below:Price, free cash flow, Operating cash flow, revenue (TTM and yearly), total shareholders equity (aka book value), 

Operating profit, gross profit, total assets, long term and short term debt, current assets and current liabilities, no. of shares, capital leases, market cap, cash and equivalents, minority interest (this one is optional, can be omitted if no such data)
Of which, two years of data (latest year and the year before it) is needed for net income, Operating cash flow, and number of shares only  
Are these data available by using the smfupdate function?

Sent from Yahoo Mail on Android

On Wed, 18 May, 2016 at 15:17, joo zhi perseuslim11@yahoo.com [smf_addin]<smf_addin@yahoogroups.com> wrote:  
I will rank it roughly based on what I think the author would be doing the ranking,  as there are 3 other articles where he briefly introduced what he emphasise on in each score (mainly quality,  value and growth scores) 

http://www.oldschoolvalue.com/blog/valuation-methods/osv-growth-rating-system/
http://www.oldschoolvalue.com/blog/valuation-methods/achieving-16-7-returns-with-the-value-score/

http://www.oldschoolvalue.com/blog/valuation-methods/quality-stocks-rating/

Sent from Yahoo Mail on Android

 

Even if you can get all the necessary data items, how would you calculate the various scores to rank the stocks? I don't see the details of those calculations in the blog entry.

On Tue, May 17, 2016 at 11:04 PM, perseuslim11@​...wrote:

as shown in photo above, are the ratios/items needed for calculating the "Action score"http://www.oldschoolvalue.com/blog/valuation-methods/action-score-quality-value-growth/

Wed May 18, 2016 1:23 am (PDT) . Posted by:

"Randy Harmelink" rharmelink

If you have add-in functions getting those data items, they should be able
to be used with smfUpdateDownloadTable.

As to whether those data items are available for the stocks you want them
for, I have no idea.

On Wed, May 18, 2016 at 1:12 AM, joo zhi perseuslim11@
​...wrote:

>
> The data I would need are as below:
> Price, free cash flow, Operating cash flow, revenue (TTM and yearly),
> total shareholders equity (aka book value),
>
> Operating profit, gross profit, total assets, long term and short term
> debt, current assets and current liabilities, no. of shares, capital
> leases, market cap, cash and equivalents, minority interest (this one is
> optional, can be omitted if no such data)
>
> Of which, two years of data (latest year and the year before it) is needed
> for net income, Operating cash flow, and number of shares only
>
> Are these data available by using the smfupdate function?
>
>
>

Wed May 18, 2016 1:47 am (PDT) . Posted by:

"joo zhi" perseuslim11

I see, yes there are add ins available for these dataThanks for your clarification ^^
Sent from Yahoo Mail on Android

On Wed, 18 May, 2016 at 16:23, Randy Harmelink rharmelink@gmail.com [smf_addin]<smf_addin@yahoogroups.com> wrote:  

If you have add-in functions getting those data items, they should be able to be used with smfUpdateDownloadTable.

As to whether those data items are available for the stocks you want them for, I have no idea.

On Wed, May 18, 2016 at 1:12 AM, joo zhi perseuslim11@​...wrote:

The data I would need are as below:Price, free cash flow, Operating cash flow, revenue (TTM and yearly), total shareholders equity (aka book value), 

Operating profit, gross profit, total assets, long term and short term debt, current assets and current liabilities, no. of shares, capital leases, market cap, cash and equivalents, minority interest (this one is optional, can be omitted if no such data)
Of which, two years of data (latest year and the year before it) is needed for net income, Operating cash flow, and number of shares only  
Are these data available by using the smfupdate function?

For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF

Tidak ada komentar:

Poskan Komentar