4 Messages
Digest #2999
Messages
Sun Mar 9, 2014 5:40 pm (PDT) . Posted by:
salexander7781
Thank you for that explanation and tip of using smfGetTagContent. The example you provided worked fine, Randy. However, I just cannot get fields from the Balance Sheet and Cashflow Statement to work. I've spent 2 hours trying out various combinations. Could I please bother you again for some guidance ?
Here's what I was trying. Following the logic of your example statement, I now am grepping for div's
going backward 2 positions after finding the phrases "Balance Sheet", "Accounts Receivable" and "Latest Q."
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,">Balance Sheet",">Accounts Receivable","Latest Q.")
I am expecting (desperately) it behaves the way I think it should and picks up 899 (which is the Accounts Receivable of Oct2013. Here's how it looks in the source.
<td class='style4' title='923'><div class='bs9' title='923'>923</div></td>
<td class='style4' title='899'><div class='bs10' title='899'>899</div></td>
Instead, the thing is picking up, the value of 338. This is the Shares Outstanding from the Income Statement.
Same's the case with a figure from the Cashflow Statement (eg: Change In Working Capital)
Any inputs on what I'm doing wrong and how I should have thought to figure this out myself would be immensely appreciated, Randy.
Here's what I was trying. Following the logic of your example statement, I now am grepping for div's
going backward 2 positions after finding the phrases "Balance Sheet", "Accounts Receivable" and "Latest Q."
=smfGetTagContent("http://www.gurufocus.com/financials/A","
I am expecting (desperately) it behaves the way I think it should and picks up 899 (which is the Accounts Receivable of Oct2013. Here's how it looks in the source.
<td class='style4&#
<td class='style4&#
Instead, the thing is picking up, the value of 338. This is the Shares Outstanding from the Income Statement.
Same's the case with a figure from the Cashflow Statement (eg: Change In Working Capital)
Any inputs on what I'm doing wrong and how I should have thought to figure this out myself would be immensely appreciated, Randy.
Sun Mar 9, 2014 6:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
The problem is that your search strings aren't getting you to where you
think they are.
The first string, ">Balance Sheet" actually gets you to the link that is in
the list of links just above the chart.
Then, the second string, ">Account Receivable", actually gets you to a
sub-menu of the chart, under the "Select a metric" drop-down for the chart.
And then, "Latest Q" gets you to the date header line for the balance sheet
section. Not sure why you chose this string, since it isn't in the
"Accounts Receivable" line of data...?
And the "div" HTML tags prior to that are the "Shares Outstanding" line
just above the balance sheet section. Which is what you're picking up.
In any case, this is what I ended up with:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"Print
This Table",">Balance Sheet",">Accounts Receivable","ttm")
The "Print this table" string gets you to the top of all of the financial
information. And the "Latest Q" column of the balance sheet looks like it
has TTM labels, at least for accounts receivable?
Another option is to use their titles or glossary links to get directly to
the lines of data. For example:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"title='Accounts
Receivable'","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"/term/Accts
Rec./","ttm")
I'm not sure how reliable that would be? For the cash flow item, these
appear to work:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"Print
This Table",">Balance Sheet",">Change in Working Capital","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"title='Change
in Working Capital'","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A
","div",-2,"/term/ChangeinWorkingCapital/","ttm")
On Sun, Mar 9, 2014 at 5:40 PM, <salexand@gmail.com> wrote:
>
> I just cannot get fields from the Balance Sheet and Cashflow Statement to
> work. I've spent 2 hours trying out various combinations. Could I please
> bother you again for some guidance ?
>
> Here's what I was trying. Following the logic of your example statement, I
> now am grepping for div's
> going backward 2 positions after finding the phrases "Balance Sheet",
> "Accounts Receivable" and "Latest Q."
> =smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,">Balance
> Sheet",">Accounts Receivable","Latest Q.")
> I am expecting (desperately) it behaves the way I think it should and
> picks up 899 (which is the Accounts Receivable of Oct2013. Here's how it
> looks in the source.
> <td class='style4' title='923'><div class='bs9' title='923'>923</div></td>
> <td class='style4' title='899'><div class='bs10' title='899'>899</div></td>
>
> Instead, the thing is picking up, the value of 338. This is the Shares
> Outstanding from the Income Statement.
> Same's the case with a figure from the Cashflow Statement (eg: Change In
> Working Capital)
> Any inputs on what I'm doing wrong and how I should have thought to figure
> this out myself would be immensely appreciated, Randy.
>
think they are.
The first string, ">Balance Sheet" actually gets you to the link that is in
the list of links just above the chart.
Then, the second string, ">Account Receivable", actually gets you to a
sub-menu of the chart, under the "Select a metric" drop-down for the chart.
And then, "Latest Q" gets you to the date header line for the balance sheet
section. Not sure why you chose this string, since it isn't in the
"Accounts Receivable" line of data...?
And the "div" HTML tags prior to that are the "Shares Outstanding" line
just above the balance sheet section. Which is what you're picking up.
In any case, this is what I ended up with:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"Print
This Table",">Balance Sheet",">Accounts Receivable","ttm")
The "Print this table" string gets you to the top of all of the financial
information. And the "Latest Q" column of the balance sheet looks like it
has TTM labels, at least for accounts receivable?
Another option is to use their titles or glossary links to get directly to
the lines of data. For example:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"title='Accounts
Receivable'","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"/term/Accts
Rec./","ttm")
I'm not sure how reliable that would be? For the cash flow item, these
appear to work:
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"Print
This Table",">Balance Sheet",">Change in Working Capital","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A","div",-2,"title='Change
in Working Capital'","ttm")
=smfGetTagContent("http://www.gurufocus.com/financials/A
","div",-2,"/term/ChangeinWorkingCapital/","ttm")
On Sun, Mar 9, 2014 at 5:40 PM, <salexand@gmail.com> wrote:
>
> I just cannot get fields from the Balance Sheet and Cashflow Statement to
> work. I've spent 2 hours trying out various combinations. Could I please
> bother you again for some guidance ?
>
> Here's what I was trying. Following the logic of your example statement, I
> now am grepping for div's
> going backward 2 positions after finding the phrases "Balance Sheet",
> "Accounts Receivable" and "Latest Q."
> =smfGetTagContent("http://www.gurufocus.com/financials/A","
> Sheet",
> I am expecting (desperately) it behaves the way I think it should and
> picks up 899 (which is the Accounts Receivable of Oct2013. Here's how it
> looks in the source.
> <td class='style4&#
> <td class='style4&#
>
> Instead, the thing is picking up, the value of 338. This is the Shares
> Outstanding from the Income Statement.
> Same's the case with a figure from the Cashflow Statement (eg: Change In
> Working Capital)
> Any inputs on what I'm doing wrong and how I should have thought to figure
> this out myself would be immensely appreciated, Randy.
>
Sun Mar 9, 2014 11:43 pm (PDT) . Posted by:
"vijay gangrade" vijay_gangrade
Dear Sir,
Thank you so much for your help. It started working but it seems it is not giving results for all companies within NYSE. I made a test xls and tried to find only the available periods for some companies. But it works for some and gives "Error" for the rest. Also I noticed that it is not consistent despite of deleting history and cookies from Internet explorer. So for a company, it gives error on some occasions and correct results on other. Attached is the example xls I am using.
I was wondering if it is because of time-outs. I wouldn't be surprised if that is the case since my internet speed is a lot lesser than usual. Is there any way to override it.
I also wanted to ask if there is any way to stop the add-in from running as soon as the xls is opened. That way I can first prepare the xls completely and then run it like a macro.
Thank you once again. This has made my life a lot easier.
Regards,
Vijay Gangrade
On Friday, March 7, 2014 2:41 PM, Randy Harmelink <rharmelink@gmail.com> wrote:
The LINKS area of the Yahoo group has now been moved to the SMF website:
http://ogres-crypt.com/SMF/Tips-and-FAQs/
The two tips you should take a look at are:
Link AdvFN.001 - A possible fix for "Error" responses from AdvFN due to a change from a Windows Update.
Link AdvFN.002 - A workaround for AdvFN users that get redirected to one of their regional domains
...except that you would use "in" instead of "uk" for the advice in the second link.
Remember that you need to exit EXCEL before making those changes. When you restart EXCEL, it should load all the new settings. And hopefully work.
Actually, the URL I should have given you for testing redirection is:
http://www.advfn.com/p.php?pid=financials&mode=quarterly_reports&symbol=MMM
If you need the "in" prefix, that should redirect you to:
http://in.advfn.com/exchanges/NYSE/MMM/financials?mode=quarterly_reports
If you don't need the "in" prefix, it shoulld redirect you to:
http://www.advfn.com/exchanges/NYSE/MMM/financials?mode=quarterly_reports
On Fri, Mar 7, 2014 at 12:56 AM, <vijay_gangrade@yahoo.com> wrote:
> =RCHGetElementNumber("Version")
returns "Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\SMF
Add-In; www; 1)" which seems most recent version.
>
>I am from India but the URL http://www.advfn.com/exchanges/NYSE/MMM/stock-price doesn't redirect me anywhere. I tried in both Firefox and IE. However http://www.advfn.com does redirect me to http://in.advfn.com/
>I don't think it should matter since then I tried http://in.advfn.com/exchanges/NYSE/MMM/stock-price but that resulted in 404 error.
>
>I am in the process of checking Links area as you suggested. Is there anything else I should try in addition to that.
>
On Fri, Mar 7, 2014 at 1:03 AM, vijay gangrade <vijay_gangrade@yahoo.com> wrote:
>
>I forgot to mention in previous mail. I have tried this in Windows Vista as well as Windows XP but am getting the same result.
>
Thank you so much for your help. It started working but it seems it is not giving results for all companies within NYSE. I made a test xls and tried to find only the available periods for some companies. But it works for some and gives "Error" for the rest. Also I noticed that it is not consistent despite of deleting history and cookies from Internet explorer. So for a company, it gives error on some occasions and correct results on other. Attached is the example xls I am using.
I was wondering if it is because of time-outs. I wouldn't be surprised if that is the case since my internet speed is a lot lesser than usual. Is there any way to override it.
I also wanted to ask if there is any way to stop the add-in from running as soon as the xls is opened. That way I can first prepare the xls completely and then run it like a macro.
Thank you once again. This has made my life a lot easier.
Regards,
Vijay Gangrade
On Friday, March 7, 2014 2:41 PM, Randy Harmelink <rharmelink@gmail.com> wrote:
The LINKS area of the Yahoo group has now been moved to the SMF website:
http://ogres-crypt.com/SMF/Tips-and-FAQs/
The two tips you should take a look at are:
Link AdvFN.001 - A possible fix for "Error" responses from AdvFN due to a change from a Windows Update.
Link AdvFN.002 - A workaround for AdvFN users that get redirected to one of their regional domains
...except that you would use "in" instead of "uk" for the advice in the second link.
Remember that you need to exit EXCEL before making those changes. When you restart EXCEL, it should load all the new settings. And hopefully work.
Actually, the URL I should have given you for testing redirection is:
http://www.advfn.com/p.php?pid=financials&mode=quarterly_reports&symbol=MMM
If you need the "in" prefix, that should redirect you to:
http://in.advfn.com/exchanges/NYSE/MMM/financials?mode=quarterly_reports
If you don't need the "in" prefix, it shoulld redirect you to:
http://www.advfn.com/exchanges/NYSE/MMM/financials?mode=quarterly_reports
On Fri, Mar 7, 2014 at 12:56 AM, <vijay_gangrade@yahoo.com> wrote:
> =RCHGetElementNumber("Version")
returns "Stock Market Functions add-in, Version 2.1.2014.01.30 (C:\SMF
Add-In; www; 1)" which seems most recent version.
>
>I am from India but the URL http://www.advfn.com/exchanges/NYSE/MMM/stock-price doesn't redirect me anywhere. I tried in both Firefox and IE. However http://www.advfn.com does redirect me to http://in.advfn.com/
>I don't think it should matter since then I tried http://in.advfn.com/exchanges/NYSE/MMM/stock-price but that resulted in 404 error.
>
>I am in the process of checking Links area as you suggested. Is there anything else I should try in addition to that.
>
On Fri, Mar 7, 2014 at 1:03 AM, vijay gangrade <vijay_gangrade@yahoo.com> wrote:
>
>I forgot to mention in previous mail. I have tried this in Windows Vista as well as Windows XP but am getting the same result.
>
Attachment(s) from vijay gangrade
1 of 1 File(s)
Mon Mar 10, 2014 12:17 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
I get errors on ABEV, ACG, ACP. Totally correct, since AdvFN has no annual
financial statements for them.
I also got an error on ACRE. That's because it only has one year of
history. The "# of periods" is derived from the drop-down box that lists
available periods. When only one year is available, that drop-down box
doesn't appear. Not gonna worry about it, since it's such a rare situation.
The other four -- MSFT, FB, MEG, MEI -- worked fine here.
To prevent execution, you can change all the formulas to be conditional.
For example:
=IF($F$1<>"Y","--",smfGetAdvFNElement(A2,B2,999))
Then, the SMF formula will only calculate if cell F1 has a "Y" in it. But
you'll need to make sure to set it to "N" whenever you save the file. :)
You can also take a look at the smfUpdateDownloadTable process. It *is* a
macro, and saves values into cells instead of recalculating formulas. It
only updates the values when you run the macro:
Link <http://finance.groups.yahoo.com/group/smf_addin/message/3027>2.1a -
Using the smfUpdateDownloadTable macro
Link<http://finance.groups.yahoo.com/group/smf_addin/message/8832>2.1b
- Using the smfUpdateDownloadTable macro
Link<http://seekingalpha.com/instablog/326478-derek-a-barrett/1836421-automating-alpha-optimize-large-data-sets-with-the-smfupdatedownloadtable-macro>2.1c
- A blog entry on using the smfUpdateDownloadTable macro
I find it easiest to just start with the sample workbook, so that all the
basic cell names and structure is already in place.
On Sun, Mar 9, 2014 at 11:43 PM, vijay gangrade <vijay_gangrade@yahoo.com>wrote:
>
> Thank you so much for your help. It started working but it seems it is not
> giving results for all companies within NYSE. I made a test xls and tried
> to find only the available periods for some companies. But it works for
> some and gives "Error" for the rest. Also I noticed that it is not
> consistent despite of deleting history and cookies from Internet explorer.
> So for a company, it gives error on some occasions and correct results on
> other. Attached is the example xls I am using.
>
> I was wondering if it is because of time-outs. I wouldn't be surprised if
> that is the case since my internet speed is a lot lesser than usual. Is
> there any way to override it.
>
> I also wanted to ask if there is any way to stop the add-in from running
> as soon as the xls is opened. That way I can first prepare the xls
> completely and then run it like a macro.
>
> Thank you once again. This has made my life a lot easier.
>
financial statements for them.
I also got an error on ACRE. That's because it only has one year of
history. The "# of periods" is derived from the drop-down box that lists
available periods. When only one year is available, that drop-down box
doesn't appear. Not gonna worry about it, since it's such a rare situation.
The other four -- MSFT, FB, MEG, MEI -- worked fine here.
To prevent execution, you can change all the formulas to be conditional.
For example:
=IF($F$1<>"Y","--",smfGetAdvFNElement(A2,B2,999))
Then, the SMF formula will only calculate if cell F1 has a "Y" in it. But
you'll need to make sure to set it to "N" whenever you save the file. :)
You can also take a look at the smfUpdateDownloadTable process. It *is* a
macro, and saves values into cells instead of recalculating formulas. It
only updates the values when you run the macro:
Link <http://finance.groups.yahoo.com/group/smf_addin/message/3027>2.1a -
Using the smfUpdateDownloadTable macro
Link<http://finance.groups.yahoo.com/group/smf_addin/message/8832>2.1b
- Using the smfUpdateDownloadTable macro
Link<http://seekingalpha.com/instablog/326478-derek-a-barrett/1836421-automating-alpha-optimize-large-data-sets-with-the-smfupdatedownloadtable-macro>2.1c
- A blog entry on using the smfUpdateDownloadTable macro
I find it easiest to just start with the sample workbook, so that all the
basic cell names and structure is already in place.
On Sun, Mar 9, 2014 at 11:43 PM, vijay gangrade <vijay_gangrade@yahoo.com>wrote:
>
> Thank you so much for your help. It started working but it seems it is not
> giving results for all companies within NYSE. I made a test xls and tried
> to find only the available periods for some companies. But it works for
> some and gives "Error" for the rest. Also I noticed that it is not
> consistent despite of deleting history and cookies from Internet explorer.
> So for a company, it gives error on some occasions and correct results on
> other. Attached is the example xls I am using.
>
> I was wondering if it is because of time-outs. I wouldn't be surprised if
> that is the case since my internet speed is a lot lesser than usual. Is
> there any way to override it.
>
> I also wanted to ask if there is any way to stop the add-in from running
> as soon as the xls is opened. That way I can first prepare the xls
> completely and then run it like a macro.
>
> Thank you once again. This has made my life a lot easier.
>
Tidak ada komentar:
Posting Komentar