15 New Messages
Digest #2475
Messages
Mon Jan 21, 2013 7:01 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Usually, the easiest way to do RCHGetHTMLTable() is to use a unique string
from a column heading, and then go to the start and the end of the table
from that location. So:
=RCHGetHTMLTable("http://www.kase.kz/en/emitters/show/NRBN ","NIN or
ISIN",-1,"",1)
The news area is more difficult, since it's not an HTML table. It's
constructed with "div" tags. But you should be able to get the headlines
with:
=smfGetTagContent("http://www.kase.kz/en/emitters/show/NRBN ","a",2,"Last 10
news")
...varying the "2" from "2" to "11".
On Mon, Jan 21, 2013 at 4:11 AM, daul3285 daul3285@yahoo.com > wrote:
> I imported some data from this page
> http://www.kase.kz/en/emitters/show/NRBN using rchgettablecell, now i
> need to import whole table from that page. I need whole table under title
> "Instruments of Nurbank JSC included into trade lists" and last 10 news
> (only headers) under title "Last 10 news about Nurbank JSC for last 4
> months". I tried to use rchgethtmltable, but can't understand how to use
> it. And how i can import data, not table to excel using your adding?
>
from a column heading, and then go to the start and the end of the table
from that location. So:
=RCHGetHTMLTable(
ISIN",-
The news area is more difficult, since it's not an HTML table. It's
constructed with "div" tags. But you should be able to get the headlines
with:
=smfGetTagContent(
news")
...varying the "2" from "2" to "11"
On Mon, Jan 21, 2013 at 4:11 AM, daul3285 daul3285@yahoo.
> I imported some data from this page
> http://www.kase.
> need to import whole table from that page. I need whole table under title
> "Instruments of Nurbank JSC included into trade lists" and last 10 news
> (only headers) under title "Last 10 news about Nurbank JSC for last 4
> months"
> it. And how i can import data, not table to excel using your adding?
>
Mon Jan 21, 2013 8:12 pm (PST) . Posted by:
"calhandon" calhandon
Howdy Randy,
Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns multiple value formats and the column refuses to take any Excel formatting choices I try to set.
The values being returned are any one of the following formats:
Mmm dd (assuming current year?)
dd-Mmm-yy (no century, so that 1993 is returned as "00")
N/A
How can I make the display of these values consistent, such as mm/dd/yyyy, since I can't edit the format via Format Cells?
Thank you,
-Don
Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns multiple value formats and the column refuses to take any Excel formatting choices I try to set.
The values being returned are any one of the following formats:
Mmm dd (assuming current year?)
dd-Mmm-yy (no century, so that 1993 is returned as "00"
N/A
How can I make the display of these values consistent, such as mm/dd/yyyy, since I can't edit the format via Format Cells?
Thank you,
-Don
Mon Jan 21, 2013 9:24 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Format won't work because they are text strings that Yahoo returns. You'd
need to add a work column to convert the date into an EXCEL serial date if
you want to see a consistent format.
On Mon, Jan 21, 2013 at 9:12 PM, calhandon bozo@donandcarla.com > wrote:
>
> Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns
> multiple value formats and the column refuses to take any Excel formatting
> choices I try to set.
>
> The values being returned are any one of the following formats:
> Mmm dd (assuming current year?)
> dd-Mmm-yy (no century, so that 1993 is returned as "00")
> N/A
>
> How can I make the display of these values consistent, such as mm/dd/yyyy,
> since I can't edit the format via Format Cells?
>
need to add a work column to convert the date into an EXCEL serial date if
you want to see a consistent format.
On Mon, Jan 21, 2013 at 9:12 PM, calhandon bozo@donandcarla.
>
> Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns
> multiple value formats and the column refuses to take any Excel formatting
> choices I try to set.
>
> The values being returned are any one of the following formats:
> Mmm dd (assuming current year?)
> dd-Mmm-yy (no century, so that 1993 is returned as "00"
> N/A
>
> How can I make the display of these values consistent, such as mm/dd/yyyy,
> since I can't edit the format via Format Cells?
>
Mon Jan 21, 2013 10:25 pm (PST) . Posted by:
"bozo" calhandon
That's what I figured. Thank you for confirming this, Randy.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Monday, January 21, 2013 10:25 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] RCHGetYahooQuotes Ex-Div Date Problems
Format won't work because they are text strings that Yahoo returns. You'd need to add a work column to convert the date into an EXCEL serial date if you want to see a consistent format.
On Mon, Jan 21, 2013 at 9:12 PM, calhandon bozo@donandcarla.com > wrote:
Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns multiple value formats and the column refuses to take any Excel formatting choices I try to set.
The values being returned are any one of the following formats:
Mmm dd (assuming current year?)
dd-Mmm-yy (no century, so that 1993 is returned as "00")
N/A
How can I make the display of these values consistent, such as mm/dd/yyyy, since I can't edit the format via Format Cells?
From: smf_addin@yahoogrou
Sent: Monday, January 21, 2013 10:25 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] RCHGetYahooQuotes Ex-Div Date Problems
Format won't work because they are text strings that Yahoo returns. You'd need to add a work column to convert the date into an EXCEL serial date if you want to see a consistent format.
On Mon, Jan 21, 2013 at 9:12 PM, calhandon bozo@donandcarla.
Using the "q" parameter (Ex-Div Date) with RCHGetYahooQuotes returns multiple value formats and the column refuses to take any Excel formatting choices I try to set.
The values being returned are any one of the following formats:
Mmm dd (assuming current year?)
dd-Mmm-yy (no century, so that 1993 is returned as "00"
N/A
How can I make the display of these values consistent, such as mm/dd/yyyy, since I can't edit the format via Format Cells?
Mon Jan 21, 2013 8:43 pm (PST) . Posted by:
"calhandon" calhandon
Thank you for sharing your workbook with us, hupsuli.
--- In smf_addin@yahoogroups.com , "hupsuli" wrote:
>
> I put all sorts of data into excel and the final look is something like this:
>
> http://www.kolumbus.fi/cuddles/TMPStockCalc_screenshot.jpg
>
> The .xls is posted in the Files and Uploads by forum members -section.
>
> http://f1.grp.yahoofs.com/v1/gJD4UBMSOOAcLJLCdbicB3VjJPKVImnAwLHnoJH2zqyF7TqPuv9oAzh6YF9Gx781gdVjWAgElg_J2xGr1fygDATyEe6QAQ/Uploads%20by%20forum%20members/TMP%20Stock%20Calc.xls
>
--- In smf_addin@yahoogrou
>
> I put all sorts of data into excel and the final look is something like this:
>
> http://www.kolumbus
>
> The .xls is posted in the Files and Uploads by forum members -section.
>
> http://f1.grp.
>
Mon Jan 21, 2013 9:20 pm (PST) . Posted by:
"j.omeara@ymail.com" j.omeara@ymail.com
Hi,
I am new to the add in and am having a lot of difficulty getting smfForceRecalc to run. I have a workbook with about 150 rchGetElemNum functions in it. When I use the function with a new stock symbol it works fine. But I cannot get the smfForceRecalc to update the quotes. The macro seems to run but it doesn't update the prices. After reading through the board I changed my setting for browsing to check for newer version of web page every time I visit the web page. On another post I saw a recommendation to delete browsing history (and make sure the top box, Preserve Favorite Web Sites Data, is unclicked). This worked once as the prices updated when I ran the macro but has not worked since. It seems to be something with the cache because I also inserted a smfLastPrice function in another cell for a symbol I already had retrieved on the page and it returned the stale price, not the most recent. I am running Excel 2010, IE 9 and Windows 7 Pro. I appreciate any help you can give.
Thank you,
John
I am new to the add in and am having a lot of difficulty getting smfForceRecalc to run. I have a workbook with about 150 rchGetElemNum functions in it. When I use the function with a new stock symbol it works fine. But I cannot get the smfForceRecalc to update the quotes. The macro seems to run but it doesn't update the prices. After reading through the board I changed my setting for browsing to check for newer version of web page every time I visit the web page. On another post I saw a recommendation to delete browsing history (and make sure the top box, Preserve Favorite Web Sites Data, is unclicked). This worked once as the prices updated when I ran the macro but has not worked since. It seems to be something with the cache because I also inserted a smfLastPrice function in another cell for a symbol I already had retrieved on the page and it returned the stale price, not the most recent. I am running Excel 2010, IE 9 and Windows 7 Pro. I appreciate any help you can give.
Thank you,
John
Mon Jan 21, 2013 9:30 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It does sound like a cache problem. But if you've changed your IE setting,
I'm not sure what to tell you.
Is it possible you have multiple versions of IE installed, and the IE
object being used by EXCEL is not the one you're using as a browser? I'm
not even sure how to check such a thing...maybe refreshing the page in IE
first, then run the smfForceRecalculation to see if you got the new data to
match what the web page has?
For example, this gets a time stamp on the web page of a Yahoo quote page:
=smfGetTagContent("http://finance.yahoo.com/q?s=MMM
","span",-1,"yfs_market_time")
You don't have a location error in the workbook, right (see LINKS area of
the group)?
On Mon, Jan 21, 2013 at 9:06 PM, j.omeara@ymail.com
j.omeara@inneradv.com >wrote:
>
> I am new to the add in and am having a lot of difficulty getting
> smfForceRecalc to run. I have a workbook with about 150 rchGetElemNum
> functions in it. When I use the function with a new stock symbol it works
> fine. But I cannot get the smfForceRecalc to update the quotes. The macro
> seems to run but it doesn't update the prices. After reading through the
> board I changed my setting for browsing to check for newer version of web
> page every time I visit the web page. On another post I saw a
> recommendation to delete browsing history (and make sure the top box,
> Preserve Favorite Web Sites Data, is unclicked). This worked once as the
> prices updated when I ran the macro but has not worked since. It seems to
> be something with the cache because I also inserted a smfLastPrice function
> in another cell for a symbol I already had retrieved on the page and it
> returned the stale price, not the most recent. I am running Excel 2010, IE
> 9 and Windows 7 Pro. I appreciate any help you can give.
>
I'm not sure what to tell you.
Is it possible you have multiple versions of IE installed, and the IE
object being used by EXCEL is not the one you're using as a browser? I'm
not even sure how to check such a thing...maybe refreshing the page in IE
first, then run the smfForceRecalculati
match what the web page has?
For example, this gets a time stamp on the web page of a Yahoo quote page:
=smfGetTagContent(
","
You don't have a location error in the workbook, right (see LINKS area of
the group)?
On Mon, Jan 21, 2013 at 9:06 PM, j.omeara@ymail.
j.omeara@inneradv.
>
> I am new to the add in and am having a lot of difficulty getting
> smfForceRecalc to run. I have a workbook with about 150 rchGetElemNum
> functions in it. When I use the function with a new stock symbol it works
> fine. But I cannot get the smfForceRecalc to update the quotes. The macro
> seems to run but it doesn't update the prices. After reading through the
> board I changed my setting for browsing to check for newer version of web
> page every time I visit the web page. On another post I saw a
> recommendation to delete browsing history (and make sure the top box,
> Preserve Favorite Web Sites Data, is unclicked). This worked once as the
> prices updated when I ran the macro but has not worked since. It seems to
> be something with the cache because I also inserted a smfLastPrice function
> in another cell for a symbol I already had retrieved on the page and it
> returned the stale price, not the most recent. I am running Excel 2010, IE
> 9 and Windows 7 Pro. I appreciate any help you can give.
>
Mon Jan 21, 2013 9:33 pm (PST) . Posted by:
"bozo" calhandon
Thanks Randy. I'll keep an eye on the pages.
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Sunday, January 20, 2013 1:21 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Re: S&P 500 Functions No Longer Working on Template
Well, right now the web pages are empty. It may be because they are updating things right now...
On Sun, Jan 20, 2013 at 1:18 AM, calhandon bozo@donandcarla.com > wrote:
It's not only the S&P 500 data, but company, sector and industry as well. All fields in the template are blank (Error).
I did find some of what I need on MSN and will look at Yahoo as well.
From: smf_addin@yahoogrou
Sent: Sunday, January 20, 2013 1:21 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Re: S&P 500 Functions No Longer Working on Template
Well, right now the web pages are empty. It may be because they are updating things right now...
On Sun, Jan 20, 2013 at 1:18 AM, calhandon bozo@donandcarla.
It's not only the S&P 500 data, but company, sector and industry as well. All fields in the template are blank (Error).
I did find some of what I need on MSN and will look at Yahoo as well.
Tue Jan 22, 2013 12:22 am (PST) . Posted by:
"Abdur" abdurrofiq@rocketmail.com
Ok, thank you Randy
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> You would need do a separate RCHGetYahooHistory() function for each ticker
> symbol. It doesn't allow multiple ticker symbols to be passed -- there
> would be no reason to, since each ticker symbol would still need a separate
> Internet access to get the data.
>
> On Mon, Jan 21, 2013 at 2:36 AM, Abdur wrote:
>
> >
> > I wanna say thank before because of your work. Can I use
> > RCHGetYahooHistory for more than one ticker. I need closing price and
> > volume for several tickers, but in the same date range. For example; I have
> > 10 tickers in coloum, and the same range data for those tickers in row.I
> > have tried to use this formula: RCHGetYahoohistory("IBM,MMM,
> > MRK,MM,etc",2010,3,10,2011,3,10,"w";"tdcv). but it doesn't work. Could you
> > help me, which formula I should use?..
> >
>
--- In smf_addin@yahoogrou
>
> You would need do a separate RCHGetYahooHistory(
> symbol. It doesn't allow multiple ticker symbols to be passed -- there
> would be no reason to, since each ticker symbol would still need a separate
> Internet access to get the data.
>
> On Mon, Jan 21, 2013 at 2:36 AM, Abdur wrote:
>
> >
> > I wanna say thank before because of your work. Can I use
> > RCHGetYahooHistory for more than one ticker. I need closing price and
> > volume for several tickers, but in the same date range. For example; I have
> > 10 tickers in coloum, and the same range data for those tickers in row.I
> > have tried to use this formula: RCHGetYahoohistory(
> > MRK,MM,etc"
> > help me, which formula I should use?..
> >
>
Tue Jan 22, 2013 12:47 am (PST) . Posted by:
"calhandon" calhandon
Howdy Randy,
Regarding my message about Ex-Dividend Dates with RCHGetYahooQuotes, I
took a look at your modGetYahooQuotes function and made a 3-line
addition, denoted by a comment below. All this does is convert the
returned value from Yahoo to a serial date, if the value is a valid
date. Please feel free to add it permanently if you would like to.
Thanks for all of your time and effort spent on this great project!
-Don
--- start code ---
'------------------> Parse returned data
'sqData = Replace(sqData, Chr(10), Chr(13))
'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
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
' If this is an Ex-Dividend Date, convert it to DateSerial
format ...
If ((Mid(sItems, i2 + 1, 1) = "q") And (IsDate(s1))) Then
s1 = CDate(s1)
End If
vData(i1 + 1 + pHeader, i2 + 1) = s1
iPos1 = iPos2 + Len(sFind)
Next i2
Next i1
--- end code ---
Regarding my message about Ex-Dividend Dates with RCHGetYahooQuotes, I
took a look at your modGetYahooQuotes function and made a 3-line
addition, denoted by a comment below. All this does is convert the
returned value from Yahoo to a serial date, if the value is a valid
date. Please feel free to add it permanently if you would like to.
Thanks for all of your time and effort spent on this great project!
-Don
--- start code ---
'-------
'sqData = Replace(sqData, Chr(10), Chr(13))
'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
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(
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
' If this is an Ex-Dividend Date, convert it to DateSerial
format ...
If ((Mid(sItems, i2 + 1, 1) = "q"
s1 = CDate(s1)
End If
vData(i1 + 1 + pHeader, i2 + 1) = s1
iPos1 = iPos2 + Len(sFind)
Next i2
Next i1
--- end code ---
Tue Jan 22, 2013 4:56 am (PST) . Posted by:
"lfranzen_1999" lfranzen_1999
Don -
Your way is probably more elegant, but another way to get around this issue in excel is by parsing the text string. Below is some example code from an excel macro I wrote where I parse dates from a site that I scrape for date data (sometimes yahoo's data is a little behind), you can just cut the parsing section from the code below, and modify it to meet your needs.
Lee
Sub parsedivinvfiles()
'This macro parses dates from files scraped from dividend site
'into a form that excel can understand as a date
Dim monthtext As String
Dim daytext As String
Dim yeartext As String
Dim celltext As String
Dim exdatestring As String
Dim paydatestring As String
Dim historyfiledirectory As String
Windows("Dividend Stock List.xls").Activate
Sheets("Constants").Select
Range("A1").Select
Do Until ActiveCell.Value = "divhistoryfiles"
ActiveCell.Offset(1, 0).Select
Loop
historyfiledirectory = ActiveCell.Offset(0, 1).Value
exdatestring = "Dividend Ex Date"
paydatestring = "Dividend Pay Date"
MyFile = Dir(historyfiledirectory & "*.csv")
Do Until MyFile = ""
Workbooks.Open (historyfiledirectory) & MyFile
Range("A1").Name = "startofrownames"
Range("A1").End(xlDown).Name = "endofrownames"
'If WorksheetFunction.CountIf(Range("startofrownames:endofrownames"), exdatestring) > 0 Then
Range("startofrownames:endofrownames").Select
Selection.Find(What:=exdatestring, after:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
celltext = ActiveCell.Value
monthtext = Left(celltext, 4)
yeartext = Right(celltext, 5)
daytext = Mid(celltext, 5, 3)
ActiveCell.Value = daytext & monthtext & yeartext
'End If
'If WorksheetFunction.CountIf(Range("startofrownames:endofrownames"), paydatestring) > 0 Then
Range("startofrownames:endofrownames").Select
Selection.Find(What:=paydatestring, after:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, 1).Select
celltext = ActiveCell.Value
monthtext = Left(celltext, 4)
yeartext = Right(celltext, 5)
daytext = Mid(celltext, 5, 3)
ActiveCell.Value = daytext & monthtext & yeartext
'End If
ActiveWorkbook.Close savechanges:=True
MyFile = Dir
Loop
End Sub
--- In smf_addin@yahoogroups.com , "calhandon" wrote:
>
> Howdy Randy,
>
> Regarding my message about Ex-Dividend Dates with RCHGetYahooQuotes, I
> took a look at your modGetYahooQuotes function and made a 3-line
> addition, denoted by a comment below. All this does is convert the
> returned value from Yahoo to a serial date, if the value is a valid
> date. Please feel free to add it permanently if you would like to.
>
> Thanks for all of your time and effort spent on this great project!
>
> -Don
>
> --- start code ---
>
> '------------------> Parse returned data
> 'sqData = Replace(sqData, Chr(10), Chr(13))
> 'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
> 'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
> 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
>
> ' If this is an Ex-Dividend Date, convert it to DateSerial
> format ...
> If ((Mid(sItems, i2 + 1, 1) = "q") And (IsDate(s1))) Then
> s1 = CDate(s1)
> End If
>
> vData(i1 + 1 + pHeader, i2 + 1) = s1
> iPos1 = iPos2 + Len(sFind)
> Next i2
> Next i1
>
>
> --- end code ---
>
Your way is probably more elegant, but another way to get around this issue in excel is by parsing the text string. Below is some example code from an excel macro I wrote where I parse dates from a site that I scrape for date data (sometimes yahoo's data is a little behind), you can just cut the parsing section from the code below, and modify it to meet your needs.
Lee
Sub parsedivinvfiles(
'This macro parses dates from files scraped from dividend site
'into a form that excel can understand as a date
Dim monthtext As String
Dim daytext As String
Dim yeartext As String
Dim celltext As String
Dim exdatestring As String
Dim paydatestring As String
Dim historyfiledirector
Windows("
Sheets("
Range("
Do Until ActiveCell.Value = "divhistoryfil
ActiveCell.Offset(
Loop
historyfiledirector
exdatestring = "Dividend Ex Date"
paydatestring = "Dividend Pay Date"
MyFile = Dir(historyfiledire
Do Until MyFile = ""
Workbooks.Open (historyfiledirecto
Range("
Range("
'If WorksheetFunction.
Range("
Selection.Find(
, LookAt:=xlPart, SearchOrder:
MatchCase:=False, SearchFormat:
ActiveCell.Offset(
celltext = ActiveCell.Value
monthtext = Left(celltext, 4)
yeartext = Right(celltext, 5)
daytext = Mid(celltext, 5, 3)
ActiveCell.Value = daytext & monthtext & yeartext
'End If
'If WorksheetFunction.
Range("
Selection.Find(
, LookAt:=xlPart, SearchOrder:
MatchCase:=False, SearchFormat:
ActiveCell.Offset(
celltext = ActiveCell.Value
monthtext = Left(celltext, 4)
yeartext = Right(celltext, 5)
daytext = Mid(celltext, 5, 3)
ActiveCell.Value = daytext & monthtext & yeartext
'End If
ActiveWorkbook.
MyFile = Dir
Loop
End Sub
--- In smf_addin@yahoogrou
>
> Howdy Randy,
>
> Regarding my message about Ex-Dividend Dates with RCHGetYahooQuotes, I
> took a look at your modGetYahooQuotes function and made a 3-line
> addition, denoted by a comment below. All this does is convert the
> returned value from Yahoo to a serial date, if the value is a valid
> date. Please feel free to add it permanently if you would like to.
>
> Thanks for all of your time and effort spent on this great project!
>
> -Don
>
> --- start code ---
>
> '-------
> 'sqData = Replace(sqData, Chr(10), Chr(13))
> 'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
> 'sqData = Replace(sqData, Chr(13) & Chr(13), Chr(13))
> 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(
> 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
>
> ' If this is an Ex-Dividend Date, convert it to DateSerial
> format ...
> If ((Mid(sItems, i2 + 1, 1) = "q"
> s1 = CDate(s1)
> End If
>
> vData(i1 + 1 + pHeader, i2 + 1) = s1
> iPos1 = iPos2 + Len(sFind)
> Next i2
> Next i1
>
>
> --- end code ---
>
Tue Jan 22, 2013 3:03 am (PST) . Posted by:
"dennyschenk" dennyschenk
Hello,
this morning i have had to do an update but at reuters i can´t find the financials. So the addin cannot work.
What is happened?
this morning i have had to do an update but at reuters i can´t find the financials. So the addin cannot work.
What is happened?
Tue Jan 22, 2013 3:04 am (PST) . Posted by:
"Randy Harmelink" rharmelink
Don't know. The data's been MIA from their web site all weekend...
On Tue, Jan 22, 2013 at 3:38 AM, dennyschenk dennyschenk@yahoo.de > wrote:
>
> this morning i have had to do an update but at reuters i can´t find the
> financials. So the addin cannot work.
>
> What is happened?
>
On Tue, Jan 22, 2013 at 3:38 AM, dennyschenk dennyschenk@
>
> this morning i have had to do an update but at reuters i can´t find the
> financials. So the addin cannot work.
>
> What is happened?
>
Tue Jan 22, 2013 4:18 am (PST) . Posted by:
"cesarcrivelli" cesarcrivelli
Hi all,
Leveraging from this topic, I am trying to get the Close, Open, High, Low, Vol, Avg Vol, 52 wk high, 52 wk low, from Reuters, trough the following address
http://www.reuters.com/finance/stocks/overview?symbol=PETR4.SA
I am using this formula
=RCHGetTableCell("http://www.reuters.com/finance/stocks/overview?symbol=PETR4.SA ";1;"overview";"Prev Close")
But instead of getting the close price I receive "beta:"
Any help?
--- In smf_addin@yahoogroups.com , Randy Harmelink wrote:
>
> Don't know. The data's been MIA from their web site all weekend...
>
> On Tue, Jan 22, 2013 at 3:38 AM, dennyschenk wrote:
>
> >
> > this morning i have had to do an update but at reuters i can´t find the
> > financials. So the addin cannot work.
> >
> > What is happened?
> >
>
Leveraging from this topic, I am trying to get the Close, Open, High, Low, Vol, Avg Vol, 52 wk high, 52 wk low, from Reuters, trough the following address
http://www.reuters.
I am using this formula
=RCHGetTableCell(
But instead of getting the close price I receive "beta:"
Any help?
--- In smf_addin@yahoogrou
>
> Don't know. The data's been MIA from their web site all weekend...
>
> On Tue, Jan 22, 2013 at 3:38 AM, dennyschenk wrote:
>
> >
> > this morning i have had to do an update but at reuters i can´t find the
> > financials. So the addin cannot work.
> >
> > What is happened?
> >
>
Tue Jan 22, 2013 4:28 am (PST) . Posted by:
"dennyschenk" dennyschenk
I think it is the same problem. They changed something at the style of this page and so the addin can´t find the right data and shifts in another line with other data like "Beta".
Hopefully they will bring the style in the old shape otherwise the program hat do get relaunge with new vba infomation.
Hopefully they will bring the style in the old shape otherwise the program hat do get relaunge with new vba infomation.
Tidak ada komentar:
Posting Komentar