13 New Messages
Digest #2920
Messages
Wed Jan 8, 2014 5:01 am (PST) . Posted by:
"Dave Charlaff" copthornelabs
Thank you.
So easy.
I must have been tired.
Randy,
On a personal note: I have been using your functions for years now (at least five I think), and I have not taken the time to say thank you for your tireless, polite and swift responses.
In a world where it appears that too many people think that obscenities may be expressed in public and on the internet as their right without thought to the deliberate and disappointingly uncreative nature of their expression and the tiresome offense it gives, it is a pleasure to read your calm, precise adult responses. Thank you for your tireless dedication to this project. Thank you for your courtesy.
A pox on all those whose only form of expression is an obscenity and who misapprehend that this diminishes them in the eyes of the listener; quite the opposite of their intention to enhance their stature. Tiresome and uncreative as this expression may have been, it is encouraging to me to see so many of your loyal adherents publicly bounding into the fray on your behalf. My congratulations to them too for their measured responses.
Your project is an unqualified success. It has obviously helped a myriad of seekers. It has helped me immeasurably.
Thank you. God Bless.
Dave C.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of Randy Harmelink
Sent: Tuesday, January 07, 2014 6:49 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Total volume
Just array-enter this over a 3-row by 1-column range:
=RCHGetYahooQuotes("^TV.O,^TV.N,^TV.AQ","l1")
On Tue, Jan 7, 2014 at 1:17 PM, Dave Charlaff <davecharlaff@hotmail.com> wrote:
Yahoo provides Total Volume for the NYSE at http://finance.yahoo.com/q?s=%5ETV.o <http://finance.yahoo.com/q?s=%5ETV.o&ql=1> &ql=1. Their actual symbol is ^TV.N.
I am trying to capture Total Volume for the NTSE and the OTC (NASDAQ - ^TV.O) and the American Stock Exchange(^TV.AQ I think) to try and get a feel for total volume of shares traded daily.
This is not a price. What is the simplest way to get these volume stats into separate cells in my spreadsheet?
So easy.
I must have been tired.
Randy,
On a personal note: I have been using your functions for years now (at least five I think), and I have not taken the time to say thank you for your tireless, polite and swift responses.
In a world where it appears that too many people think that obscenities may be expressed in public and on the internet as their right without thought to the deliberate and disappointingly uncreative nature of their expression and the tiresome offense it gives, it is a pleasure to read your calm, precise adult responses. Thank you for your tireless dedication to this project. Thank you for your courtesy.
A pox on all those whose only form of expression is an obscenity and who misapprehend that this diminishes them in the eyes of the listener; quite the opposite of their intention to enhance their stature. Tiresome and uncreative as this expression may have been, it is encouraging to me to see so many of your loyal adherents publicly bounding into the fray on your behalf. My congratulations to them too for their measured responses.
Your project is an unqualified success. It has obviously helped a myriad of seekers. It has helped me immeasurably.
Thank you. God Bless.
Dave C.
From: smf_addin@yahoogrou
Sent: Tuesday, January 07, 2014 6:49 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Total volume
Just array-enter this over a 3-row by 1-column range:
=RCHGetYahooQuotes(
On Tue, Jan 7, 2014 at 1:17 PM, Dave Charlaff <davecharlaff@
Yahoo provides Total Volume for the NYSE at http://finance.
I am trying to capture Total Volume for the NTSE and the OTC (NASDAQ - ^TV.O) and the American Stock Exchange(^TV.
This is not a price. What is the simplest way to get these volume stats into separate cells in my spreadsheet?
Wed Jan 8, 2014 7:00 am (PST) . Posted by:
"James Olson" trunk7174
Not to mention that the ones who are making those sort of comments are
probably too lazy to create or re-create the formulas themselves. If they
really don't like it, they should "roll their own" formulas and macros.
J.O.
On Wed, Jan 8, 2014 at 8:01 AM, Dave Charlaff <davecharlaff@hotmail.com>wrote:
>
>
> Thank you.
>
>
>
> So easy.
>
>
>
> I must have been tired.
>
>
>
> Randy,
>
>
>
> On a personal note: I have been using your functions for years now (at
> least five I think), and I have not taken the time to say thank you for
> your tireless, polite and swift responses.
>
>
>
> In a world where it appears that too many people think that obscenities
> may be expressed in public and on the internet as their right without
> thought to the deliberate and disappointingly uncreative nature of their
> expression and the tiresome offense it gives, it is a pleasure to read your
> calm, precise adult responses. Thank you for your tireless dedication to
> this project. Thank you for your courtesy.
>
>
>
> A pox on all those whose only form of expression is an obscenity and who
> misapprehend that this diminishes them in the eyes of the listener; quite
> the opposite of their intention to enhance their stature. Tiresome and
> uncreative as this expression may have been, it is encouraging to me to see
> so many of your loyal adherents publicly bounding into the fray on your
> behalf. My congratulations to them too for their measured responses.
>
>
>
> Your project is an unqualified success. It has obviously helped a myriad
> of seekers. It has helped me immeasurably.
>
>
>
> Thank you. God Bless.
>
>
>
> Dave C.
>
>
>
> *From:* smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] *On
> Behalf Of *Randy Harmelink
> *Sent:* Tuesday, January 07, 2014 6:49 PM
> *To:* smf_addin@yahoogroups.com
> *Subject:* Re: [smf_addin] Total volume
>
>
>
>
>
> Just array-enter this over a 3-row by 1-column range:
>
> =RCHGetYahooQuotes("^TV.O,^TV.N,^TV.AQ","l1")
>
>
>
> On Tue, Jan 7, 2014 at 1:17 PM, Dave Charlaff <davecharlaff@hotmail.com>
> wrote:
>
>
>
> Yahoo provides Total Volume for the NYSE at
> http://finance.yahoo.com/q?s=%5ETV.o&ql=1. Their actual symbol is ^TV.N.
>
>
>
> I am trying to capture Total Volume for the NTSE and the OTC (NASDAQ -
> ^TV.O) and the American Stock Exchange(^TV.AQ I think) to try and get a
> feel for total volume of shares traded daily.
>
>
>
> This is not a price. What is the simplest way to get these volume stats
> into separate cells in my spreadsheet?
>
>
>
>
>
>
>
probably too lazy to create or re-create the formulas themselves. If they
really don't like it, they should "roll their own" formulas and macros.
J.O.
On Wed, Jan 8, 2014 at 8:01 AM, Dave Charlaff <davecharlaff@
>
>
> Thank you.
>
>
>
> So easy.
>
>
>
> I must have been tired.
>
>
>
> Randy,
>
>
>
> On a personal note: I have been using your functions for years now (at
> least five I think), and I have not taken the time to say thank you for
> your tireless, polite and swift responses.
>
>
>
> In a world where it appears that too many people think that obscenities
> may be expressed in public and on the internet as their right without
> thought to the deliberate and disappointingly uncreative nature of their
> expression and the tiresome offense it gives, it is a pleasure to read your
> calm, precise adult responses. Thank you for your tireless dedication to
> this project. Thank you for your courtesy.
>
>
>
> A pox on all those whose only form of expression is an obscenity and who
> misapprehend that this diminishes them in the eyes of the listener; quite
> the opposite of their intention to enhance their stature. Tiresome and
> uncreative as this expression may have been, it is encouraging to me to see
> so many of your loyal adherents publicly bounding into the fray on your
> behalf. My congratulations to them too for their measured responses.
>
>
>
> Your project is an unqualified success. It has obviously helped a myriad
> of seekers. It has helped me immeasurably.
>
>
>
> Thank you. God Bless.
>
>
>
> Dave C.
>
>
>
> *From:* smf_addin@yahoogrou
> Behalf Of *Randy Harmelink
> *Sent:* Tuesday, January 07, 2014 6:49 PM
> *To:* smf_addin@yahoogrou
> *Subject:* Re: [smf_addin] Total volume
>
>
>
>
>
> Just array-enter this over a 3-row by 1-column range:
>
> =RCHGetYahooQuotes(
>
>
>
> On Tue, Jan 7, 2014 at 1:17 PM, Dave Charlaff <davecharlaff@
> wrote:
>
>
>
> Yahoo provides Total Volume for the NYSE at
> http://finance.
>
>
>
> I am trying to capture Total Volume for the NTSE and the OTC (NASDAQ -
> ^TV.O) and the American Stock Exchange(^TV.
> feel for total volume of shares traded daily.
>
>
>
> This is not a price. What is the simplest way to get these volume stats
> into separate cells in my spreadsheet?
>
>
>
>
>
>
>
Wed Jan 8, 2014 7:00 am (PST) . Posted by:
hobo.1118
I am sure this is simple however with the update to the RCHGetElements File it struck out element '25' which was the last stock price. What is the substitute for this since I don't see another "Last Price" element in the file. Much appreciated.
Wed Jan 8, 2014 7:12 am (PST) . Posted by:
"Randy Harmelink" rharmelink
I obsoleted element #25 recently. Ever since MSN updated their stock print
page last year (or was it 2012?), they've been posting the previous day's
closing price as "Last Price".
You should use RCHGetYahooQuotes() to get stock quotes.
On Wed, Jan 8, 2014 at 5:53 AM, <jeffwermers@ymail.com> wrote:
>
> I am sure this is simple however with the update to the RCHGetElements
> File it struck out element '25' which was the last stock price. What is
> the substitute for this since I don't see another "Last Price" element in
> the file. Much appreciated.
>
page last year (or was it 2012?), they've been posting the previous day's
closing price as "Last Price".
You should use RCHGetYahooQuotes(
On Wed, Jan 8, 2014 at 5:53 AM, <jeffwermers@
>
> I am sure this is simple however with the update to the RCHGetElements
> File it struck out element '25' which was the last stock price. What is
> the substitute for this since I don't see another "Last Price" element in
> the file. Much appreciated.
>
Wed Jan 8, 2014 10:11 am (PST) . Posted by:
Charles8839
Randy, Is there a way to get in touch with you in regards to helping me pull the balance sheet, income statement and Cash Flow into Excel. I have office 365. I'm willing to pay for your time. I have the spreadsheets that match exactly Yahoo format.. You can reach me 703 931 3290.
Wed Jan 8, 2014 11:10 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Did the templates not work?
On Wed, Jan 8, 2014 at 11:11 AM, <charles.coker@cokersoft.com> wrote:
> Randy, Is there a way to get in touch with you in regards to helping me
> pull the balance sheet, income statement and Cash Flow into Excel. I have
> office 365. I'm willing to pay for your time. I have the spreadsheets
> that match exactly Yahoo format.. You can reach me 703 931 3290.
>
On Wed, Jan 8, 2014 at 11:11 AM, <charles.coker@
> Randy, Is there a way to get in touch with you in regards to helping me
> pull the balance sheet, income statement and Cash Flow into Excel. I have
> office 365. I'm willing to pay for your time. I have the spreadsheets
> that match exactly Yahoo format.. You can reach me 703 931 3290.
>
Wed Jan 8, 2014 11:51 am (PST) . Posted by:
Charles8839
I look in the files area and there were so many different templates I wasn't sure which ones to select for the Balance Sheet , Income Statement and Statement of Cash Flow. I have the spreadsheets set up exactly in the same format as Yahoo. I'm willing to pay you to set this up right the first time for me. 703 931 3290
Wed Jan 8, 2014 2:54 pm (PST) . Posted by:
hamishthedenizen
From the uploads by forum members, try:
10 year model - GuruFocus - All Types.xlsx https://f1.grp.yahoofs.com/v2/VNbNUsc1csksbxVDpqFOG6Lk4wvJHzjABjsfmZ4dpuEJ29UGVpx7zF4l5ZSVqDApUEBe_51qE7KVdMNqWwAteUKLL5Gt-N3t0euS_LcJz3MGC9QLx1qD3DW1Ebxai4bKbT_gFldBGBX1tLwiHWoA6uMcKv-A4WLzF8x25LmJKbPIk24WuIdsOdIMepvtM7pz9wTsTf8zNm41t9o65b9RJw/10%20year%20model%20-%20GuruFocus%20-%20All%20Types.xlsx or 15-Year-Financial-Statement-Retriever-ADVFN.xlsx https://f1.grp.yahoofs.com/v2/VNbNUnB9HzcsbxVDcbufr9RerHk-sHuacKlX6EM6eLPOcGbxCbJvpjJbTl9zzLwpyK4k4bWURpiJalG6MJZdgRzOuEsZVjtR-WRbwwaszWbyTP9NpH5Ocd0HqyTaKm_YYUKadVvCmq8XaQ_MZ8brE540P5qUjoeeFpUhxyltFSUPdnxZn-HcLHmWfb4eajrkxGmpSjRiif4/15-Year-Financial-Statement-Retriever-ADVFN.xlsx for starters - they pull all 3 statements, but from different databases.
From "Templates and Examples" Randy has built
SMF-Template-Google-Balance-Sheet-Tables.xls https://f1.grp.yahoofs.com/v2/zNbNUvvGi84mDsnq8ZHrS0KIoyl0YtEhH27390kxXTTnp8PL4UKinYDNquilcueAIAahC5jli-9ntdnZ09aHc8jtcHqsk61K8n1XcUGlYmfzRA5HAQr4b2fv_FLBKdJ9XnJ8VWLeyCyAiH27hg7U8MVk9TVOZ0jY6G9nxSJF1a5Yoka12BgRjQVvecah8WZB/SMF-Template-Google-Balance-Sheet-Tables.xls
There are other sheets below it for income statement and cashflows. https://f1.grp.yahoofs.com/v2/zNbNUvvGi84mDsnq8ZHrS0KIoyl0YtEhH27390kxXTTnp8PL4UKinYDNquilcueAIAahC5jli-9ntdnZ09aHc8jtcHqsk61K8n1XcUGlYmfzRA5HAQr4b2fv_FLBKdJ9XnJ8VWLeyCyAiH27hg7U8MVk9TVOZ0jY6G9nxSJF1a5Yoka12BgRjQVvecah8WZB/SMF-Template-Google-Balance-Sheet-Tables.xls
10 year model - GuruFocus - All Types.xlsx https://f1.grp.
From "Templates and Examples" Randy has built
SMF-Template-
There are other sheets below it for income statement and cashflows. https://f1.grp.
Wed Jan 8, 2014 3:35 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Unfortunately, the "f1.*" links aren't permanent. As far as I know, there
is no longer a "permalink" available for files in a Yahoo group.
On Wed, Jan 8, 2014 at 3:54 PM, <dan-carroll@yahoo.com> wrote:
> From the uploads by forum members, try:
>
> 10 year model - GuruFocus - All Types.xlsx<https://f1.grp.yahoofs.com/v2/VNbNUsc1csksbxVDpqFOG6Lk4wvJHzjABjsfmZ4dpuEJ29UGVpx7zF4l5ZSVqDApUEBe_51qE7KVdMNqWwAteUKLL5Gt-N3t0euS_LcJz3MGC9QLx1qD3DW1Ebxai4bKbT_gFldBGBX1tLwiHWoA6uMcKv-A4WLzF8x25LmJKbPIk24WuIdsOdIMepvtM7pz9wTsTf8zNm41t9o65b9RJw/10%20year%20model%20-%20GuruFocus%20-%20All%20Types.xlsx>or
> 15-Year-Financial-Statement-Retriever-ADVFN.xlsx<https://f1.grp.yahoofs.com/v2/VNbNUnB9HzcsbxVDcbufr9RerHk-sHuacKlX6EM6eLPOcGbxCbJvpjJbTl9zzLwpyK4k4bWURpiJalG6MJZdgRzOuEsZVjtR-WRbwwaszWbyTP9NpH5Ocd0HqyTaKm_YYUKadVvCmq8XaQ_MZ8brE540P5qUjoeeFpUhxyltFSUPdnxZn-HcLHmWfb4eajrkxGmpSjRiif4/15-Year-Financial-Statement-Retriever-ADVFN.xlsx>for starters - they pull all 3 statements, but from different databases.
>
> From "Templates and Examples" Randy has built
>
> SMF-Template-Google-Balance-Sheet-Tables.xls<https://f1.grp.yahoofs.com/v2/zNbNUvvGi84mDsnq8ZHrS0KIoyl0YtEhH27390kxXTTnp8PL4UKinYDNquilcueAIAahC5jli-9ntdnZ09aHc8jtcHqsk61K8n1XcUGlYmfzRA5HAQr4b2fv_FLBKdJ9XnJ8VWLeyCyAiH27hg7U8MVk9TVOZ0jY6G9nxSJF1a5Yoka12BgRjQVvecah8WZB/SMF-Template-Google-Balance-Sheet-Tables.xls>
>
> There are other sheets below it for income statement and cashflows.<https://f1.grp.yahoofs.com/v2/zNbNUvvGi84mDsnq8ZHrS0KIoyl0YtEhH27390kxXTTnp8PL4UKinYDNquilcueAIAahC5jli-9ntdnZ09aHc8jtcHqsk61K8n1XcUGlYmfzRA5HAQr4b2fv_FLBKdJ9XnJ8VWLeyCyAiH27hg7U8MVk9TVOZ0jY6G9nxSJF1a5Yoka12BgRjQVvecah8WZB/SMF-Template-Google-Balance-Sheet-Tables.xls>
>
is no longer a "permalink&quo
On Wed, Jan 8, 2014 at 3:54 PM, <dan-carroll@
> From the uploads by forum members, try:
>
> 10 year model - GuruFocus - All Types.xlsx<https://f1.grp.
> 15-Year-Financial-
>
> From "Templates and Examples" Randy has built
>
> SMF-Template-
>
> There are other sheets below it for income statement and cashflows.<https://f1.grp.
>
Wed Jan 8, 2014 2:05 pm (PST) . Posted by:
drrossien
First, like everybody else, thanks for making your work available, and for your seemingly unending supply of patience with Group members. Sometimes I see the questions you get and want to scream.
I see you answer the same questions multiple times per week. I appreciate that you try to answer a few basic questions on the Group About page, that you cover lots of stuff in your Overview.html page and that there is a sort-of FAQ list on the Links section, including the SeekingAlpha "Getting Started" document, but IMO what folks need is a static one page FAQ that says something like the below, preferably with a hyperlink to it on the front of the About page (I don't know if you can have hyperlinksthere but I hope so).
1. What are the "Stock Market Functions" add-in?
They are a set of "add ins" (aka user defined functions) that work in all recent versions of Excel for Windows (they do not work on Excel for Macs) that pull data off Internet web pages and into an Excel spreadsheet. There are some that are "specialized" to work with sites like Yahoo Finance and others that will work with any site (not just market data sites) that creates a web page in (correctly formed) HTML.
2. How to I use them?
To use them you have to a) Download and install the Add-Ins, b) choose the one(s) you want to use and then c) array enter the formula -- with the add-in function name (prefaced, like all functions, with an = sign) and any parameters -- into a cell of the spreadsheet. Instructions for all this can be found in the Documentation section. So for example:
=RCHGetYahooHistory("IBM",2007,1,3,2007,1,3,,"A",0)
will return the adjusted closing price for (ticker symbol) IBM for January 3, 2007 (e.g. 86.04). Because this returns a single number you can just type it in without array-entering and it will work, but most functions have to be array-entered to work.
3. How do I array enter something? (I've been using Excel for years and never heard of it!)
To array-enter a formula in Excel, first highlight the range of cells where you would like the returned data to appear -- the number of rows for the range should be the number of periods of data you are requesting from the function, while the number of columns for the range should be the number of data items you are requesting for each date from the function. Next, enter your formula and then press Ctrl-Shift-Enter. So for example, if you use RCHGetYahooHistory without any parameters it returns the Open, High, Low, Close, Volume and Adjusted Close for the specified Ticker, starting from the last market close and going back however many rows you array-entered. Array entering =RCHGetYahooHistory("IBM") over a 7x9 rectangle of cells on 1/8/2013 yields:
Date Open High Low Close Volume Adj Close 1/7/2014 186.39 190.35 186.38 189.71 5,932,300 189.71 1/6/2014 187.15 187.36 185.30 186.00 4,067,800 186.00 1/3/2014 185.83 187.35 185.30 186.64 4,063,200 186.64 1/2/2014 187.21 187.40 185.20 185.53 4,546,500 185.53 12/31/2013 186.49 187.79 186.30 187.57 3,619,700 187.57 12/30/2013 185.32 186.70 184.67 186.41 3,018,600 186.41 12/27/2013 185.84 186.50 184.56 185.08 3,381,600 185.08 12/26/2013 183.56 185.56 183.51 185.35 3,325,700 185.35
Note that the first row returns the column labels, so if you neglect to array-enter a function that expects it, you will typically just get labels without any values.
4. Do I have to type in the Tickers each time?
No, instead of "IBM" in the example above you can just reference a cell, and then put the Ticker in the cell! So assuming you typed IBM into cell A1 you could put this into Cell B1:
=RCHGetYahooHistory(A1,2007,1,3,2007,1,3,,"A",0)
You can also make use of the Excel string concatenation function (&). If this isn't obvious how to use it, Google around.
5. Anything else I need to know up front?
a) The functions were developed in the US, and are somewhat US-centric. They have been used quite successfully by folks throughout the world, but if you aren't in the US there are a few problems you could face. For example, some of the functions assume the decimal character is a period and the thousands delimiter is a comma. To use these functions you'll need to change your "system separators" in Excel. Another issue is that some websites try to be "smart" and use your IP address to determine your PC's location and then re-direct you to a local website.
b) It is possible that your Internet access options could be set to prevent the Add-In functions from working. In Internet Explorer for example you may have to go to Tools, Internet Options, Security, Custom Level button (for the zone that's currently highlighted); Find the 'Access Data Sources Across Domains' option and set to 'Enable'. Click OK to save the settings change.
c) Regardless of what browser you typically use, the Add-Ins make use of Internet Explorer's cache. What this means is if you want to access a site with the Add-Ins that requires a sign on you need to sign on first with Internet Explorer.
6. The Add-Ins have saved me so much time and/or made me a much better investor! How do I pay for them?
The Add-Ins were developed by Randy Harmelink to support his personal investing process and he then made them available for free to the general public. He neither asks for nor accepts payment, all he asks is that you "pay it forward" in any way you'd like. Note that this is therefore a 'hobby' for him, and any time he spends improving them or answering questions is time away from family, friends and whatever else he would like to be doing, so any suggestions of the form "it would be so much better (for me) if you changed this" will typically be met with "Thanks, why don't you try!".
I see you answer the same questions multiple times per week. I appreciate that you try to answer a few basic questions on the Group About page, that you cover lots of stuff in your Overview.html page and that there is a sort-of FAQ list on the Links section, including the SeekingAlpha "Getting Started" document, but IMO what folks need is a static one page FAQ that says something like the below, preferably with a hyperlink to it on the front of the About page (I don't know if you can have hyperlinksthere but I hope so).
1. What are the "Stock Market Functions" add-in?
They are a set of "add ins" (aka user defined functions) that work in all recent versions of Excel for Windows (they do not work on Excel for Macs) that pull data off Internet web pages and into an Excel spreadsheet. There are some that are "specialized&q
2. How to I use them?
To use them you have to a) Download and install the Add-Ins, b) choose the one(s) you want to use and then c) array enter the formula -- with the add-in function name (prefaced, like all functions, with an = sign) and any parameters -- into a cell of the spreadsheet. Instructions for all this can be found in the Documentation section. So for example:
=RCHGetYahooHistory
will return the adjusted closing price for (ticker symbol) IBM for January 3, 2007 (e.g. 86.04). Because this returns a single number you can just type it in without array-entering and it will work, but most functions have to be array-entered to work.
3. How do I array enter something? (I've been using Excel for years and never heard of it!)
To array-enter a formula in Excel, first highlight the range of cells where you would like the returned data to appear -- the number of rows for the range should be the number of periods of data you are requesting from the function, while the number of columns for the range should be the number of data items you are requesting for each date from the function. Next, enter your formula and then press Ctrl-Shift-Enter. So for example, if you use RCHGetYahooHistory without any parameters it returns the Open, High, Low, Close, Volume and Adjusted Close for the specified Ticker, starting from the last market close and going back however many rows you array-entered. Array entering =RCHGetYahooHistory
Date Open High Low Close Volume Adj Close 1/7/2014 186.39 190.35 186.38 189.71 5,932,300 189.71 1/6/2014 187.15 187.36 185.30 186.00 4,067,800 186.00 1/3/2014 185.83 187.35 185.30 186.64 4,063,200 186.64 1/2/2014 187.21 187.40 185.20 185.53 4,546,500 185.53 12/31/2013 186.49 187.79 186.30 187.57 3,619,700 187.57 12/30/2013 185.32 186.70 184.67 186.41 3,018,600 186.41 12/27/2013 185.84 186.50 184.56 185.08 3,381,600 185.08 12/26/2013 183.56 185.56 183.51 185.35 3,325,700 185.35
Note that the first row returns the column labels, so if you neglect to array-enter a function that expects it, you will typically just get labels without any values.
4. Do I have to type in the Tickers each time?
No, instead of "IBM" in the example above you can just reference a cell, and then put the Ticker in the cell! So assuming you typed IBM into cell A1 you could put this into Cell B1:
=RCHGetYahooHistory
You can also make use of the Excel string concatenation function (&). If this isn't obvious how to use it, Google around.
5. Anything else I need to know up front?
a) The functions were developed in the US, and are somewhat US-centric. They have been used quite successfully by folks throughout the world, but if you aren't in the US there are a few problems you could face. For example, some of the functions assume the decimal character is a period and the thousands delimiter is a comma. To use these functions you'll need to change your "system separators" in Excel. Another issue is that some websites try to be "smart" and use your IP address to determine your PC's location and then re-direct you to a local website.
b) It is possible that your Internet access options could be set to prevent the Add-In functions from working. In Internet Explorer for example you may have to go to Tools, Internet Options, Security, Custom Level button (for the zone that's currently highlighted)
c) Regardless of what browser you typically use, the Add-Ins make use of Internet Explorer'
6. The Add-Ins have saved me so much time and/or made me a much better investor! How do I pay for them?
The Add-Ins were developed by Randy Harmelink to support his personal investing process and he then made them available for free to the general public. He neither asks for nor accepts payment, all he asks is that you "pay it forward" in any way you'd like. Note that this is therefore a 'hobby&#
Wed Jan 8, 2014 3:34 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Part of the problem is that some of the most frequent questions have the
same answer -- learn how to use EXCEL.
Being a typical programmer, the thing I hate most is writing up
documentation, so it gets a short straw. :(
But it doesn't help that many of the questions asked are already answered
somewhere, either on the front page of the group, the email welcoming
someone to the group, or in the LINKS area of the group. Even in the
documentation folder. But people don't take the time to read them. It's
easier for them, to ask.
I see that on other groups as well. Someone will ask a question that could
easily have been answered in 30 seconds with a quick visit to Google. Or a
quick search of the group. Yet they will spend more time writing up a
message to the group asking the question, and then wait hours, even days,
for someone to answer.
When I was a professional programmer, I was the guru of the group. You
don't know how many times someone would come and ask me a question and the
first thing I would do is pull the Programmer's Guide or Language Reference
off my shelf, look it up in the index, and then read it through with them.
same answer -- learn how to use EXCEL.
Being a typical programmer, the thing I hate most is writing up
documentation, so it gets a short straw. :(
But it doesn't help that many of the questions asked are already answered
somewhere, either on the front page of the group, the email welcoming
someone to the group, or in the LINKS area of the group. Even in the
documentation folder. But people don't take the time to read them. It's
easier for them, to ask.
I see that on other groups as well. Someone will ask a question that could
easily have been answered in 30 seconds with a quick visit to Google. Or a
quick search of the group. Yet they will spend more time writing up a
message to the group asking the question, and then wait hours, even days,
for someone to answer.
When I was a professional programmer, I was the guru of the group. You
don't know how many times someone would come and ask me a question and the
first thing I would do is pull the Programmer&#
off my shelf, look it up in the index, and then read it through with them.
Wed Jan 8, 2014 3:51 pm (PST) . Posted by:
"Jim Ranum" amt2100
Just one man's 2 cents here, I can understand Randy's frustration with people not doing their own basic research, but I really like what you've written up here.
For me, when I first came to this group after searching for a tool to extract prices/data/etc. from the ether, I didn't really get right off what or how this tool worked. This synopsis gives you that and leads you into the Documentation a little more prepared to work through it.
Very nice.
Jim
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com] On Behalf Of DRossien@gmail.com
Sent: Wednesday, January 08, 2014 5:05 PM
To: smf_addin@yahoogroups.com
Subject: [smf_addin] A Modest Proposal for Randy (apologies for the length)
First, like everybody else, thanks for making your work available, and for your seemingly unending supply of patience with Group members. Sometimes I see the questions you get and want to scream.
I see you answer the same questions multiple times per week. I appreciate that you try to answer a few basic questions on the Group About page, that you cover lots of stuff in your Overview.html page and that there is a sort-of FAQ list on the Links section, including the SeekingAlpha "Getting Started" document, but IMO what folks need is a static one page FAQ that says something like the below, preferably with a hyperlink to it on the front of the About page (I don't know if you can have hyperlinksthere but I hope so).
1. What are the "Stock Market Functions" add-in?
They are a set of "add ins" (aka user defined functions) that work in all recent versions of Excel for Windows (they do not work on Excel for Macs) that pull data off Internet web pages and into an Excel spreadsheet. There are some that are "specialized" to work with sites like Yahoo Finance and others that will work with any site (not just market data sites) that creates a web page in (correctly formed) HTML.
2. How to I use them?
To use them you have to a) Download and install the Add-Ins, b) choose the one(s) you want to use and then c) array enter the formula -- with the add-in function name (prefaced, like all functions, with an = sign) and any parameters -- into a cell of the spreadsheet. Instructions for all this can be found in the Documentation section. So for example:
=RCHGetYahooHistory("IBM",2007,1,3,2007,1,3,,"A",0)
will return the adjusted closing price for (ticker symbol) IBM for January 3, 2007 (e.g. 86.04). Because this returns a single number you can just type it in without array-entering and it will work, but most functions have to be array-entered to work.
3. How do I array enter something? (I've been using Excel for years and never heard of it!)
To array-enter a formula in Excel, first highlight the range of cells where you would like the returned data to appear -- the number of rows for the range should be the number of periods of data you are requesting from the function, while the number of columns for the range should be the number of data items you are requesting for each date from the function. Next, enter your formula and then press Ctrl-Shift-Enter. So for example, if you use RCHGetYahooHistory without any parameters it returns the Open, High, Low, Close, Volume and Adjusted Close for the specified Ticker, starting from the last market close and going back however many rows you array-entered. Array entering =RCHGetYahooHistory("IBM") over a 7x9 rectangle of cells on 1/8/2013 yields:
Date
Open
High
Low
Close
Volume
Adj Close
1/7/2014
186.39
190.35
186.38
189.71
5,932,300
189.71
1/6/2014
187.15
187.36
185.30
186.00
4,067,800
186.00
1/3/2014
185.83
187.35
185.30
186.64
4,063,200
186.64
1/2/2014
187.21
187.40
185.20
185.53
4,546,500
185.53
12/31/2013
186.49
187.79
186.30
187.57
3,619,700
187.57
12/30/2013
185.32
186.70
184.67
186.41
3,018,600
186.41
12/27/2013
185.84
186.50
184.56
185.08
3,381,600
185.08
12/26/2013
183.56
185.56
183.51
185.35
3,325,700
185.35
Note that the first row returns the column labels, so if you neglect to array-enter a function that expects it, you will typically just get labels without any values.
4. Do I have to type in the Tickers each time?
No, instead of "IBM" in the example above you can just reference a cell, and then put the Ticker in the cell! So assuming you typed IBM into cell A1 you could put this into Cell B1:
=RCHGetYahooHistory(A1,2007,1,3,2007,1,3,,"A",0)
You can also make use of the Excel string concatenation function (&). If this isn't obvious how to use it, Google around.
5. Anything else I need to know up front?
a) The functions were developed in the US, and are somewhat US-centric. They have been used quite successfully by folks throughout the world, but if you aren't in the US there are a few problems you could face. For example, some of the functions assume the decimal character is a period and the thousands delimiter is a comma. To use these functions you'll need to change your "system separators" in Excel. Another issue is that some websites try to be "smart" and use your IP address to determine your PC's location and then re-direct you to a local website.
b) It is possible that your Internet access options could be set to prevent the Add-In functions from working. In Internet Explorer for example you may have to go to Tools, Internet Options, Security, Custom Level button (for the zone that's currently highlighted); Find the 'Access Data Sources Across Domains' option and set to 'Enable'. Click OK to save the settings change.
c) Regardless of what browser you typically use, the Add-Ins make use of Internet Explorer's cache. What this means is if you want to access a site with the Add-Ins that requires a sign on you need to sign on first with Internet Explorer.
6. The Add-Ins have saved me so much time and/or made me a much better investor! How do I pay for them?
The Add-Ins were developed by Randy Harmelink to support his personal investing process and he then made them available for free to the general public. He neither asks for nor accepts payment, all he asks is that you "pay it forward" in any way you'd like. Note that this is therefore a 'hobby' for him, and any time he spends improving them or answering questions is time away from family, friends and whatever else he would like to be doing, so any suggestions of the form "it would be so much better (for me) if you changed this" will typically be met with "Thanks, why don't you try!".
For me, when I first came to this group after searching for a tool to extract prices/data/
Very nice.
Jim
From: smf_addin@yahoogrou
Sent: Wednesday, January 08, 2014 5:05 PM
To: smf_addin@yahoogrou
Subject: [smf_addin] A Modest Proposal for Randy (apologies for the length)
First, like everybody else, thanks for making your work available, and for your seemingly unending supply of patience with Group members. Sometimes I see the questions you get and want to scream.
I see you answer the same questions multiple times per week. I appreciate that you try to answer a few basic questions on the Group About page, that you cover lots of stuff in your Overview.html page and that there is a sort-of FAQ list on the Links section, including the SeekingAlpha "Getting Started" document, but IMO what folks need is a static one page FAQ that says something like the below, preferably with a hyperlink to it on the front of the About page (I don't know if you can have hyperlinksthere but I hope so).
1. What are the "Stock Market Functions" add-in?
They are a set of "add ins" (aka user defined functions) that work in all recent versions of Excel for Windows (they do not work on Excel for Macs) that pull data off Internet web pages and into an Excel spreadsheet. There are some that are "specialized&q
2. How to I use them?
To use them you have to a) Download and install the Add-Ins, b) choose the one(s) you want to use and then c) array enter the formula -- with the add-in function name (prefaced, like all functions, with an = sign) and any parameters -- into a cell of the spreadsheet. Instructions for all this can be found in the Documentation section. So for example:
=RCHGetYahooHistory
will return the adjusted closing price for (ticker symbol) IBM for January 3, 2007 (e.g. 86.04). Because this returns a single number you can just type it in without array-entering and it will work, but most functions have to be array-entered to work.
3. How do I array enter something? (I've been using Excel for years and never heard of it!)
To array-enter a formula in Excel, first highlight the range of cells where you would like the returned data to appear -- the number of rows for the range should be the number of periods of data you are requesting from the function, while the number of columns for the range should be the number of data items you are requesting for each date from the function. Next, enter your formula and then press Ctrl-Shift-Enter. So for example, if you use RCHGetYahooHistory without any parameters it returns the Open, High, Low, Close, Volume and Adjusted Close for the specified Ticker, starting from the last market close and going back however many rows you array-entered. Array entering =RCHGetYahooHistory
Date
Open
High
Low
Close
Volume
Adj Close
1/7/2014
186.39
190.35
186.38
189.71
5,932,300
189.71
1/6/2014
187.15
187.36
185.30
186.00
4,067,800
186.00
1/3/2014
185.83
187.35
185.30
186.64
4,063,200
186.64
1/2/2014
187.21
187.40
185.20
185.53
4,546,500
185.53
12/31/2013
186.49
187.79
186.30
187.57
3,619,700
187.57
12/30/2013
185.32
186.70
184.67
186.41
3,018,600
186.41
12/27/2013
185.84
186.50
184.56
185.08
3,381,600
185.08
12/26/2013
183.56
185.56
183.51
185.35
3,325,700
185.35
Note that the first row returns the column labels, so if you neglect to array-enter a function that expects it, you will typically just get labels without any values.
4. Do I have to type in the Tickers each time?
No, instead of "IBM" in the example above you can just reference a cell, and then put the Ticker in the cell! So assuming you typed IBM into cell A1 you could put this into Cell B1:
=RCHGetYahooHistory
You can also make use of the Excel string concatenation function (&). If this isn't obvious how to use it, Google around.
5. Anything else I need to know up front?
a) The functions were developed in the US, and are somewhat US-centric. They have been used quite successfully by folks throughout the world, but if you aren't in the US there are a few problems you could face. For example, some of the functions assume the decimal character is a period and the thousands delimiter is a comma. To use these functions you'll need to change your "system separators" in Excel. Another issue is that some websites try to be "smart" and use your IP address to determine your PC's location and then re-direct you to a local website.
b) It is possible that your Internet access options could be set to prevent the Add-In functions from working. In Internet Explorer for example you may have to go to Tools, Internet Options, Security, Custom Level button (for the zone that's currently highlighted)
c) Regardless of what browser you typically use, the Add-Ins make use of Internet Explorer'
6. The Add-Ins have saved me so much time and/or made me a much better investor! How do I pay for them?
The Add-Ins were developed by Randy Harmelink to support his personal investing process and he then made them available for free to the general public. He neither asks for nor accepts payment, all he asks is that you "pay it forward" in any way you'd like. Note that this is therefore a 'hobby&#
Wed Jan 8, 2014 5:05 pm (PST) . Posted by:
"Alain Bigio" albibr
I understand you very well. I was once a software support and the most
common answer we would give to our field people was R T F M (read the .....
Manual) pure exhaustion after answering the same question over a 100 times.
Alain
Em quarta-feira, 8 de janeiro de 2014, Randy Harmelink escreveu:
>
>
> Part of the problem is that some of the most frequent questions have the
> same answer -- learn how to use EXCEL.
>
> Being a typical programmer, the thing I hate most is writing up
> documentation, so it gets a short straw. :(
>
> But it doesn't help that many of the questions asked are already answered
> somewhere, either on the front page of the group, the email welcoming
> someone to the group, or in the LINKS area of the group. Even in the
> documentation folder. But people don't take the time to read them. It's
> easier for them, to ask.
>
> I see that on other groups as well. Someone will ask a question that could
> easily have been answered in 30 seconds with a quick visit to Google. Or a
> quick search of the group. Yet they will spend more time writing up a
> message to the group asking the question, and then wait hours, even days,
> for someone to answer.
>
> When I was a professional programmer, I was the guru of the group. You
> don't know how many times someone would come and ask me a question and the
> first thing I would do is pull the Programmer's Guide or Language Reference
> off my shelf, look it up in the index, and then read it through with them.
>
>
>
--
*Alainalain@bigio.net.br <alain@bigio.net.br>*
common answer we would give to our field people was R T F M (read the .....
Manual) pure exhaustion after answering the same question over a 100 times.
Alain
Em quarta-feira, 8 de janeiro de 2014, Randy Harmelink escreveu:
>
>
> Part of the problem is that some of the most frequent questions have the
> same answer -- learn how to use EXCEL.
>
> Being a typical programmer, the thing I hate most is writing up
> documentation, so it gets a short straw. :(
>
> But it doesn't help that many of the questions asked are already answered
> somewhere, either on the front page of the group, the email welcoming
> someone to the group, or in the LINKS area of the group. Even in the
> documentation folder. But people don't take the time to read them. It's
> easier for them, to ask.
>
> I see that on other groups as well. Someone will ask a question that could
> easily have been answered in 30 seconds with a quick visit to Google. Or a
> quick search of the group. Yet they will spend more time writing up a
> message to the group asking the question, and then wait hours, even days,
> for someone to answer.
>
> When I was a professional programmer, I was the guru of the group. You
> don't know how many times someone would come and ask me a question and the
> first thing I would do is pull the Programmer&#
> off my shelf, look it up in the index, and then read it through with them.
>
>
>
--
*Alainalain@
Tidak ada komentar:
Posting Komentar