15 Messages
Digest #2989
Messages
Mon Mar 3, 2014 8:08 am (PST) . Posted by:
myriad58
Thanks for that Randy. Basically, I wanted to use the plugin to build a spreadsheet so I can see the values of my investment accounts and re-balance them if necessary. The GIC stands for a, 'Guaranteed Investment Certificate' which is not a traded stock so the value has to be entered manually and that is why I can't use the add-in.
One more question: now when I enter a GIC value it goes in the correct spot in the array but the VBA code wipes it out every time it subsequently runs again. How can I add another case statement that looks at the Ticker, "GIC" AND the value of the price field and steps over it (i.e., not wiping it out)?
One more question: now when I enter a GIC value it goes in the correct spot in the array but the VBA code wipes it out every time it subsequently runs again. How can I add another case statement that looks at the Ticker, "GIC" AND the value of the price field and steps over it (i.e., not wiping it out)?
Mon Mar 3, 2014 10:59 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Easiest thing to do is make sure GIC is not in the range of ticker symbols
passed.
Otherwise, you could do a VLOOKUP() on the range of ticker symbols, looking
for "GIC", and saving whatever value you find. Then in the loop through the
RCHGetYahooQuotes() results, instead of prompting for the data, fill in the
quotes part of the array with the saved value. If you do this, it might be
easier to put the INPUT() statement there as well, if you need to. Just
save its value. Then, the quotes loop just always sets the quotes to the
saved value, which is either what the list had, or what INPUT() collects.
On Mon, Mar 3, 2014 at 9:08 AM, <myriad58@yahoo.com> wrote:
>
> Thanks for that Randy. Basically, I wanted to use the plugin to build a
> spreadsheet so I can see the values of my investment accounts and
> re-balance them if necessary. The GIC stands for a, 'Guaranteed Investment
> Certificate' which is not a traded stock so the value has to be entered
> manually and that is why I can't use the add-in.
> One more question: now when I enter a GIC value it goes in the correct
> spot in the array but the VBA code wipes it out every time it subsequently
> runs again. How can I add another case statement that looks at the Ticker,
> "GIC" AND the value of the price field and steps over it (i.e., not wiping
> it out)?
>
passed.
Otherwise, you could do a VLOOKUP() on the range of ticker symbols, looking
for "GIC"
RCHGetYahooQuotes(
quotes part of the array with the saved value. If you do this, it might be
easier to put the INPUT() statement there as well, if you need to. Just
save its value. Then, the quotes loop just always sets the quotes to the
saved value, which is either what the list had, or what INPUT() collects.
On Mon, Mar 3, 2014 at 9:08 AM, <myriad58@yahoo.
>
> Thanks for that Randy. Basically, I wanted to use the plugin to build a
> spreadsheet so I can see the values of my investment accounts and
> re-balance them if necessary. The GIC stands for a, 'Guaranteed Investment
> Certificate&#
> manually and that is why I can't use the add-in.
> One more question: now when I enter a GIC value it goes in the correct
> spot in the array but the VBA code wipes it out every time it subsequently
> runs again. How can I add another case statement that looks at the Ticker,
> "GIC" AND the value of the price field and steps over it (i.e., not wiping
> it out)?
>
Mon Mar 3, 2014 6:51 pm (PST) . Posted by:
myriad58
This is getting there:
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:=2)
dRate = RCHGetYahooQuotes("USDCAD=X", "l1")(1, 1)
For i1 = 1 To rTickers.Rows.Count
Select Case True
Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) <= 0
vData(i1, 2) = InputBox(Prompt:="Please enter the value of the GIC", _
Title:="ENTER GIC AMOUNT")
vData(i1, 2) = vData(i1, 2)
Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) >= 0
vData(i1, 2) = rTickers(i1, 3)
Case rTickers(i1, 1) = ""
vData(i1, 1) = ""
vData(i1, 2) = ""
Case Not Right(rTickers(i1, 1), 3) = ".TO"
vData(i1, 2) = dRate * vData(i1, 2)
End Select
Next i1
rTickers.Offset(0, 1).Resize(rTickers.Rows.Count, 2) = vData
End Sub
Option Explicit
Sub Test()
Dim vData As Variant, dRate As Variant, rTickers As Range
Dim i1 As Integer
Set rTickers = Range("
vData = RCHGetYahooQuotes(
dRate = RCHGetYahooQuotes(
For i1 = 1 To rTickers.Rows.
Select Case True
Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) <= 0
vData(i1, 2) = InputBox(Prompt:
Title:="
vData(i1, 2) = vData(i1, 2)
Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) >= 0
vData(i1, 2) = rTickers(i1, 3)
Case rTickers(i1, 1) = ""
vData(i1, 1) = ""
vData(i1, 2) = ""
Case Not Right(rTickers(
vData(i1, 2) = dRate * vData(i1, 2)
End Select
Next i1
rTickers.Offset(
End Sub
Mon Mar 3, 2014 7:28 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
How about automating it? For example, if you use something like this as
your ticker symbol:
GIC=100.1
Then just have the case statement look for "GIC=" in the first four bytes
of the ticker symbol, and use the rest of the ticker symbol as your
"amount" to use for the GIC.
On Mon, Mar 3, 2014 at 7:51 PM, <myriad58@yahoo.com> wrote:
>
> This is getting there:
> 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:=2)
> dRate = RCHGetYahooQuotes("USDCAD=X", "l1")(1, 1)
>
> For i1 = 1 To rTickers.Rows.Count
> Select Case True
> Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) <= 0
> vData(i1, 2) = InputBox(Prompt:="Please enter the value of
> the GIC", _
> Title:="ENTER GIC AMOUNT")
> vData(i1, 2) = vData(i1, 2)
> Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) >= 0
> vData(i1, 2) = rTickers(i1, 3)
> Case rTickers(i1, 1) = ""
> vData(i1, 1) = ""
> vData(i1, 2) = ""
> Case Not Right(rTickers(i1, 1), 3) = ".TO"
> vData(i1, 2) = dRate * vData(i1, 2)
> End Select
>
> Next i1
> rTickers.Offset(0, 1).Resize(rTickers.Rows.Count, 2) = vData
>
> End Sub
>
your ticker symbol:
GIC=100.1
Then just have the case statement look for "GIC=" in the first four bytes
of the ticker symbol, and use the rest of the ticker symbol as your
"amount" to use for the GIC.
On Mon, Mar 3, 2014 at 7:51 PM, <myriad58@yahoo.
>
> This is getting there:
> Option Explicit
> Sub Test()
> Dim vData As Variant, dRate As Variant, rTickers As Range
> Dim i1 As Integer
> Set rTickers = Range("
>
> vData = RCHGetYahooQuotes(
> pDim2:=2)
> dRate = RCHGetYahooQuotes(
>
> For i1 = 1 To rTickers.Rows.
> Select Case True
> Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) <= 0
> vData(i1, 2) = InputBox(Prompt:
> the GIC", _
> Title:="
> vData(i1, 2) = vData(i1, 2)
> Case rTickers(i1, 1) Like "GIC*" And rTickers(i1, 3) >= 0
> vData(i1, 2) = rTickers(i1, 3)
> Case rTickers(i1, 1) = ""
> vData(i1, 1) = ""
> vData(i1, 2) = ""
> Case Not Right(rTickers(
> vData(i1, 2) = dRate * vData(i1, 2)
> End Select
>
> Next i1
> rTickers.Offset(
>
> End Sub
>
Mon Mar 3, 2014 1:58 pm (PST) . Posted by:
gryemann
Thank you Randy!
I had tried placing "v" in cell G7 and when I did G8 displayed "Volume", as expected, however the remaining relevant cells in column G displayed " Unrecognized item ID: V" – So I assumed I was/am doing something wrong.
I would not have done the concatenation correctly with the spaces and other delimiters.
What source does "smfGetOptionQuotes" default to if no source is identified for use?
Thanks for all your help! I'm a newbie to this and though I have spent several hours in the "Files" area I still manage to confuse myself.
Thanks for making all this available.
JBSeattle
I had tried placing "v" in cell G7 and when I did G8 displayed "Volume"
I would not have done the concatenation correctly with the spaces and other delimiters.
What source does "smfGetOptionQ
Thanks for all your help! I'm a newbie to this and though I have spent several hours in the "Files" area I still manage to confuse myself.
Thanks for making all this available.
JBSeattle
Mon Mar 3, 2014 2:10 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Both questions are answered in the function's documentation (which could
use an update):
-- The OX data source doesn't have volume available.
-- The default data source would be Yahoo.
Concatenation is second nature to me these days. You wouldn't believe some
of the gymnastics I've had to do with it. :)
On Mon, Mar 3, 2014 at 2:58 PM, <gryemann@yahoo.com> wrote:
>
>
> I had tried placing "v" in cell G7 and when I did G8 displayed "Volume",
> as expected, however the remaining relevant cells in column G displayed " Unrecognized
> item ID: V" – So I assumed I was/am doing something wrong.
>
> I would not have done the concatenation correctly with the spaces and
> other delimiters.
>
> What source does "smfGetOptionQuotes" default to if no source is
> identified for use?
>
> Thanks for all your help! I'm a newbie to this and though I have spent
> several hours in the "Files" area I still manage to confuse myself.
>
use an update):
-- The OX data source doesn't have volume available.
-- The default data source would be Yahoo.
Concatenation is second nature to me these days. You wouldn't believe some
of the gymnastics I've had to do with it. :)
On Mon, Mar 3, 2014 at 2:58 PM, <gryemann@yahoo.
>
>
> I had tried placing "v" in cell G7 and when I did G8 displayed "Volume"
> as expected, however the remaining relevant cells in column G displayed " Unrecognized
> item ID: V" – So I assumed I was/am doing something wrong.
>
> I would not have done the concatenation correctly with the spaces and
> other delimiters.
>
> What source does "smfGetOptionQ
> identified for use?
>
> Thanks for all your help! I'm a newbie to this and though I have spent
> several hours in the "Files" area I still manage to confuse myself.
>
Mon Mar 3, 2014 3:51 pm (PST) . Posted by:
herkshirebathaway
Hi All,
I know this has been posted somewhere but I could not find the thread using search tool so sorry if this has been covered before.
Just wondering how I can get the RCHGetYahooQuotes function to pull current prices when I press the F9 key?
I read over the documentation in Randy's template but I did not understand the use of the rand() function in cell 203.
Hoping someone can offer some insights,
Thanks for your time guys
I know this has been posted somewhere but I could not find the thread using search tool so sorry if this has been covered before.
Just wondering how I can get the RCHGetYahooQuotes function to pull current prices when I press the F9 key?
I read over the documentation in Randy's template but I did not understand the use of the rand() function in cell 203.
Hoping someone can offer some insights,
Thanks for your time guys
Mon Mar 3, 2014 4:42 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
That template was created before I added the parameter that allows the use
of NOW() to make the function volatile, as described in the documentation.
I've updated the template to use that NOW() technique instead of the RAND()
technique and placed it on the new SMF supplementary files website:
RCHGetYahooQuotes()
Templates<http://ogres-crypt.com/SMF/Templates/#RCHGetYahooQuotes%28%29>
On Mon, Mar 3, 2014 at 4:51 PM, <j.zhen.yap@gmail.com> wrote:
>
> I know this has been posted somewhere but I could not find the thread
> using search tool so sorry if this has been covered before.
>
> Just wondering how I can get the RCHGetYahooQuotes function to pull
> current prices when I press the F9 key?
>
> I read over the documentation in Randy's template but I did not understand
> the use of the rand() function in cell 203.
>
> Hoping someone can offer some insights,
>
>
of NOW() to make the function volatile, as described in the documentation.
I've updated the template to use that NOW() technique instead of the RAND()
technique and placed it on the new SMF supplementary files website:
RCHGetYahooQuotes(
Templates<http://ogres-
On Mon, Mar 3, 2014 at 4:51 PM, <j.zhen.yap@gmail.
>
> I know this has been posted somewhere but I could not find the thread
> using search tool so sorry if this has been covered before.
>
> Just wondering how I can get the RCHGetYahooQuotes function to pull
> current prices when I press the F9 key?
>
> I read over the documentation in Randy's template but I did not understand
> the use of the rand() function in cell 203.
>
> Hoping someone can offer some insights,
>
>
Mon Mar 3, 2014 4:00 pm (PST) . Posted by:
ojalee1
Has anyone been successful in using a library account to access 10 year data from M*? Here is the url is that helps:
http://library.morningstar.com.cod.idm.oclc.org/Stock/financials/income-statement?t=SLP®ion=USA&culture=en-US http://library.morningstar.com.cod.idm.oclc.org/Stock/financials/income-statement?t=SLP®ion=USA&culture=en-US
http://library.
Mon Mar 3, 2014 4:12 pm (PST) . Posted by:
ojalee1
Has anyone used the smf to get 10 year data from M* through a library account? This is the URL if that helps
http://library.morningstar.com.cod.idm.oclc.org/Stock/financials/income-statement?t=SLP®ion=USA&culture=en-US http://library.morningstar.com.cod.idm.oclc.org/Stock/financials/income-statement?t=SLP®ion=USA&culture=en-US
http://library.
Mon Mar 3, 2014 4:44 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Looks like you would need to create an IE security cookie first, by logging
in, either with the EXCEL Web Query dialog (preferred) or with the IE
browser.
On Mon, Mar 3, 2014 at 4:50 PM, <ojalee1@yahoo.com> wrote:
> Has anyone used the smf to get 10 year data from M* through a library
> account? This is the URL if that helps
>
>
> http://library.morningstar.com.cod.idm.oclc.org/Stock/financials/income-statement?t=SLP®ion=USA&culture=en-US
>
in, either with the EXCEL Web Query dialog (preferred) or with the IE
browser.
On Mon, Mar 3, 2014 at 4:50 PM, <ojalee1@yahoo.
> Has anyone used the smf to get 10 year data from M* through a library
> account? This is the URL if that helps
>
>
> http://library.
>
Mon Mar 3, 2014 7:11 pm (PST) . Posted by:
ojalee1
I'm not sure why this is not working. I've tried both of your suggestions, creating a web query and signing in with IE. For the web query, I've clicked Data-> From Web then a web box opens up with a script issue. I'm able to go to M* and from there, regardless if I save the query or hit import, I'm only able to extract 5 years of data.
As for IE, my credentials are saved and I only need to resign in once a day. Thanks for your time.
As for IE, my credentials are saved and I only need to resign in once a day. Thanks for your time.
Mon Mar 3, 2014 6:17 pm (PST) . Posted by:
infinityholdings@ymail.com
Randy
You helped me extract some data from the S&P website a couple weeks ago and need your help from with it one more time. I need help getting the sector allocation for each of the sectors of the S&P 500 from the sector breakdown tab from the following url
http://www.spindices.com/indices/equity/sp-500 http://www.spindices.com/indices/equity/sp-500
Thank you again for all the help.
Adam
You helped me extract some data from the S&P website a couple weeks ago and need your help from with it one more time. I need help getting the sector allocation for each of the sectors of the S&P 500 from the sector breakdown tab from the following url
http://www.spindice
Thank you again for all the help.
Adam
Mon Mar 3, 2014 7:22 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Not a trivial extraction, since it's buried within JavaScript code. Try
something like this to get the first one:
=smfstrExtr(smfWord(smfGetTagContent("
http://us.spindices.com/indices/equity/sp-500","script",-1,"
(Sector)"),2,"sectorIndexId"),"sectorIndexName"":"""," (Sector)")
=smfConvertData(smfstrExtr(smfWord(smfGetTagContent("
http://us.spindices.com/indices/equity/sp-500","script",-1,"
(Sector)"),2,"sectorIndexId"),"marketCapitalPercentage"":",","))
You'll need to increment the "2" parameter in those formulas to get the
additional sectors.
On Mon, Mar 3, 2014 at 7:17 PM, <coons_adam@yahoo.com> wrote:
>
> You helped me extract some data from the S&P website a couple weeks ago
> and need your help from with it one more time. I need help getting the
> sector allocation for each of the sectors of the S&P 500 from the sector
> breakdown tab from the following url
>
> http://www.spindices.com/indices/equity/sp-500
>
something like this to get the first one:
=smfstrExtr(
http://us.spindices
(Sector)"
=smfConvertData(
http://us.spindices
(Sector)"
You'll need to increment the "2" parameter in those formulas to get the
additional sectors.
On Mon, Mar 3, 2014 at 7:17 PM, <coons_adam@yahoo.
>
> You helped me extract some data from the S&P website a couple weeks ago
> and need your help from with it one more time. I need help getting the
> sector allocation for each of the sectors of the S&P 500 from the sector
> breakdown tab from the following url
>
> http://www.spindice
>
Tidak ada komentar:
Posting Komentar