12 New Messages
Digest #2655
2a
Re: Getting "Error" value and want to have it show as a zero value by "Kermit W. Prather" kermitpra
2b
Re: Getting "Error" value and want to have it show as a zero value [ by "Randy Harmelink" rharmelink
2c
Re: Getting "Error" value and want to have it show as a zero value by "Kermit W. Prather" kermitpra
2d
Re: Getting "Error" value and want to have it show as a zero value by "Randy Harmelink" rharmelink
2e
Re: Getting "Error" value and want to have it show as a zero value by "Randy Harmelink" rharmelink
2f
Re: Getting "Error" value and want to have it show as a zero value [ by "Kermit W. Prather" kermitpra
2g
Re: Getting "Error" value and want to have it show as a zero value by "Randy Harmelink" rharmelink
2h
Re: Getting "Error" value and want to have it show as a zero value by "Kermit W. Prather" kermitpra
Messages
Hello,
This email message is a notification to let you know that
a file has been uploaded to the Files area of the smf_addin
group.
File : /Uploads by forum members/EX-Dividend
Uploaded by : kermitpra <kermitp@tampabay.
Description : This spreadsheet retrieves the last 20 Ex-Dividates and payouts. It then calculates the number of dividend increases and decreases. The objective is to identify didvidend stocks that have a consistent payout over th last 20 payout periods. This is still a work in progress. It is being upload so Randy can assist with excel formulas. My skill are not even close to his.
You can access this file at the URL:
http://groups.
To learn more about file sharing for your group, please visit:
http://help.
Regards,
kermitpra <kermitp@tampabay.
Thu Jun 13, 2013 7:00 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Randy, you lost me. I am attaching the spreadsheet in question and I have uploaded it EX-Dividend-Dates-and-Payouts.xls <http://f1.grp.yahoofs.com/v1/VM25UXlcvTH7FqFXp9z_8JaF7d0D2RCu-0ptgz-m97anDNO1FUlKwCeXRFgf3NDlE91OP-OP6xw8yhcLW3jDPnIynK4obhrU/Uploads%20by%20forum%20members/EX-Dividend-Dates-and-Payouts.xls > into the files folder Uploads by forum members
Sorry to bug you with this but I have spent hours in help files and examples without success.
I don't understand why you changed formula by removing the O$3 and inserted 500 and 501 in your 2 examples
Neither of your examples returned a value when the element was a valid date or dollar amount.
It always returned a value that implied an ERROR result occurred.
In my example column O is referring to the element number in the table odd numbers are dividend dollar values and even numbers are EX-Dividend dates.
So a value of 19 is a numeric value not a date.
If you have time take a look at row 7 in the Retrieve stocks worksheet. Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with blank.
If you could help with cell M7 and AG then I can populate it over all the other cells.
Thanks, Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Wednesday, June 12, 2013 4:53 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
In such situations, I usually divide an expected value by 1. If it's numeric, the value stays the same. If not, it triggers an error.
For example, for the date and amount, I would do something like:
=IFERROR(DATEVALUE(smfGetTagContent("http://dividata.com/stock/ "&$F4&"/dividend","p",500,">Ex-Dividend Date"))/1,"--")
=IFERROR(smfGetTagContent("http://dividata.com/stock/ "&$F4&"/dividend","p",501,">Ex-Dividend Date")/1,0)
On Wed, Jun 12, 2013 at 7:47 AM, Kermit W. Prather <kermitp@tampabay.rr.com > wrote:
I need assistance with the IFERROR formula. In the table below I have hidden columns not needed for this formula.
I look at the Excel help but still can't figure out the correct formula.
What I want to do is have a zero value in place of the Error value that is the result of the IF statement shown in Column A Row 5
I hope I have provide enough information to allow you to understand what I am trying to do.
Column
A
B
F
O
1
11
Element#
19
1
per
per
Error
IFERROR(IF($B4=$F4,(smfGetTagContent("http://dividata.com/stock/ "&$F4&"/dividend","p",O$3,">Ex-Dividend Date"))," "),"0")
Sorry to bug you with this but I have spent hours in help files and examples without success.
I don't understand why you changed formula by removing the O$3 and inserted 500 and 501 in your 2 examples
Neither of your examples returned a value when the element was a valid date or dollar amount.
It always returned a value that implied an ERROR result occurred.
In my example column O is referring to the element number in the table odd numbers are dividend dollar values and even numbers are EX-Dividend dates.
So a value of 19 is a numeric value not a date.
If you have time take a look at row 7 in the Retrieve stocks worksheet. Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with blank.
If you could help with cell M7 and AG then I can populate it over all the other cells.
Thanks, Kermit
From: smf_addin@yahoogrou
Sent: Wednesday, June 12, 2013 4:53 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
In such situations, I usually divide an expected value by 1. If it's numeric, the value stays the same. If not, it triggers an error.
For example, for the date and amount, I would do something like:
=IFERROR(DATEVALUE(
=IFERROR(smfGetTagC
On Wed, Jun 12, 2013 at 7:47 AM, Kermit W. Prather <kermitp@tampabay.
I need assistance with the IFERROR formula. In the table below I have hidden columns not needed for this formula.
I look at the Excel help but still can't figure out the correct formula.
What I want to do is have a zero value in place of the Error value that is the result of the IF statement shown in Column A Row 5
I hope I have provide enough information to allow you to understand what I am trying to do.
Column
A
B
F
O
1
11
Element#
19
1
per
per
Error
IFERROR(IF($
Attachments with this message:
1 of 1 File(s)
Thu Jun 13, 2013 8:01 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
My formulas for G7 and AA7 would be:
=IF($B7=$F7,IFERROR(smfGetTagContent("http://dividata.com/stock/ "&$F7&"/dividend","p",G$3,">Ex-Dividend
Date")/1," ")," ")
=IF($B7=$F7,IFERROR(DATEVALUE(smfGetTagContent("http://dividata.com/stock/ "&$F7&"/dividend","p",AA$3,">Ex-Dividend
Date"))," ")," ")
...and copy rightward as needed.
On Thu, Jun 13, 2013 at 6:59 AM, Kermit W. Prather
<kermitp@tampabay.rr.com >wrote:
>
> Randy, you lost me. I am attaching the spreadsheet in question and I have
> uploaded it EX-Dividend-Dates-and-Payouts.xls<http://f1.grp.yahoofs.com/v1/VM25UXlcvTH7FqFXp9z_8JaF7d0D2RCu-0ptgz-m97anDNO1FUlKwCeXRFgf3NDlE91OP-OP6xw8yhcLW3jDPnIynK4obhrU/Uploads%20by%20forum%20members/EX-Dividend-Dates-and-Payouts.xls > into
> the files folder *
> *
>
> *Sorry to bug you with this but I have spent hours in help files and
> examples without success. *
>
>
>
> I don't understand why you changed formula by removing the O$3 and
> inserted 500 and 501 in your 2 examples
>
> Neither of your examples returned a value when the element was a valid
> date or dollar amount.
>
> It always returned a value that implied an ERROR result occurred.
>
>
>
> In my example column O is referring to the element number in the table odd
> numbers are dividend dollar values and even numbers are EX-Dividend dates.
>
> So a value of 19 is a numeric value not a date.
>
>
>
> If you have time take a look at row 7 in the Retrieve stocks worksheet.
> Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with
> $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with
> blank.
>
>
>
> If you could help with cell M7 and AG then I can populate it over all the
> other cells.
>
>
>
=IF($B7=$F7,
Date")/
=IF($B7=$F7,
Date"))
...and copy rightward as needed.
On Thu, Jun 13, 2013 at 6:59 AM, Kermit W. Prather
<kermitp@tampabay.
>
> Randy, you lost me. I am attaching the spreadsheet in question and I have
> uploaded it EX-Dividend-
> the files folder *
> *
>
> *Sorry to bug you with this but I have spent hours in help files and
> examples without success. *
>
>
>
> I don't understand why you changed formula by removing the O$3 and
> inserted 500 and 501 in your 2 examples
>
> Neither of your examples returned a value when the element was a valid
> date or dollar amount.
>
> It always returned a value that implied an ERROR result occurred.
>
>
>
> In my example column O is referring to the element number in the table odd
> numbers are dividend dollar values and even numbers are EX-Dividend dates.
>
> So a value of 19 is a numeric value not a date.
>
>
>
> If you have time take a look at row 7 in the Retrieve stocks worksheet.
> Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with
> $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with
> blank.
>
>
>
> If you could help with cell M7 and AG then I can populate it over all the
> other cells.
>
>
>
Thu Jun 13, 2013 10:57 am (PDT) . Posted by:
"Kermit W. Prather" kermitpra
THANKS, Randy
As usual your solution is perfect. But as I said it is a work in progress so I had change added column definitions and had to adjust your solution.
I do have a problem trying to pull multiple values for multiple stocks from dividata.com when initially opening the spreadsheet. If I keep it down to 10 or so stocks it returns the correct information.
Any thoughts on this?
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Thursday, June 13, 2013 11:01 AM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
My formulas for G7 and AA7 would be:
=IF($B7=$F7,IFERROR(smfGetTagContent("http://dividata.com/stock/ "&$F7&"/dividend","p",G$3,">Ex-Dividend Date")/1," ")," ")
=IF($B7=$F7,IFERROR(DATEVALUE(smfGetTagContent("http://dividata.com/stock/ "&$F7&"/dividend","p",AA$3,">Ex-Dividend Date"))," ")," ")
...and copy rightward as needed.
On Thu, Jun 13, 2013 at 6:59 AM, Kermit W. Prather <kermitp@tampabay.rr.com > wrote:
Randy, you lost me. I am attaching the spreadsheet in question and I have uploaded it EX-Dividend-Dates-and-Payouts.xls <http://f1.grp.yahoofs.com/v1/VM25UXlcvTH7FqFXp9z_8JaF7d0D2RCu-0ptgz-m97anDNO1FUlKwCeXRFgf3NDlE91OP-OP6xw8yhcLW3jDPnIynK4obhrU/Uploads%20by%20forum%20members/EX-Dividend-Dates-and-Payouts.xls > into the files folder
Sorry to bug you with this but I have spent hours in help files and examples without success.
I don't understand why you changed formula by removing the O$3 and inserted 500 and 501 in your 2 examples
Neither of your examples returned a value when the element was a valid date or dollar amount.
It always returned a value that implied an ERROR result occurred.
In my example column O is referring to the element number in the table odd numbers are dividend dollar values and even numbers are EX-Dividend dates.
So a value of 19 is a numeric value not a date.
If you have time take a look at row 7 in the Retrieve stocks worksheet. Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with blank.
If you could help with cell M7 and AG then I can populate it over all the other cells.
As usual your solution is perfect. But as I said it is a work in progress so I had change added column definitions and had to adjust your solution.
I do have a problem trying to pull multiple values for multiple stocks from dividata.com when initially opening the spreadsheet. If I keep it down to 10 or so stocks it returns the correct information.
Any thoughts on this?
Kermit
From: smf_addin@yahoogrou
Sent: Thursday, June 13, 2013 11:01 AM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
My formulas for G7 and AA7 would be:
=IF($B7=$F7,
=IF($B7=$F7,
...and copy rightward as needed.
On Thu, Jun 13, 2013 at 6:59 AM, Kermit W. Prather <kermitp@tampabay.
Randy, you lost me. I am attaching the spreadsheet in question and I have uploaded it EX-Dividend-
Sorry to bug you with this but I have spent hours in help files and examples without success.
I don't understand why you changed formula by removing the O$3 and inserted 500 and 501 in your 2 examples
Neither of your examples returned a value when the element was a valid date or dollar amount.
It always returned a value that implied an ERROR result occurred.
In my example column O is referring to the element number in the table odd numbers are dividend dollar values and even numbers are EX-Dividend dates.
So a value of 19 is a numeric value not a date.
If you have time take a look at row 7 in the Retrieve stocks worksheet. Symbol AMTG the cells (M7 thru Z7) returning values I want to replace with $0.00 or a zero value and cells (AG7 thru AT7) I want to replace with blank.
If you could help with cell M7 and AG then I can populate it over all the other cells.
Thu Jun 13, 2013 11:44 am (PDT) . Posted by:
"Randy Harmelink" rharmelink
Multiple values for a given stock won't make a difference, since they all
come from the same web page. The add-in just retrieves the web page once,
saves it, and then extracts data from the saved web page.
In any case, they appear to have some mechanism that denies over 50 page
requests within a short time...
You really should do this process as an smfUpdateDownloadTable worksheet,
so that it has values instead of formulas. They aren't going to change much
anyway.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:
>
>
> As usual your solution is perfect. But as I said it is a work in progress
> so I had change added column definitions and had to adjust your solution.
> ****
>
> ** **
>
> I do have a problem trying to pull multiple values for multiple stocks
> from dividata.com when initially opening the spreadsheet. If I keep it
> down to 10 or so stocks it returns the correct information.****
>
> ** **
>
> Any thoughts on this?
>
come from the same web page. The add-in just retrieves the web page once,
saves it, and then extracts data from the saved web page.
In any case, they appear to have some mechanism that denies over 50 page
requests within a short time...
You really should do this process as an smfUpdateDownloadTa
so that it has values instead of formulas. They aren't going to change much
anyway.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.
> wrote:
>
>
> As usual your solution is perfect. But as I said it is a work in progress
> so I had change added column definitions and had to adjust your solution.
> ****
>
> ** **
>
> I do have a problem trying to pull multiple values for multiple stocks
> from dividata.com when initially opening the spreadsheet. If I keep it
> down to 10 or so stocks it returns the correct information.
>
> ** **
>
> Any thoughts on this?
>
Thu Jun 13, 2013 12:21 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Attached is a copy of what it might look like as an smfUpdateDownloadTable
process.
To populate the table, just run the smfUpdateDownloadTable macro.
It will update rows until it finds a blank ticker symbol. That means you
can just insert an empty line to tell it where to stop. You can also sort
it to put those items you want to update at the top -- for example, oldest
ex-dividend dates.
In between updates, all data will stay the same because the workbook has
values in it instead of formulas that would otherwise recalculate every
time you open the workbook. This way, it ONLY updates when you run the
macro.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.rr.com
> wrote:
>
> Any thoughts on this?
>
process.
To populate the table, just run the smfUpdateDownloadTa
It will update rows until it finds a blank ticker symbol. That means you
can just insert an empty line to tell it where to stop. You can also sort
it to put those items you want to update at the top -- for example, oldest
ex-dividend dates.
In between updates, all data will stay the same because the workbook has
values in it instead of formulas that would otherwise recalculate every
time you open the workbook. This way, it ONLY updates when you run the
macro.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.
> wrote:
>
> Any thoughts on this?
>
Attachments with this message:
1 of 1 File(s)
Thu Jun 13, 2013 1:14 pm (PDT) . Posted by:
"Kermit W. Prather" kermitpra
Randy, I did not get the expected results. In fact, all I got was – in every cell. Any suggestions?
I stepped thru the routine but don't know how it is suppose to work. It did step thru all 10 rows.
Public Sub smfUpdateDownloadTable()
On Error GoTo ErrorExit
oldStatusBar = Application.DisplayStatusBar
Application.DisplayStatusBar = True
nTickers = Application.WorksheetFunction.CountA(Range([Ticker].Offset(1, 0), [Ticker].Offset(999, 0)))
For iRow = 1 To kRowLimit
sTicker = [Ticker].Offset(iRow, 0)
If sTicker = "" Then Exit For
Application.StatusBar = Round(100 * ((iRow - 1) / nTickers), 0) & "% Completed " & _
" -- now processing " & sTicker & " -- #" & iRow & " of " & nTickers
For iCol = 1 To 200
sFormula = [Ticker].Offset(-1, iCol)
If sFormula = "" Then Exit For
If UCase(sFormula) <> "X" Then
If IsNumeric(sFormula) Then
Never executed this code
If smfGetAParms(1) = "" Then s1 = RCHGetElementNumber("Source", 1)
s1 = smfWord(smfGetAParms(0 + sFormula), 3, ";")
If Left(s1, 1) = "=" Then
sFormula = s1
Else
sFormula = "RCHGetElementNumber(""~~~~~"", " & sFormula & ")"
End If
End If
sFormula = Replace(sFormula, "~~~~~", sTicker)
For i1 = 1 To 20
If InStr(sFormula, "~~~") = 0 Then Exit For
If InStr(sFormula, "~~~" & i1 & "~~~") > 0 Then
sFormula = Replace(sFormula, "~~~" & i1 & "~~~", [Ticker].Offset(iRow, iCol).Offset(0, -i1).Value2)
End If
Next i1
[Ticker].Offset(iRow, iCol) = Evaluate(sFormula)
End If
Next iCol
Next iRow
IFERROR(smfConvertData(smfGetTagContent("http://dividata.com/stock/~~~~~/dividend ","p",3,">Ex-Dividend Date"))/1,0)
IFERROR(smfConvertData(smfGetTagContent("http://dividata.com/stock/~~~~~/dividend ","p",5,">Ex-Dividend Date"))/1,0)
Ticker
Div #1
Div #2
AGNC
--
--
AI
--
--
AINV
--
--
AMTG
--
--
ARCC
--
--
ARI
--
--
BBEP
--
--
BKCC
--
--
BOE
--
--
CHKR
--
--
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Thursday, June 13, 2013 3:21 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value [1 Attachment]
[Attachment(s) from Randy Harmelink included below]
Attached is a copy of what it might look like as an smfUpdateDownloadTable process.
To populate the table, just run the smfUpdateDownloadTable macro.
It will update rows until it finds a blank ticker symbol. That means you can just insert an empty line to tell it where to stop. You can also sort it to put those items you want to update at the top -- for example, oldest ex-dividend dates.
In between updates, all data will stay the same because the workbook has values in it instead of formulas that would otherwise recalculate every time you open the workbook. This way, it ONLY updates when you run the macro.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.rr.com > wrote:
Any thoughts on this?
Attachment(s) from Randy Harmelink
1 of 1 File(s)
<http://l.yimg.com/kq/static/images/yg/img/doc/xls16x16.gif >
<http://xa.yimg.com/kq/groups/18094620/1535604518/name/smfUpdateDownloadTable-Get-Last-20-Ex-Dividends%2Exls > smfUpdateDownloadTable-Get-Last-20-Ex-Dividends.xls
I stepped thru the routine but don't know how it is suppose to work. It did step thru all 10 rows.
Public Sub smfUpdateDownloadTa
On Error GoTo ErrorExit
oldStatusBar = Application.
Application.
nTickers = Application.
For iRow = 1 To kRowLimit
sTicker = [Ticker].Offset(
If sTicker = "" Then Exit For
Application.
" -- now processing " & sTicker & " -- #" & iRow & " of " & nTickers
For iCol = 1 To 200
sFormula = [Ticker].Offset(
If sFormula = "" Then Exit For
If UCase(sFormula) <> "X" Then
If IsNumeric(sFormula) Then
Never executed this code
If smfGetAParms(
s1 = smfWord(smfGetAParm
If Left(s1, 1) = "=" Then
sFormula = s1
Else
sFormula = "RCHGetElement
End If
End If
sFormula = Replace(sFormula, "~~~~~"
For i1 = 1 To 20
If InStr(sFormula, "~~~"
If InStr(sFormula, "~~~" & i1 & "~~~"
sFormula = Replace(sFormula, "~~~" & i1 & "~~~"
End If
Next i1
[Ticker].Offset(
End If
Next iCol
Next iRow
IFERROR(smfConvertD
IFERROR(smfConvertD
Ticker
Div #1
Div #2
AGNC
--
--
AI
--
--
AINV
--
--
AMTG
--
--
ARCC
--
--
ARI
--
--
BBEP
--
--
BKCC
--
--
BOE
--
--
CHKR
--
--
From: smf_addin@yahoogrou
Sent: Thursday, June 13, 2013 3:21 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value [1 Attachment]
[Attachment(
Attached is a copy of what it might look like as an smfUpdateDownloadTa
To populate the table, just run the smfUpdateDownloadTa
It will update rows until it finds a blank ticker symbol. That means you can just insert an empty line to tell it where to stop. You can also sort it to put those items you want to update at the top -- for example, oldest ex-dividend dates.
In between updates, all data will stay the same because the workbook has values in it instead of formulas that would otherwise recalculate every time you open the workbook. This way, it ONLY updates when you run the macro.
On Thu, Jun 13, 2013 at 10:57 AM, Kermit W. Prather <kermitp@tampabay.
Any thoughts on this?
Attachment(s) from Randy Harmelink
1 of 1 File(s)
<http://l.yimg.
<http://xa.yimg.
Thu Jun 13, 2013 1:27 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Hmmm. Works fine here:
Incr Desc Unch *Ticker* *Div #1* *Div #2* *Div #3* *Div #4* *Div #5* *Div
#6* *Div #7* *Div #8* 3 3 13 AGNC $1.25 $1.25 $1.25 $1.25 $1.25 $1.40 $1.40
$1.40 3 2 14 AI $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 1 2 16
AINV $0.20 $0.20 $0.20 $0.20 $0.20 $0.28 $0.28 $0.28 4 1 1 AMTG $0.70 $1.05
$0.85 $0.75 $0.75 $0.30 -- -- 3 2 14 ARCC $0.38 $0.43 $0.43 $0.37 $0.37
$0.36 $0.35 $0.35 2 0 11 ARI $0.40 $0.40 $0.40 $0.40 $0.40 $0.40 $0.40
$0.40 12 1 6 BBEP $0.48 $0.47 $0.47 $0.46 $0.46 $0.45 $0.44 $0.42 1 2 16
BKCC $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 0 2 17 BOE $0.31 $0.31
$0.31 $0.41 $0.41 $0.57 $0.57 $0.57 5 2 0 CHKR $0.69 $0.67 $0.63 $0.61
$0.66 $0.73 $0.58 --
The code you highlighted never would be executed, since I'm not using
element numbers.
Do you have an older version of EXCEL?
Hmmm. Close EXCEL and restart it, then open this workbook and run the
macro. See if that works to purge everything retrieved in the past.
It should execute the following two statements for each cell above:
sFormula = Replace(sFormula, "~~~~~", sTicker)
.
.
.
[Ticker].Offset(iRow, iCol) = Evaluate(sFormula)
That first statement puts the ticker into the specified formula (e.g. what
is in cell F2). That second statement should evaluate that resulting
formula and place the value into the cell.
On Thu, Jun 13, 2013 at 1:14 PM, Kermit W. Prather
<kermitp@tampabay.rr.com >wrote:
> Randy, I did not get the expected results. In fact*, all I got was – in
> every cell*. Any suggestions? ****
>
>
>
Incr Desc Unch *Ticker* *Div #1* *Div #2* *Div #3* *Div #4* *Div #5* *Div
#6* *Div #7* *Div #8* 3 3 13 AGNC $1.25 $1.25 $1.25 $1.25 $1.25 $1.40 $1.40
$1.40 3 2 14 AI $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 $0.88 1 2 16
AINV $0.20 $0.20 $0.20 $0.20 $0.20 $0.28 $0.28 $0.28 4 1 1 AMTG $0.70 $1.05
$0.85 $0.75 $0.75 $0.30 -- -- 3 2 14 ARCC $0.38 $0.43 $0.43 $0.37 $0.37
$0.36 $0.35 $0.35 2 0 11 ARI $0.40 $0.40 $0.40 $0.40 $0.40 $0.40 $0.40
$0.40 12 1 6 BBEP $0.48 $0.47 $0.47 $0.46 $0.46 $0.45 $0.44 $0.42 1 2 16
BKCC $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 $0.26 0 2 17 BOE $0.31 $0.31
$0.31 $0.41 $0.41 $0.57 $0.57 $0.57 5 2 0 CHKR $0.69 $0.67 $0.63 $0.61
$0.66 $0.73 $0.58 --
The code you highlighted never would be executed, since I'm not using
element numbers.
Do you have an older version of EXCEL?
Hmmm. Close EXCEL and restart it, then open this workbook and run the
macro. See if that works to purge everything retrieved in the past.
It should execute the following two statements for each cell above:
sFormula = Replace(sFormula, "~~~~~"
.
.
.
[Ticker].Offset(
That first statement puts the ticker into the specified formula (e.g. what
is in cell F2). That second statement should evaluate that resulting
formula and place the value into the cell.
On Thu, Jun 13, 2013 at 1:14 PM, Kermit W. Prather
<kermitp@tampabay.
> Randy, I did not get the expected results. In fact*, all I got was – in
> every cell*. Any suggestions? ****
>
>
>
Thu Jun 13, 2013 1:32 pm (PDT) . Posted by:
"Kermit W. Prather" kermitpra
I am using EXCEL 2007 the table download has worked previously.
I'll work with it and get back to you.
Thanks,
Kermit
From: smf_addin@yahoogroups.com [mailto:smf_addin@yahoogroups.com ] On Behalf Of Randy Harmelink
Sent: Thursday, June 13, 2013 4:27 PM
To: smf_addin@yahoogroups.com
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
Hmmm. Works fine here:
Incr
Desc
Unch
Ticker
Div #1
Div #2
Div #3
Div #4
Div #5
Div #6
Div #7
Div #8
3
3
13
AGNC
$1.25
$1.25
$1.25
$1.25
$1.25
$1.40
$1.40
$1.40
3
2
14
AI
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
1
2
16
AINV
$0.20
$0.20
$0.20
$0.20
$0.20
$0.28
$0.28
$0.28
4
1
1
AMTG
$0.70
$1.05
$0.85
$0.75
$0.75
$0.30
--
--
3
2
14
ARCC
$0.38
$0.43
$0.43
$0.37
$0.37
$0.36
$0.35
$0.35
2
0
11
ARI
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
12
1
6
BBEP
$0.48
$0.47
$0.47
$0.46
$0.46
$0.45
$0.44
$0.42
1
2
16
BKCC
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
0
2
17
BOE
$0.31
$0.31
$0.31
$0.41
$0.41
$0.57
$0.57
$0.57
5
2
0
CHKR
$0.69
$0.67
$0.63
$0.61
$0.66
$0.73
$0.58
--
The code you highlighted never would be executed, since I'm not using element numbers.
Do you have an older version of EXCEL?
Hmmm. Close EXCEL and restart it, then open this workbook and run the macro. See if that works to purge everything retrieved in the past.
It should execute the following two statements for each cell above:
sFormula = Replace(sFormula, "~~~~~", sTicker)
.
.
.
[Ticker].Offset(iRow, iCol) = Evaluate(sFormula)
That first statement puts the ticker into the specified formula (e.g. what is in cell F2). That second statement should evaluate that resulting formula and place the value into the cell.
On Thu, Jun 13, 2013 at 1:14 PM, Kermit W. Prather <kermitp@tampabay.rr.com > wrote:
Randy, I did not get the expected results. In fact, all I got was – in every cell. Any suggestions?
I'll work with it and get back to you.
Thanks,
Kermit
From: smf_addin@yahoogrou
Sent: Thursday, June 13, 2013 4:27 PM
To: smf_addin@yahoogrou
Subject: Re: [smf_addin] Getting "Error" value and want to have it show as a zero value
Hmmm. Works fine here:
Incr
Desc
Unch
Ticker
Div #1
Div #2
Div #3
Div #4
Div #5
Div #6
Div #7
Div #8
3
3
13
AGNC
$1.25
$1.25
$1.25
$1.25
$1.25
$1.40
$1.40
$1.40
3
2
14
AI
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
$0.88
1
2
16
AINV
$0.20
$0.20
$0.20
$0.20
$0.20
$0.28
$0.28
$0.28
4
1
1
AMTG
$0.70
$1.05
$0.85
$0.75
$0.75
$0.30
--
--
3
2
14
ARCC
$0.38
$0.43
$0.43
$0.37
$0.37
$0.36
$0.35
$0.35
2
0
11
ARI
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
$0.40
12
1
6
BBEP
$0.48
$0.47
$0.47
$0.46
$0.46
$0.45
$0.44
$0.42
1
2
16
BKCC
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
$0.26
0
2
17
BOE
$0.31
$0.31
$0.31
$0.41
$0.41
$0.57
$0.57
$0.57
5
2
0
CHKR
$0.69
$0.67
$0.63
$0.61
$0.66
$0.73
$0.58
--
The code you highlighted never would be executed, since I'm not using element numbers.
Do you have an older version of EXCEL?
Hmmm. Close EXCEL and restart it, then open this workbook and run the macro. See if that works to purge everything retrieved in the past.
It should execute the following two statements for each cell above:
sFormula = Replace(sFormula, "~~~~~"
.
.
.
[Ticker].Offset(
That first statement puts the ticker into the specified formula (e.g. what is in cell F2). That second statement should evaluate that resulting formula and place the value into the cell.
On Thu, Jun 13, 2013 at 1:14 PM, Kermit W. Prather <kermitp@tampabay.
Randy, I did not get the expected results. In fact, all I got was – in every cell. Any suggestions?
Thu Jun 13, 2013 11:48 am (PDT) . Posted by:
"pete32257" pete32257
I have three inputs, stock symbol, amount of contracts, and
expiration month.
The result is listing of calls and puts; and works great
thanks for the templates/instruction.
I have two requests.
1. How do I get excel to tell me in a cell what the third
friday date is if giveN the expiration month input(ie. if input
is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
2. I have extracted next earning date per stock symbol input.
I also have a cell that has todays date. I want excel to tell
me in a cell if the earnings date falls between todays date
and expiration date then spit out a text saying something like
NO GOOD MOVE ON TO NEXT SEARCH.
expiration month.
The result is listing of calls and puts; and works great
thanks for the templates/instructi
I have two requests.
1. How do I get excel to tell me in a cell what the third
friday date is if giveN the expiration month input(ie. if input
is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
2. I have extracted next earning date per stock symbol input.
I also have a cell that has todays date. I want excel to tell
me in a cell if the earnings date falls between todays date
and expiration date then spit out a text saying something like
NO GOOD MOVE ON TO NEXT SEARCH.
Thu Jun 13, 2013 12:35 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
On Thu, Jun 13, 2013 at 11:45 AM, pete32257 <pete32257@yahoo.com > wrote:
> I have three inputs, stock symbol, amount of contracts, and
> expiration month.
>
> The result is listing of calls and puts; and works great
> thanks for the templates/instruction.
>
> I have two requests.
>
> 1. How do I get excel to tell me in a cell what the third
> friday date is if giveN the expiration month input(ie. if input
> is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
>
This will spit on 7/20/2013:
=smfGetOptionExpiry(2013,7)
They technically expire on Saturday, and contract ticker symbols usually
reflect that...
2. I have extracted next earning date per stock symbol input.
> I also have a cell that has todays date. I want excel to tell
> me in a cell if the earnings date falls between todays date
> and expiration date then spit out a text saying something like
> NO GOOD MOVE ON TO NEXT SEARCH.
>
Just compare the dates.
A little tip -- functions like TODAY() and NOW() are volatile, and any
function that refer to them will also be volatile. That means every time
you change something in the workbook, ALL of those volatile formulas will
recalculate. A trick I often use is to instead use the last traded date of
SPY:
=DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
In any case, your comparison would be something like:
=IF((E5<rToday)+(E5>rExpiry),"Continue","Ignore stock")
Although I would think you could ignore today's date process altogether,
and just use:
=IF(E5>rExpiry,"Continue","Ignore stock")
Also, a "gotcha" -- make sure your date is actually an EXCEL serial date
and not a string formatted as a date. An easy way to check is to change the
date format of the cell so that the date is displayed differently. If it is
a string, changing the date format of the cell will have no affect on how
the string is displayed.
> I have three inputs, stock symbol, amount of contracts, and
> expiration month.
>
> The result is listing of calls and puts; and works great
> thanks for the templates/instructi
>
> I have two requests.
>
> 1. How do I get excel to tell me in a cell what the third
> friday date is if giveN the expiration month input(ie. if input
> is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
>
This will spit on 7/20/2013:
=smfGetOptionExpiry
They technically expire on Saturday, and contract ticker symbols usually
reflect that...
2. I have extracted next earning date per stock symbol input.
> I also have a cell that has todays date. I want excel to tell
> me in a cell if the earnings date falls between todays date
> and expiration date then spit out a text saying something like
> NO GOOD MOVE ON TO NEXT SEARCH.
>
Just compare the dates.
A little tip -- functions like TODAY() and NOW() are volatile, and any
function that refer to them will also be volatile. That means every time
you change something in the workbook, ALL of those volatile formulas will
recalculate. A trick I often use is to instead use the last traded date of
SPY:
=DATEVALUE(RCHGetYa
In any case, your comparison would be something like:
=IF((E5<rToday)
Although I would think you could ignore today's date process altogether,
and just use:
=IF(E5>rExpiry,
Also, a "gotcha" -- make sure your date is actually an EXCEL serial date
and not a string formatted as a date. An easy way to check is to change the
date format of the cell so that the date is displayed differently. If it is
a string, changing the date format of the cell will have no affect on how
the string is displayed.
Thu Jun 13, 2013 12:58 pm (PDT) . Posted by:
"Pete" pete32257
Ok Randy, thanks so much for being a super user and most
of all sharing the expertise!!!!
Sent from my iPhone
On Jun 13, 2013, at 3:35 PM, Randy Harmelink <rharmelink@gmail.com > wrote:
> On Thu, Jun 13, 2013 at 11:45 AM, pete32257 <pete32257@yahoo.com > wrote:
>> I have three inputs, stock symbol, amount of contracts, and
>> expiration month.
>>
>> The result is listing of calls and puts; and works great
>> thanks for the templates/instruction.
>>
>> I have two requests.
>>
>> 1. How do I get excel to tell me in a cell what the third
>> friday date is if giveN the expiration month input(ie. if input
>> is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
>
> This will spit on 7/20/2013:
>
> =smfGetOptionExpiry(2013,7)
>
> They technically expire on Saturday, and contract ticker symbols usually reflect that...
>
>> 2. I have extracted next earning date per stock symbol input.
>> I also have a cell that has todays date. I want excel to tell
>> me in a cell if the earnings date falls between todays date
>> and expiration date then spit out a text saying something like
>> NO GOOD MOVE ON TO NEXT SEARCH.
>
> Just compare the dates.
>
> A little tip -- functions like TODAY() and NOW() are volatile, and any function that refer to them will also be volatile. That means every time you change something in the workbook, ALL of those volatile formulas will recalculate. A trick I often use is to instead use the last traded date of SPY:
>
> =DATEVALUE(RCHGetYahooQuotes("SPY","d1"))
>
> In any case, your comparison would be something like:
>
> =IF((E5<rToday)+(E5>rExpiry),"Continue","Ignore stock")
>
> Although I would think you could ignore today's date process altogether, and just use:
>
> =IF(E5>rExpiry,"Continue","Ignore stock")
>
> Also, a "gotcha" -- make sure your date is actually an EXCEL serial date and not a string formatted as a date. An easy way to check is to change the date format of the cell so that the date is displayed differently. If it is a string, changing the date format of the cell will have no affect on how the string is displayed.
>
of all sharing the expertise!!!
Sent from my iPhone
On Jun 13, 2013, at 3:35 PM, Randy Harmelink <rharmelink@gmail.
> On Thu, Jun 13, 2013 at 11:45 AM, pete32257 <pete32257@yahoo.
>> I have three inputs, stock symbol, amount of contracts, and
>> expiration month.
>>
>> The result is listing of calls and puts; and works great
>> thanks for the templates/instructi
>>
>> I have two requests.
>>
>> 1. How do I get excel to tell me in a cell what the third
>> friday date is if giveN the expiration month input(ie. if input
>> is 7, in a cell I want excel to spit out 7/19/2013 in another cell)
>
> This will spit on 7/20/2013:
>
> =smfGetOptionExpiry
>
> They technically expire on Saturday, and contract ticker symbols usually reflect that...
>
>> 2. I have extracted next earning date per stock symbol input.
>> I also have a cell that has todays date. I want excel to tell
>> me in a cell if the earnings date falls between todays date
>> and expiration date then spit out a text saying something like
>> NO GOOD MOVE ON TO NEXT SEARCH.
>
> Just compare the dates.
>
> A little tip -- functions like TODAY() and NOW() are volatile, and any function that refer to them will also be volatile. That means every time you change something in the workbook, ALL of those volatile formulas will recalculate. A trick I often use is to instead use the last traded date of SPY:
>
> =DATEVALUE(RCHGetYa
>
> In any case, your comparison would be something like:
>
> =IF((E5<rToday)
>
> Although I would think you could ignore today's date process altogether, and just use:
>
> =IF(E5>rExpiry,
>
> Also, a "gotcha" -- make sure your date is actually an EXCEL serial date and not a string formatted as a date. An easy way to check is to change the date format of the cell so that the date is displayed differently. If it is a string, changing the date format of the cell will have no affect on how the string is displayed.
>
Tidak ada komentar:
Posting Komentar