6 Messages
Digest #2988
Messages
Sun Mar 2, 2014 2:08 pm (PST) . Posted by:
myriad58
First of all I would like to thank Randy for an amazing add-in, it is the perfect tool for my needs. My problem is basically lack of skill with VBA array functions and after many hours of trying to figure it out on my own I am turning to the group for help.
I want to use the 'RCHGetYahooQuotes' function to return the name of the stock & the current price and ultimately to convert the US price to CAD (if the ticker symbol does not contain the string ".*TO*"), though I was unable to figure out how to multiply the value of the array items after they are returned. I figured out a work-around by adding another column to the worksheet and did my calculation after the price was returned.
I can get the price and the name to work but in the list of symbols I occasionally want to add a GIC and have a message box pop up and be able to enter the GIC value manually and in subsequent updates have the VBA loop through the array and ignore the manually entered data.
I have attached a sample of what I am trying to do. Maybe the things that seem so simple ultimately turn out to be the most complicated and it is not possible to do this in VBA.
Regards,
Rick
I want to use the 'RCHGetYahooQuo
I can get the price and the name to work but in the list of symbols I occasionally want to add a GIC and have a message box pop up and be able to enter the GIC value manually and in subsequent updates have the VBA loop through the array and ignore the manually entered data.
I have attached a sample of what I am trying to do. Maybe the things that seem so simple ultimately turn out to be the most complicated and it is not possible to do this in VBA.
Regards,
Rick
Attachment(s) from
1 of 1 File(s)
Sun Mar 2, 2014 3:53 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I'm not sure I understand the GIC processing at all, since I don't even
know what GIC is, but here's what I ended up with:
Option Explicit
Sub Test()
Dim vData As Variant, dRate As Variant, rTickers As Range
Dim i1 As Integer
Set rTickers = Range("B14:B50")
vData = RCHGetYahooQuotes(rTickers, "nl1l1", pDim1:=rTickers.Rows.Count,
pDim2:=3)
dRate = RCHGetYahooQuotes("USDCAD=X", "l1")(1, 1)
For i1 = 1 To rTickers.Rows.Count
Select Case True
Case Right(rTickers(i1, 1), 3) = ".TO"
vData(i1, 3) = dRate * vData(i1, 3)
Case rTickers(i1, 1) = "GIC"
vData(i1, 2) = InputBox(Prompt:="Please enter the value of the
GIC", _
Title:="ENTER GIC AMOUNT")
vData(i1, 3) = vData(i1, 2)
End Select
Next i1
rTickers.Offset(0, 1).Resize(rTickers.Rows.Count, 3) = vData
End Sub
Two key processing changes, for speed considerations:
-- One RCHGetYahooQuotes() function is issued for the list of ticker
symbols. This means only one Internet access, instead of two per ticker
symbol.
-- The entire array is output to the worksheet in one statement. Then it
doesn't have to interact with the worksheet for each item.
On Sun, Mar 2, 2014 at 1:26 PM, <myriad58@yahoo.com> wrote:
> [Attachment(s) <#14484d7877c7aee2_TopText> from myriad58@yahoo.comincluded below]
>
> First of all I would like to thank Randy for an amazing add-in, it is the
> perfect tool for my needs. My problem is basically lack of skill with VBA
> array functions and after many hours of trying to figure it out on my own I
> am turning to the group for help.
> I want to use the 'RCHGetYahooQuotes' function to return the name of the
> stock & the current price and ultimately to convert the US price to CAD (if
> the ticker symbol does not contain the string ".*TO*"), though I was unable
> to figure out how to multiply the value of the array items after they are
> returned. I figured out a work-around by adding another column to the
> worksheet and did my calculation after the price was returned.
> I can get the price and the name to work but in the list of symbols I
> occasionally want to add a GIC and have a message box pop up and be able to
> enter the GIC value manually and in subsequent updates have the VBA loop
> through the array and ignore the manually entered data.
> I have attached a sample of what I am trying to do. Maybe the things that
> seem so simple ultimately turn out to be the most complicated and it is not
> possible to do this in VBA.
>
know what GIC is, but here's what I ended up with:
Option Explicit
Sub Test()
Dim vData As Variant, dRate As Variant, rTickers As Range
Dim i1 As Integer
Set rTickers = Range("
vData = RCHGetYahooQuotes(
pDim2:=3)
dRate = RCHGetYahooQuotes(
For i1 = 1 To rTickers.Rows.
Select Case True
Case Right(rTickers(
vData(i1, 3) = dRate * vData(i1, 3)
Case rTickers(i1, 1) = "GIC"
vData(i1, 2) = InputBox(Prompt:
GIC", _
Title:="
vData(i1, 3) = vData(i1, 2)
End Select
Next i1
rTickers.Offset(
End Sub
Two key processing changes, for speed considerations:
-- One RCHGetYahooQuotes(
symbols. This means only one Internet access, instead of two per ticker
symbol.
-- The entire array is output to the worksheet in one statement. Then it
doesn't have to interact with the worksheet for each item.
On Sun, Mar 2, 2014 at 1:26 PM, <myriad58@yahoo.
> [Attachment(
>
> First of all I would like to thank Randy for an amazing add-in, it is the
> perfect tool for my needs. My problem is basically lack of skill with VBA
> array functions and after many hours of trying to figure it out on my own I
> am turning to the group for help.
> I want to use the 'RCHGetYahooQuo
> stock & the current price and ultimately to convert the US price to CAD (if
> the ticker symbol does not contain the string ".*TO*"
> to figure out how to multiply the value of the array items after they are
> returned. I figured out a work-around by adding another column to the
> worksheet and did my calculation after the price was returned.
> I can get the price and the name to work but in the list of symbols I
> occasionally want to add a GIC and have a message box pop up and be able to
> enter the GIC value manually and in subsequent updates have the VBA loop
> through the array and ignore the manually entered data.
> I have attached a sample of what I am trying to do. Maybe the things that
> seem so simple ultimately turn out to be the most complicated and it is not
> possible to do this in VBA.
>
Sun Mar 2, 2014 10:05 pm (PST) . Posted by:
"vijay gangrade" vijay_gangrade
Hello
This plug in is not working in my computer.
I have tried it in all O/S but always it gives an error
Please help me
Thanks
This plug in is not working in my computer.
I have tried it in all O/S but always it gives an error
Please help me
Thanks
Sun Mar 2, 2014 10:10 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
What specifically are you trying that gives an error?
On Sun, Mar 2, 2014 at 11:00 PM, vijay gangrade <vijay_gangrade@yahoo.com>wrote:
>
> This plug in is not working in my computer.
> I have tried it in all O/S but always it gives an error
> Please help me
>
>
On Sun, Mar 2, 2014 at 11:00 PM, vijay gangrade <vijay_gangrade@
>
> This plug in is not working in my computer.
> I have tried it in all O/S but always it gives an error
> Please help me
>
>
Sun Mar 2, 2014 10:20 pm (PST) . Posted by:
"vijay gangrade" vijay_gangrade
I was just trying to get data for a particular company using this .Xls
smfGetAdvFNElement-Template-All-Line-Items
But after giving the name of company is gives an errors ,i think it is unable to find the comp name or a link .
On Monday, March 3, 2014 11:41 AM, Randy Harmelink <rharmelink@gmail.com> wrote:
What specifically are you trying that gives an error?
On Sun, Mar 2, 2014 at 11:00 PM, vijay gangrade <vijay_gangrade@yahoo.com> wrote:
>
>This plug in is not working in my computer.
>I have tried it in all O/S but always it gives an error
>
>Please help me
>
smfGetAdvFNElement-
But after giving the name of company is gives an errors ,i think it is unable to find the comp name or a link .
On Monday, March 3, 2014 11:41 AM, Randy Harmelink <rharmelink@gmail.
What specifically are you trying that gives an error?
On Sun, Mar 2, 2014 at 11:00 PM, vijay gangrade <vijay_gangrade@
>
>This plug in is not working in my computer.
>I have tried it in all O/S but always it gives an error
>
>Please help me
>
Sun Mar 2, 2014 10:53 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
OK. AdvFN is a little finicky about access. First, what do you get with:
=RCHGetElementNumber("Version")
There is a change you may need to make to your Internet Options in order to
access AdvFN. See the LINKS area of the group for information on that.
Lastly, if you're not in the United States, AdvFN may be directing you to a
different regional website. If you go to your browser, do you get
redirected if you use this URL address:
http://www.advfn.com/exchanges/NYSE/MMM/stock-price
If that redirects you to a URL with something other than "www" in it, you
should exit EXCEL and create an smf-AdvFN-prefix.txt file in the folder
where the add-in exists (just use Notepad), and put the characters in that
file that you see instead of the "www".
On Sun, Mar 2, 2014 at 11:17 PM, vijay gangrade <vijay_gangrade@yahoo.com>wrote:
>
> I was just trying to get data for a particular company using this .Xls
> smfGetAdvFNElement-Template-All-Line-Items
>
> But after giving the name of company is gives an errors ,i think it is
> unable to find the comp name or a link .
>
>
=RCHGetElementNumbe
There is a change you may need to make to your Internet Options in order to
access AdvFN. See the LINKS area of the group for information on that.
Lastly, if you're not in the United States, AdvFN may be directing you to a
different regional website. If you go to your browser, do you get
redirected if you use this URL address:
http://www.advfn.
If that redirects you to a URL with something other than "www" in it, you
should exit EXCEL and create an smf-AdvFN-prefix.
where the add-in exists (just use Notepad), and put the characters in that
file that you see instead of the "www"
On Sun, Mar 2, 2014 at 11:17 PM, vijay gangrade <vijay_gangrade@
>
> I was just trying to get data for a particular company using this .Xls
> smfGetAdvFNElement-
>
> But after giving the name of company is gives an errors ,i think it is
> unable to find the comp name or a link .
>
>
Tidak ada komentar:
Posting Komentar