Messages In This Digest (5 Messages)
- 2.1.
- Re: Another way to use the SMF formulas to retrieve data From: Randy H
- 3a.
- Question about RCHGetElementNumber? From: cocoaacc
- 3b.
- Re: Question about RCHGetElementNumber? From: Randy Harmelink
Messages
- 1a.
-
New
Posted by: "wine.nate" wine.nate@yahoo.com wine.nate
Mon Dec 26, 2011 12:28 pm (PST)
Hey guys,
I'm new to the group. I've downloaded and installed the add-in and checked out a few sample spreadsheets (many of which are very impressive) but when I change the ticker on most of them the data doesn't change. Also, what would be a good reference guys for using the add-in. I'm pretty unfamiliar with the whole process.
Thanks,
-Nate
- 1b.
-
Re: New
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Dec 26, 2011 12:37 pm (PST)
The two best places would be the individual documentation for the various
functions (because the add-in almost entirely just user defined functions),
and the "Links" area of the group. The "Location error" item in the "Links"
area of the group would probably address your unchanging data when a new
ticker is entered.
On Mon, Dec 26, 2011 at 11:24 AM, wine.nate <wine.nate@yahoo.com > wrote:
>
> I'm new to the group. I've downloaded and installed the add-in and checked
> out a few sample spreadsheets (many of which are very impressive) but when
> I change the ticker on most of them the data doesn't change. Also, what
> would be a good reference guys for using the add-in. I'm pretty unfamiliar
> with the whole process.
>
- 2.1.
-
Re: Another way to use the SMF formulas to retrieve data
Posted by: "Randy H" rharmelink@gmail.com rharmelink
Mon Dec 26, 2011 12:42 pm (PST)
My only guess would be that maybe the one long line is wrapped onto
several lines, in your code? Otherwise, where the compiler points to in
your code should be the clue as to what is wrong...
On Mon, Dec 26, 2011 at 1:22 PM, cs.holdings <c.s.holdings@...
<mailto:c.s.holdings@sbcglobal. > > wrote:net
> Hi Randy
> Ive tried to use the enclosed per your example in a module and it
returns a Complier error: Expected End Sub
>
> Thoughts?
--- In smf_addin@yahoogroups.com <mailto:smf_addin@yahoogroups.com > ,
"Randy Harmelink" <rharmelink@...> wrote:
>
> Here's a different way to use a VBA method to use the SMF formulas.
In
> this example, I will be retrieving history data for 3 different
> companies, although it can EASILY be adjusted for more. Step by step:
>
> 1. Open a new workbook
>
> 2. Put Ticker symbols in cells C4 thru E4. Also, add one of them to
> cell B4. For example:
> B C D E 4 IBM IBM MMM JNJ 5 3. Next, hit alt-F11 to
open
> up the VBA editor.
>
> 4. Insert a new module in the workbook that was created for this
> example.
>
> 5. Use menu option > Tools > References and check the box in front of
> the "RCH_Stock_Market_Functions" reference library.
>
> 6. Insert this code into the new module:
>
> Sub Test()
> Dim sItems As String
> Dim oCell As Range
> sItems = "D"
> For Each oCell In [B4:Z4]
> If oCell.Value2 = "" Then Exit For
> Range(oCell.Offset(1, 0), oCell.Offset( 1000, 0)) =
> RCHGetYahooHistory(oCell.Value2, pResort:=1, pItems:=sItems,
> pDim1:=1000, pDim2:=1)
> sItems = "A"
> Next oCell
> End Sub
>
> 7. Run the Test() subroutine. On my machine, this took less than 10
> seconds.
>
> When you go back to the workbook, you should see something like:
> B C D E 4 IBM IBM MMM JNJ 5 Date Adj. Close* Adj. Close* Adj.
> Close* 6 11/26/2002 $81.89 $58.05 $52.36 7 11/27/2002 $84.43
> $60.01 $53.59 8 11/29/2002 $83.68 $60.11 $52.92 9 12/2/2002
> $84.05 $59.42 $51.70 10 12/3/2002 $82.03 $59.35 $51.82 11
> 12/4/2002 $80.57 $59.26 $52.66 12 12/5/2002 $79.97 $58.29
$51.86
> Now, you could easily have done the same thing with a formula in one
> column and copied it from one column to the next. However, I've been
> using this to get sets of data VALUES without having to array-enter
the
> formula or convert everything to values (to prevent recalculation when
I
> change other things on the worksheet).
>
> In general, if I want other symbols, it's fast enough to run again
after
> changing the ticker symbols.
>
> Now what does the VBA code do? Here's what:
>
> > sItems = "D"
>
> ...is used to get dates for the first column. Note that after the
first
> RCHGetYahooHistory() function is executed, this is changed to an "A"
so
> that all future invocations of the function get the "Adjusted Close".
>
> > For Each oCell In [B4:Z4]
>
> ...just iterates through each cell of the worksheet from cell B4
through
> Z4.
>
> > If oCell.Value2 = "" Then Exit For
>
> ...causes the loop to be exited on the first cell in the range that is
> empty. This means you don't have to change the VBA code to do 6
> companies or 10 companies -- it automatically does as many as you have
> (up to Z4, anyway). If you need more, you can increase the Z4 to a
> later column. I'm not sure I would recommend doing TOO many though.
>
> > Range(oCell.Offset(1, 0), oCell.Offset( 1000, 0))
> =RCHGetYahooHistory(oCell.Value2, pResort:=1,
> pItems:=sItems,pDim1:=1000, pDim2:=1)
>
> ...is the workhorse of the routine. Piece by piece:
>
> > Range(oCell.Offset(1, 0), oCell.Offset( 1000, 0))
>
> ...say to fill a range starting 1 row below the iterated cell (e.g.
"B4"
> or "C4") and ending 1000 rows below that iterated cell. In essence, a
> 1000-row by 1-column range set up 1 row below the iterated cell.
>
> > RCHGetYahooHistory(oCell.Value2, pResort:=1,
> pItems:=sItems,pDim1:=1000, pDim2:=1)
>
> ...is the call to the RCHGetYahooHistory() function. "oCell.Value2"
is
> used to pass the ticker symbol from the iterated cell (e.g. "B4" or
> "C4"). "pResort:=1" tells the function to sort the returned data in
> ascending date sequence. "pItems:=sItems" tells the function what
data
> to return -- in this case, "D" (i.e. "Dates") on the first call and
"A"
> (i.e. "Adjusted Close") on all other calls. "pDim1:=1000, pDim2:=1"
> tell the function what size array to return -- 1000 rows by 1 column.
>
- 3a.
-
Question about RCHGetElementNumber?
Posted by: "cocoaacc" cocoaacc@yahoo.com.hk cocoaacc
Mon Dec 26, 2011 7:38 pm (PST)
Hi Randy Harmelink,
I got a Error display for the following functions:
Average Volume (3 month)
=RCHGetElementNumber(GOOG,979)
Average Volume (10 day)
=RCHGetElementNumber(GOOG,980)
But the data shows up by looking at Internet Explore.
http://finance.yahoo.com/ q/ks?s=goog
Moreover, is Yahoo provide the Element Number for the following term of stock?
(Name, Last Price, Percent Change, and Volumn)
Thank you very much!
I love SMF function you created.
Will
- 3b.
-
Re: Question about RCHGetElementNumber?
Posted by: "Randy Harmelink" rharmelink@gmail.com rharmelink
Mon Dec 26, 2011 7:48 pm (PST)
Not sure what to tell you -- both of those elements are working fine here.
However, your cited examples would not work, because you have GOOG as a
named cell reference, not a string literal (i.e. "GOOG").
For the additional data items you're asking about, you should use the
RCHGetYahooQuotes() function.
On Mon, Dec 26, 2011 at 8:38 PM, cocoaacc <cocoaacc@yahoo.com.hk > wrote:
>
> I got a Error display for the following functions:
> Average Volume (3 month)
> =RCHGetElementNumber(GOOG,979)
> Average Volume (10 day)
> =RCHGetElementNumber(GOOG,980)
>
> But the data shows up by looking at Internet Explore.
> http://finance.yahoo.com/ q/ks?s=goog
>
> Moreover, is Yahoo provide the Element Number for the following term of
> stock?
> (Name, Last Price, Percent Change, and Volumn)
>
Need to Reply?
Click one of the "Reply" links to respond to a specific message in the Daily Digest.
MARKETPLACE
Change settings via the Web (Yahoo! ID required)
Change settings via email: Switch delivery to Individual | Switch format to Traditional
Visit Your Group | Yahoo! Groups Terms of Use | Unsubscribe
Tidak ada komentar:
Posting Komentar