15 New Messages
Digest #2448
2a
Re: Problem getting data from either Advfn and/or element numbers >4 by "jeff.mcclelland" jeff.mcclelland
Messages
Thu Dec 27, 2012 4:59 am (PST) . Posted by:
"jeroen.michiels@ymail.com" jeroen.michiels@ymail.com
Dear Patrick,
your fix did partly solve my problem. I changed the code to
sURL = "http://" & kAdvFNPrefix & ".advfn.com/fr/p.php?pid=financials" & "&symbol=" & sTicker & sLabel1
but did not manage to get different date, however, I went into the control panel menu ==> change keyboards or other input methods ==> Formats ==> Additional Settings and changed the seperators for the entire system as shown in the image below:
http://i49.tinypic.com/2sbprw6.png
When I apply these settings the date grab works perfect. I don't know whether the changed URL has something to do with this..
Thanks guys! Love this add-in
Happy investing,
Jeroen
--- In smf_addin@yahoogroups.com , "Patrick" <patrick.van.beesen@...> wrote:
>
> Hi Jereoen,
>
> I've the same problem. There is some change in ADVFN,
> the place of the national AdvFNPrefix change for the foreigner.
> I've adapt the code in the Visual Basic module
> smfGetADVFNElement.
>
> FROM
>
> sURL ="http://" <http://> sAdvFNPrefix
> ".advfn.com/p.php?pid=financials" "=" sTicker sLabel1
>
> INTO
> sURL ="http://www.advfn.com/ <http://www.advfn.com/ >
> fr/p.php?pid=financials" "=" sTicker sLabel1
>
> Patrick.
> --- In smf_addin@yahoogroups.com , "jeroen.michiels@" wrote:
> >
> > Dear all,
> >
> > in addition to managing my capital efficiently I also like to manage
> my time in the most optimal way, I therefore have a small question
> concerning the data gathering that uses the AdvFN plugin before starting
> my own extensive search.
> >
> > In contrary to most posts concerning this subject I am able to get the
> data in Excel, however I encounter some issues with the . or , separator
> causing some of the data to not be easily manipulative.
> >
> > I think this is caused by an Excel (or Windows language) issue, I am
> European based and I think the separators are messing up the numbers.
> >
> > For instance:
> > Turnover: 156,508.0 translates as 156.51 (Billion)
> > Acquisitions: -350.0 translates as -3,500.00 (Million)
> >
> > Which means that when I subtract acquisitions from turnover (I know,
> the example is poorly chosen) I arrive at 2,843.49 instead of
> 155650000000. (155.65 billion)
> >
> > Going into the Excel options ==> Advanced ==> Separators I changed the
> following
> > Decimal Separator: .
> > Thousands Separator: ,
> >
> > Unfortunately sometimes easy solutions are too easy, and the above
> quick fix was definitely too easy as is does not work.
> >
> > It's strange though, when I now type in a number it shows up as
> 155,000,000.00 using the correct seperators.
> >
> > Anyone who knows how I can fix this?
> >
> > I am running Windows 7, Excel 2007 and am grabbing my data from AdvFN
> (http://www.advfn.com/fr/p.php?pid=financials=start_date==NASDAQ%3AAAPL=\
> annual_reports=NASDAQ%3AAAPL_date=19)
> >
> > Thanks in advance,
> >
> > good investing!
> >
>
your fix did partly solve my problem. I changed the code to
sURL = "http://" & kAdvFNPrefix & ".advfn.com/
but did not manage to get different date, however, I went into the control panel menu ==> change keyboards or other input methods ==> Formats ==> Additional Settings and changed the seperators for the entire system as shown in the image below:
http://i49.tinypic.
When I apply these settings the date grab works perfect. I don't know whether the changed URL has something to do with this..
Thanks guys! Love this add-in
Happy investing,
Jeroen
--- In smf_addin@yahoogrou
>
> Hi Jereoen,
>
> I've the same problem. There is some change in ADVFN,
> the place of the national AdvFNPrefix change for the foreigner.
> I've adapt the code in the Visual Basic module
> smfGetADVFNElement.
>
> FROM
>
> sURL ="http://" <http://> sAdvFNPrefix
> ".advfn.com/
>
> INTO
> sURL ="http://www.advfn.
> fr/p.php?pid=
>
> Patrick.
> --- In smf_addin@yahoogrou
> >
> > Dear all,
> >
> > in addition to managing my capital efficiently I also like to manage
> my time in the most optimal way, I therefore have a small question
> concerning the data gathering that uses the AdvFN plugin before starting
> my own extensive search.
> >
> > In contrary to most posts concerning this subject I am able to get the
> data in Excel, however I encounter some issues with the . or , separator
> causing some of the data to not be easily manipulative.
> >
> > I think this is caused by an Excel (or Windows language) issue, I am
> European based and I think the separators are messing up the numbers.
> >
> > For instance:
> > Turnover: 156,508.0 translates as 156.51 (Billion)
> > Acquisitions: -350.0 translates as -3,500.00 (Million)
> >
> > Which means that when I subtract acquisitions from turnover (I know,
> the example is poorly chosen) I arrive at 2,843.49 instead of
> 155650000000. (155.65 billion)
> >
> > Going into the Excel options ==> Advanced ==> Separators I changed the
> following
> > Decimal Separator: .
> > Thousands Separator: ,
> >
> > Unfortunately sometimes easy solutions are too easy, and the above
> quick fix was definitely too easy as is does not work.
> >
> > It's strange though, when I now type in a number it shows up as
> 155,000,000.
> >
> > Anyone who knows how I can fix this?
> >
> > I am running Windows 7, Excel 2007 and am grabbing my data from AdvFN
> (http://www.advfn.
> >
> > Thanks in advance,
> >
> > good investing!
> >
>
Thu Dec 27, 2012 5:49 am (PST) . Posted by:
"jeff.mcclelland" jeff.mcclelland
I can confirm that changing the IE settings as described worked for me for Google as a source. For instance, =RCHGetElementNumber("AAPL",2742) now works. I'm using Win8 & Excel 2013. Thanks!
--- In smf_addin@yahoogroups.com , "jsawyermib" <jsawyermib@...> wrote:
>
> Hi, I am a new user of the add-in, but I can only retrieve data up to element number c. 4660 - ie not from Advfn.
>
> I am using the add-in "RCH_Stock_Market_Functions-2.1.2012.07.12" which I have down downloaded from the "Works-in-Progress" directory of this forum and which I assume is the latest version. The add-in is saved in my "c:/Program Files/SMF Add-In/", as recommended and I am running excel 2010.
>
> Functions such as "smfGetADVFNElement(x...)" are not working, but the add-in works when I use functions such as "RCHGetElementNumber(Ticker,25)" [MSN data on last price] on Element numbers up to around 4660 [ie including MSN, Yahoo and GOOGLE), but not on the higher element numbers that include AdvFN and MorningStar.
>
> If the data is coming in OK for the lower element numbers, I assume that I have loaded the add-in correctly, but perhaps I am using an old version.
>
> Anyone got any ideas or solution so that I can access AdvFN?
>
> JS
>
--- In smf_addin@yahoogrou
>
> Hi, I am a new user of the add-in, but I can only retrieve data up to element number c. 4660 - ie not from Advfn.
>
> I am using the add-in "RCH_Stock_Market_
>
> Functions such as "smfGetADVFNElement
>
> If the data is coming in OK for the lower element numbers, I assume that I have loaded the add-in correctly, but perhaps I am using an old version.
>
> Anyone got any ideas or solution so that I can access AdvFN?
>
> JS
>
Thu Dec 27, 2012 7:49 am (PST) . Posted by:
"franksemar" franksemar
Running RCHGetYahooQuotes fine with stock and index funds for last price, date and time. When I put in "m3" for 50 day moving average, I good got values for the stocks, but for the index funds I got "N/A". Same happened with "m8" for % change in 50 day average.
index fund symbols I tried: PTTRX, FIGRX, HAINX
enviroment: Windows 7, Excel 2010, IE 9.0
index fund symbols I tried: PTTRX, FIGRX, HAINX
enviroment: Windows 7, Excel 2010, IE 9.0
Thu Dec 27, 2012 8:00 am (PST) . Posted by:
"Randy Harmelink" rharmelink
For mutual funds, I think the only alternative is to do it the slow way and
compute each one yourself, with something like:
=AVERAGE(RCHGetYahooHistory("FIGRX",,,,,,,,"A",0,,,50,1))
Note that Yahoo's 50-day and 200-day moving averages are based on calendar
days, not trading days.
On Thu, Dec 27, 2012 at 8:49 AM, franksemar <franksemar@yahoo.com > wrote:
> Running RCHGetYahooQuotes fine with stock and index funds for last price,
> date and time. When I put in "m3" for 50 day moving average, I good got
> values for the stocks, but for the index funds I got "N/A". Same happened
> with "m8" for % change in 50 day average.
>
> index fund symbols I tried: PTTRX, FIGRX, HAINX
>
compute each one yourself, with something like:
=AVERAGE(RCHGetYaho
Note that Yahoo's 50-day and 200-day moving averages are based on calendar
days, not trading days.
On Thu, Dec 27, 2012 at 8:49 AM, franksemar <franksemar@yahoo.
> Running RCHGetYahooQuotes fine with stock and index funds for last price,
> date and time. When I put in "m3" for 50 day moving average, I good got
> values for the stocks, but for the index funds I got "N/A". Same happened
> with "m8" for % change in 50 day average.
>
> index fund symbols I tried: PTTRX, FIGRX, HAINX
>
Thu Dec 27, 2012 8:48 am (PST) . Posted by:
"Jacob Jose" pepecan47
Randy, I use:
=AVERAGE(RCHGetYahooHistory(A3,,,,,,,,"C",0,1,,50,1))
Is the result the same as the formula below?
Regards,
Jose L. Jacob
________________________________
From: Randy Harmelink <rharmelink@gmail.com >
To: smf_addin@yahoogroups.com
Sent: Thursday, December 27, 2012 11:00:00 AM
Subject: Re: [smf_addin] Looking for 50 Day Avg for index funds
For mutual funds, I think the only alternative is to do it the slow way and compute each one yourself, with something like:
=AVERAGE(RCHGetYahooHistory("FIGRX",,,,,,,,"A",0,,,50,1))
Note that Yahoo's 50-day and 200-day moving averages are based on calendar days, not trading days.
On Thu, Dec 27, 2012 at 8:49 AM, franksemar <franksemar@yahoo.com > wrote:
Running RCHGetYahooQuotes fine with stock and index funds for last price, date and time. When I put in "m3" for 50 day moving average, I good got values for the stocks, but for the index funds I got "N/A". Same happened with "m8" for % change in 50 day average.
>
>index fund symbols I tried: PTTRX, FIGRX, HAINX
>
=AVERAGE(RCHGetYaho
Is the result the same as the formula below?
Regards,
Jose L. Jacob
____________
From: Randy Harmelink <rharmelink@gmail.
To: smf_addin@yahoogrou
Sent: Thursday, December 27, 2012 11:00:00 AM
Subject: Re: [smf_addin] Looking for 50 Day Avg for index funds
For mutual funds, I think the only alternative is to do it the slow way and compute each one yourself, with something like:
=AVERAGE(RCHGetYaho
Note that Yahoo's 50-day and 200-day moving averages are based on calendar days, not trading days.
On Thu, Dec 27, 2012 at 8:49 AM, franksemar <franksemar@yahoo.
Running RCHGetYahooQuotes fine with stock and index funds for last price, date and time. When I put in "m3" for 50 day moving average, I good got values for the stocks, but for the index funds I got "N/A". Same happened with "m8" for % change in 50 day average.
>
>index fund symbols I tried: PTTRX, FIGRX, HAINX
>
Thu Dec 27, 2012 8:52 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Should come out to the same numbers. Your formula is using closing prices
that have been adjusted. I am using the raw adjusted closing prices. Mine
might run a nanosecond faster, because it doesn't need to adjust the 50
closing prices. :)
On Thu, Dec 27, 2012 at 9:48 AM, Jacob Jose <pepecan47@yahoo.ca > wrote:
>
> Randy, I use:
>
> =AVERAGE(RCHGetYahooHistory(A3,,,,,,,,"C",0,1,,50,1))
>
> Is the result the same as the formula below?
> **
> =AVERAGE(RCHGetYahooHistory("FIGRX",,,,,,,,"A",0,,,50,1))
>
that have been adjusted. I am using the raw adjusted closing prices. Mine
might run a nanosecond faster, because it doesn't need to adjust the 50
closing prices. :)
On Thu, Dec 27, 2012 at 9:48 AM, Jacob Jose <pepecan47@yahoo.
>
> Randy, I use:
>
> =AVERAGE(RCHGetYaho
>
> Is the result the same as the formula below?
> **
> =AVERAGE(RCHGetYaho
>
Thu Dec 27, 2012 9:02 am (PST) . Posted by:
"franksemar" franksemar
Thank you very much and Happy New Year
--- In smf_addin@yahoogrou
>
> Should come out to the same numbers. Your formula is using closing prices
> that have been adjusted. I am using the raw adjusted closing prices. Mine
> might run a nanosecond faster, because it doesn't need to adjust the 50
> closing prices. :)
>
> On Thu, Dec 27, 2012 at 9:48 AM, Jacob Jose <pepecan47@.
>
> >
> > Randy, I use:
> >
> > =AVERAGE(RCHGetYaho
> >
> > Is the result the same as the formula below?
> > **
> > =AVERAGE(RCHGetYaho
> >
>
Thu Dec 27, 2012 9:05 am (PST) . Posted by:
"Kermit W. Prather" kermitpra
Thanks, I was thinking of downloading a movie list with descriptions. I hate surfing thru their listings. Way to many bad movies.
I̢۪ll give these examples a try and maybe expand on them.
Thanks again, Hope your Christmas was wonderful.
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Wednesday, December 26, 2012 11:46 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: Data Sources and How to Extract
I'm not sure just what you're looking for from NetFlix. My workbooks are pretty specialized, and sometimes are built on the fly for the specific information I'm after, but I do use the following formulas to get info on individual movies:
* Genre:
=SUBSTITUTE(smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"a",1,"Genres:")," Movies","")
* Rating:
=IFERROR(0+smfStrExtr("|"&smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"span",1,"ratings:"),"|"," star"),3)
* Duration:
=smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"span",-1,"class=""duration""")
* Year:
=VALUE(smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"span",-1,"class=""year"""))
* Title:
=smfStrExtr(smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"title",1)&"|",": ","|")
* Synopsis:
=smfGetTagContent("http://dvd.netflix.com/Movie/ "&$B6,"p",-1,"class=""synopsis")
...where $B6 contains the movie numeric ID (e.g. 70120085). I used these to add entries to an "offline" queue I keep. You do need to log in to your account with the EXCEL Web Query process (preferred) or IE, from this URL:
* http://www.netflix.com
If you log in using their DVD or streaming web site, the functions may not work. I've had sporadic results.
On Wed, Dec 26, 2012 at 8:29 AM, Kermit W. Prather <kermitp@tampabay.rr.com > wrote:
Randy, I̢۪d be interested in the Netflix template. If you don̢۪t mind uploading it.
Kermit
I̢۪ll give these examples a try and maybe expand on them.
Thanks again, Hope your Christmas was wonderful.
Kermit
From: smf_addin@yahoogrou
Sent: Wednesday, December 26, 2012 11:46 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Re: Data Sources and How to Extract
I'm not sure just what you're looking for from NetFlix. My workbooks are pretty specialized, and sometimes are built on the fly for the specific information I'm after, but I do use the following formulas to get info on individual movies:
* Genre:
=SUBSTITUTE(
* Rating:
=IFERROR(0+smfStrEx
* Duration:
=smfGetTagContent(
* Year:
=VALUE(smfGetTagCon
* Title:
=smfStrExtr(
* Synopsis:
=smfGetTagContent(
...where $B6 contains the movie numeric ID (e.g. 70120085). I used these to add entries to an "offline" queue I keep. You do need to log in to your account with the EXCEL Web Query process (preferred) or IE, from this URL:
* http://www.netflix.
If you log in using their DVD or streaming web site, the functions may not work. I've had sporadic results.
On Wed, Dec 26, 2012 at 8:29 AM, Kermit W. Prather <kermitp@tampabay.
Randy, I̢۪d be interested in the Netflix template. If you don̢۪t mind uploading it.
Kermit
Thu Dec 27, 2012 10:13 am (PST) . Posted by:
"Stock Jock" stockjocktrader
Randy,
How does your code parse csv data that's downloaded from
Yahoo? Sometimes the content has a comma in it already (i.e. Alcoa,
Inc) and that messes up the parsing. Is there an alternate method?
Thu Dec 27, 2012 10:45 am (PST) . Posted by:
"Randy H" rharmelink
I'll need more specific information on your problem -- the Yahoo current
quotes file doesn't have a comma in Alcoa's name. However, this:
=RCHGetYahooQuotes("AA,PFE","nsl1")
...returns this for me:
Alcoa Inc. Common AA 8.58 Pfizer, Inc. Comm PFE 24.8977
Note that Pfizer DOES have a comma in the name, and the function handled
it properly.
Here's what Yahoo's CSV file looked like for that function:
"Alcoa Inc. Common","AA",8.58
"Pfizer, Inc. Comm","PFE",24.8977
But, I just parse it simply -- if the first byte of the remaining line
is a double-quote, then I look for a double-quote followed by the
specified delimiter to mark the end of the next field, otherwise I just
look for the specified delimiter to mark the end of the next field. And
then repeat.
I know Yahoo doesn't code the "float" field properly, since they have
commas inside the field, without coding a double-quote around it. I
don't know of a general purpose CSV parser that would ever get that
field correctly. Even opening it as a CSV file in EXCEL, or doing a
"text to columns" in EXCEL.
On Thu, Dec 27, 2012 at 11:13 AM, Stock Jock stockjocktrader@yahoo.com
<mailto:stockjocktrader@yahoo.com > > wrote:
How does your code parse csv data that's downloaded from
Yahoo? Sometimes the content has a comma in it already (i.e. Alcoa,
Inc) and that messes up the parsing. Is there an alternate method?
quotes file doesn't have a comma in Alcoa's name. However, this:
=RCHGetYahooQuotes(
...returns this for me:
Alcoa Inc. Common AA 8.58 Pfizer, Inc. Comm PFE 24.8977
Note that Pfizer DOES have a comma in the name, and the function handled
it properly.
Here's what Yahoo's CSV file looked like for that function:
"Alcoa Inc. Common","AA"
"Pfizer, Inc. Comm","PFE",
But, I just parse it simply -- if the first byte of the remaining line
is a double-quote, then I look for a double-quote followed by the
specified delimiter to mark the end of the next field, otherwise I just
look for the specified delimiter to mark the end of the next field. And
then repeat.
I know Yahoo doesn't code the "float" field properly, since they have
commas inside the field, without coding a double-quote around it. I
don't know of a general purpose CSV parser that would ever get that
field correctly. Even opening it as a CSV file in EXCEL, or doing a
"text to columns" in EXCEL.
On Thu, Dec 27, 2012 at 11:13 AM, Stock Jock stockjocktrader@
<mailto:stockjocktrader@
How does your code parse csv data that's downloaded from
Yahoo? Sometimes the content has a comma in it already (i.e. Alcoa,
Inc) and that messes up the parsing. Is there an alternate method?
Thu Dec 27, 2012 11:00 am (PST) . Posted by:
"Stock Jock" stockjocktrader
In my own code that I'm developing for my database, I'm using
Split(objXML.
handling it correctly because of what I just mentioned. It also
doesn't remove the quotes. Can I see your parsing code?
------------
--- In smf_addin@yahoogrou
>
> I'll need more specific information on your problem -- the Yahoo
current
> quotes file doesn't have a comma in Alcoa's name. However, this:
>
> =RCHGetYahooQuotes(
>
> ...returns this for me:
>
> Alcoa Inc. Common AA 8.58 Pfizer, Inc. Comm PFE 24.8977
> Note that Pfizer DOES have a comma in the name, and the function
handled
> it properly.
>
> Here's what Yahoo's CSV file looked like for that function:
>
> "Alcoa Inc. Common","AA"
> "Pfizer, Inc. Comm","PFE",
>
> But, I just parse it simply -- if the first byte of the remaining line
> is a double-quote, then I look for a double-quote followed by the
> specified delimiter to mark the end of the next field, otherwise I
just
> look for the specified delimiter to mark the end of the next field.
And
> then repeat.
>
> I know Yahoo doesn't code the "float" field properly, since they have
> commas inside the field, without coding a double-quote around it. I
> don't know of a general purpose CSV parser that would ever get that
> field correctly. Even opening it as a CSV file in EXCEL, or doing a
> "text to columns" in EXCEL.
>
> On Thu, Dec 27, 2012 at 11:13 AM, Stock Jock stockjocktrader@
> wrote:
> How does your code parse csv data that's downloaded from
> Yahoo? Sometimes the content has a comma in it already (i.e.
Alcoa,
> Inc) and that messes up the parsing. Is there an alternate method?
>
Thu Dec 27, 2012 11:12 am (PST) . Posted by:
"Stock Jock" stockjocktrader
If I'm not mistaken, this must be your parsing code. I've isolated the routine and possibly it can be made into a seperate function? Is this all that I would need or is there something else?
************
'-----------
sqData = Replace(sqData, vbCrLf, vbLf)
aqData = Split(sqData, vbLf)
iDim1 = UBound(aqData, 1)
If iDim1 > kDim1 Then iDim1 = kDim1
For i1 = 0 To iDim1 - 1
iPos1 = 1
For i2 = 0 To 200
If i2 + 1 > kDim2 Then Exit For
If iPos1 > Len(aqData(i1)
sFind = IIf(Mid(aqData(
iPos2 = InStr(iPos1, aqData(i1) & sDel, sFind)
s1 = Left(Mid(aqData(
s2 = Trim(s1)
If Right(s2, 1) = "%" Then
n1 = 100
s2 = Left(s2, Len(s2) - 1)
Else
n1 = 1
End If
On Error Resume Next
s1 = smfConvertData(
On Error GoTo ErrorExit
vData(i1 + 1 + pHeader, i2 + 1) = s1
iPos1 = iPos2 + Len(sFind)
Next i2
Next i1
************
--- In smf_addin@yahoogrou
>
>
> In my own code that I'm developing for my database, I'm using
> Split(objXML.
> handling it correctly because of what I just mentioned. It also
> doesn't remove the quotes. Can I see your parsing code?
Thu Dec 27, 2012 11:30 am (PST) . Posted by:
"Randy Harmelink" rharmelink
That's the code all right...
But, actually, RCHGetYahooQuotes() can already be used as a general-purpose
CSV parser. Note that the parsed output from:
=smfGetCSVFIle("
http://download.finance.yahoo.com/d/quotes.csv?s=MMM,IBM&f=sl1d1t1c1ohgv&e=.csv
")
...is basically just the parsed output from:
=RCHGetYahooQuotes("
http://download.finance.yahoo.com/d/quotes.csv?s=MMM,IBM&f=sl1d1t1c1ohgv&e=.csv
","")
...and both just parse the URL passed to the function.
I modified RCHGetYahooQuotes() so that I could call it with smfGetCSVFile()
and parse any CSV file (or use whatever delimiter I choose). A blank second
parameter to RCHGetYahooQuotes() tells it to do parsing of a passed in URL,
instead of generating the URL and retrieving the Yahoo CSV file from the
Internet.
On Thu, Dec 27, 2012 at 12:12 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
>
> If I'm not mistaken, this must be your parsing code. I've isolated the
> routine and possibly it can be made into a seperate function? Is this all
> that I would need or is there something else?
> ******************************************************
> '------------------> Parse returned data
> sqData = Replace(sqData, vbCrLf, vbLf)
> aqData = Split(sqData, vbLf)
> iDim1 = UBound(aqData, 1)
> If iDim1 > kDim1 Then iDim1 = kDim1
> For i1 = 0 To iDim1 - 1
> iPos1 = 1
> For i2 = 0 To 200
> If i2 + 1 > kDim2 Then Exit For
> If iPos1 > Len(aqData(i1)) Then Exit For
> sFind = IIf(Mid(aqData(i1), iPos1, 1) = Chr(34), Chr(34), "")
> & sDel
> iPos2 = InStr(iPos1, aqData(i1) & sDel, sFind)
> s1 = Left(Mid(aqData(i1), iPos1 + Len(sFind) - 1, iPos2 -
> iPos1 - Len(sFind) + 1), 255)
> s2 = Trim(s1)
> If Right(s2, 1) = "%" Then
> n1 = 100
> s2 = Left(s2, Len(s2) - 1)
> Else
> n1 = 1
> End If
> On Error Resume Next
> s1 = smfConvertData(s2) / n1
> On Error GoTo ErrorExit
> vData(i1 + 1 + pHeader, i2 + 1) = s1
> iPos1 = iPos2 + Len(sFind)
> Next i2
> Next i1
> ******************************************************
>
But, actually, RCHGetYahooQuotes(
CSV parser. Note that the parsed output from:
=smfGetCSVFIle(
http://download.
")
...is basically just the parsed output from:
=RCHGetYahooQuotes(
http://download.
","")
...and both just parse the URL passed to the function.
I modified RCHGetYahooQuotes(
and parse any CSV file (or use whatever delimiter I choose). A blank second
parameter to RCHGetYahooQuotes(
instead of generating the URL and retrieving the Yahoo CSV file from the
Internet.
On Thu, Dec 27, 2012 at 12:12 PM, Stock Jock <stockjocktrader@
>
> If I'm not mistaken, this must be your parsing code. I've isolated the
> routine and possibly it can be made into a seperate function? Is this all
> that I would need or is there something else?
> ************
> '-----------
> sqData = Replace(sqData, vbCrLf, vbLf)
> aqData = Split(sqData, vbLf)
> iDim1 = UBound(aqData, 1)
> If iDim1 > kDim1 Then iDim1 = kDim1
> For i1 = 0 To iDim1 - 1
> iPos1 = 1
> For i2 = 0 To 200
> If i2 + 1 > kDim2 Then Exit For
> If iPos1 > Len(aqData(i1)
> sFind = IIf(Mid(aqData(
> & sDel
> iPos2 = InStr(iPos1, aqData(i1) & sDel, sFind)
> s1 = Left(Mid(aqData(
> iPos1 - Len(sFind) + 1), 255)
> s2 = Trim(s1)
> If Right(s2, 1) = "%" Then
> n1 = 100
> s2 = Left(s2, Len(s2) - 1)
> Else
> n1 = 1
> End If
> On Error Resume Next
> s1 = smfConvertData(
> On Error GoTo ErrorExit
> vData(i1 + 1 + pHeader, i2 + 1) = s1
> iPos1 = iPos2 + Len(sFind)
> Next i2
> Next i1
> ************
>
Thu Dec 27, 2012 11:53 am (PST) . Posted by:
"Stock Jock" stockjocktrader
It appears that Yahoo doesn't provide the use of any other delimiter
than the ","? Are you saying that your code changes the delimiter so as
not to confuse other "," in the response text?
============
--- In smf_addin@yahoogrou
>
> That's the code all right...
>
> But, actually, RCHGetYahooQuotes(
general-purpose
> CSV parser. Note that the parsed output from:
>
> =smfGetCSVFIle(
>
http://download.
&e=.csv
> ")
>
> ...is basically just the parsed output from:
>
> =RCHGetYahooQuotes(
>
http://download.
&e=.csv
> ","")
>
> ...and both just parse the URL passed to the function.
>
> I modified RCHGetYahooQuotes(
smfGetCSVFile(
> and parse any CSV file (or use whatever delimiter I choose). A blank
second
> parameter to RCHGetYahooQuotes(
URL,
> instead of generating the URL and retrieving the Yahoo CSV file from
the
> Internet.
>
> On Thu, Dec 27, 2012 at 12:12 PM, Stock Jock stockjocktrader@
>
> >
> > If I'm not mistaken, this must be your parsing code. I've isolated
the
> > routine and possibly it can be made into a seperate function? Is
this all
> > that I would need or is there something else?
> > ************
> > '-----------
> > sqData = Replace(sqData, vbCrLf, vbLf)
> > aqData = Split(sqData, vbLf)
> > iDim1 = UBound(aqData, 1)
> > If iDim1 > kDim1 Then iDim1 = kDim1
> > For i1 = 0 To iDim1 - 1
> > iPos1 = 1
> > For i2 = 0 To 200
> > If i2 + 1 > kDim2 Then Exit For
> > If iPos1 > Len(aqData(i1)
> > sFind = IIf(Mid(aqData(
> > & sDel
> > iPos2 = InStr(iPos1, aqData(i1) & sDel, sFind)
> > s1 = Left(Mid(aqData(
> > iPos1 - Len(sFind) + 1), 255)
> > s2 = Trim(s1)
> > If Right(s2, 1) = "%" Then
> > n1 = 100
> > s2 = Left(s2, Len(s2) - 1)
> > Else
> > n1 = 1
> > End If
> > On Error Resume Next
> > s1 = smfConvertData(
> > On Error GoTo ErrorExit
> > vData(i1 + 1 + pHeader, i2 + 1) = s1
> > iPos1 = iPos2 + Len(sFind)
> > Next i2
> > Next i1
> > ************
> >
>
Thu Dec 27, 2012 1:04 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
No. Just that I had another file I needed to parse, and it had different
delimiters. So I just made the delimiter a parameter to the function as
well -- defaulting to a comma.
Have you looked at their API feeds at all, that produce XML files?
On Thu, Dec 27, 2012 at 12:53 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
> It appears that Yahoo doesn't provide the use of any other delimiter than
> the ","? Are you saying that your code changes the delimiter so as not to
> confuse other "," in the response text?
>
delimiters. So I just made the delimiter a parameter to the function as
well -- defaulting to a comma.
Have you looked at their API feeds at all, that produce XML files?
On Thu, Dec 27, 2012 at 12:53 PM, Stock Jock <stockjocktrader@
> It appears that Yahoo doesn't provide the use of any other delimiter than
> the ","? Are you saying that your code changes the delimiter so as not to
> confuse other "," in the response text?
>
Tidak ada komentar:
Posting Komentar