8 Messages
Digest #3218
Messages
Thu Oct 23, 2014 5:45 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
To update the add-in files, best practice is to exit EXCEL, then just unzip
the ZIP archive in the SMF folder, replacing files as needed. Then restart
EXCEL, which should then load the newly replaced files. No need to
uninstall.
The formula you cite below won't work, because the web page URL still uses
the "BRK-B" ticker symbol. So that formula won't find a web page with the
option contracts on it.
I just uploaded version 2014.10.23, and this now works for me using those
latest updates:
=smfGetYahooOptionQuote("BRK-B", "P", DATE(2016,1,15),105,"a")
The issue was that the web page itself uses "BRK-B" but the option
contracts were using "BRKB". And the add-in was using whatever was passed
as the ticker symbol, in both places. So I had to make a change to strip
out the hyphen when looking for the option contract, but still keep the
hyphen when looking up the web page. Before the latest changes, Yahoo must
have been using the hyphen in both places, in order for it to have been
working.
On Thu, Oct 23, 2014 at 5:26 PM, bob_15861586@...wrote:
>
> I had to uninstall the old one and then install the new one.. I then had
> to close and restart EXCEL twice. I do
> not understand why I had to do it twice. Maybe I made a mistake. Maybe
> when I was quitting EXCEL I
> just closed the file. I am just not sure.
>
> A few minutes ago, I tried the following command:
> =smfGetYahooOptionQuote("BRKB", "P", DATE(2016,1,15),105,"a")
> This came up with an error. Would you like me to upload the spread sheet?
>
>
>
the ZIP archive in the SMF folder, replacing files as needed. Then restart
EXCEL, which should then load the newly replaced files. No need to
uninstall.
The formula you cite below won't work, because the web page URL still uses
the "BRK-B" ticker symbol. So that formula won't find a web page with the
option contracts on it.
I just uploaded version 2014.10.23, and this now works for me using those
latest updates:
=smfGetYahooOptionQ
The issue was that the web page itself uses "BRK-B" but the option
contracts were using "BRKB"
as the ticker symbol, in both places. So I had to make a change to strip
out the hyphen when looking for the option contract, but still keep the
hyphen when looking up the web page. Before the latest changes, Yahoo must
have been using the hyphen in both places, in order for it to have been
working.
On Thu, Oct 23, 2014 at 5:26 PM, bob_15861586@
>
> I had to uninstall the old one and then install the new one.. I then had
> to close and restart EXCEL twice. I do
> not understand why I had to do it twice. Maybe I made a mistake. Maybe
> when I was quitting EXCEL I
> just closed the file. I am just not sure.
>
> A few minutes ago, I tried the following command:
> =smfGetYahooOptionQ
> This came up with an error. Would you like me to upload the spread sheet?
>
>
>
Thu Oct 23, 2014 7:34 pm (PDT) . Posted by:
bob_15861586
Randy,
After installing the latest version of the SMF macros, I find that I can get option quotes on BRKB. Thanks.
However, I cannot get option quotes on the DJX. Here is what I was using to get options on the DJX:
=smfGetYahooOptionQuote("^DJX", "C", DATE(2015,1,17),105,"a")
I also tried:
=smfGetYahooOptionQuote("DJX", "C", DATE(2015,1,17),105,"a")
I checked and the data is there. However, both calls come up with an error.
Bob
After installing the latest version of the SMF macros, I find that I can get option quotes on BRKB. Thanks.
However, I cannot get option quotes on the DJX. Here is what I was using to get options on the DJX:
=smfGetYahooOptionQ
I also tried:
=smfGetYahooOptionQ
I checked and the data is there. However, both calls come up with an error.
Bob
Thu Oct 23, 2014 8:24 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
Same situation. The "^" is needed for the URL, but needs to be stripped
when looking up the contract. I made a change similar to the hyphen and
it's working for me. I'll upload version 2.1.2014.10.23.B later.
I don't use these "unusual" situations, so would never even know to look
for them. :)
However, I beg to differ that the data you're trying to get is there -- the
strike price isn't one Yahoo carries.
Just uploaded version 2.1.2014.10.23.B to the website.
On Thu, Oct 23, 2014 at 7:34 PM, bob_15861586@...wrote:
>
> After installing the latest version of the SMF macros, I find that I can
> get option quotes on BRKB. Thanks.
>
> However, I cannot get option quotes on the DJX. Here is what I was using
> to get options on the DJX:
> =smfGetYahooOptionQuote("^DJX", "C", DATE(2015,1,17),105,"a")
> I also tried:
> =smfGetYahooOptionQuote("DJX", "C", DATE(2015,1,17),105,"a")
>
> I checked and the data is there. However, both calls come up with an error.
>
>
when looking up the contract. I made a change similar to the hyphen and
it's working for me. I'll upload version 2.1.2014.10.
I don't use these "unusual" situations, so would never even know to look
for them. :)
However, I beg to differ that the data you're trying to get is there -- the
strike price isn't one Yahoo carries.
Just uploaded version 2.1.2014.10.
On Thu, Oct 23, 2014 at 7:34 PM, bob_15861586@
>
> After installing the latest version of the SMF macros, I find that I can
> get option quotes on BRKB. Thanks.
>
> However, I cannot get option quotes on the DJX. Here is what I was using
> to get options on the DJX:
> =smfGetYahooOptionQ
> I also tried:
> =smfGetYahooOptionQ
>
> I checked and the data is there. However, both calls come up with an error.
>
>
Thu Oct 23, 2014 6:24 pm (PDT) . Posted by:
steven_rourk
Just one related question Randy. I can't get around this problem. For example:
Column A
Column B
Row1
0
0
Row2
1140.84
0
Row3
0
0
Row4
0
0
Row5
0
0
Row6
1144.06
0
Row7
0
0
Row8
1142.18
0
Row9
1140.42
0
Row10
0
1084.1
Row11
0
1087.12
Row12
1200
0
Row13
0
1091
Row14
0
0
I want a formula that calculates the maximum value between two rows in ColumnA where the first row is given but the last row is dynamic, depending on whether the value in ColumnB is larger than 0
So for example I want the maximum value between A3 and the first row in column A where there is value larger than 0 in column B, which in the table above would be row 10…..so the maximum value between A3:A10…. The formula should be =MAX(A3: ??)
Column A
Column B
Row1
0
0
Row2
1140.84
0
Row3
0
0
Row4
0
0
Row5
0
0
Row6
1144.06
0
Row7
0
0
Row8
1142.18
0
Row9
1140.42
0
Row10
0
1084.1
Row11
0
1087.12
Row12
1200
0
Row13
0
1091
Row14
0
0
I want a formula that calculates the maximum value between two rows in ColumnA where the first row is given but the last row is dynamic, depending on whether the value in ColumnB is larger than 0
So for example I want the maximum value between A3 and the first row in column A where there is value larger than 0 in column B, which in the table above would be row 10…..so the maximum value between A3:A10…. The formula should be =MAX(A3: ??)
Thu Oct 23, 2014 7:01 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
If I'm understanding you correctly, something like this array-entered
formula might work:
=MAX(OFFSET(A3,0,0,MIN(IF(B3:B100>0,ROW(B3:B100),99999))-ROW(B3),1))
On Thu, Oct 23, 2014 at 6:24 PM, steven_rourk@...wrote:
>
> Just one related question Randy. I can't get around this problem. For
> example:
>
>
>
> *Column A*
>
> *Column B*
>
> *Row1*
>
> 0
>
> 0
>
> *Row2*
>
> 1140.84
>
> 0
>
> *Row3*
>
> 0
>
> 0
>
> *Row4*
>
> 0
>
> 0
>
> *Row5*
>
> 0
>
> 0
>
> *Row6*
>
> 1144.06
>
> 0
>
> *Row7*
>
> 0
>
> 0
>
> *Row8*
>
> 1142.18
>
> 0
>
> *Row9*
>
> 1140.42
>
> 0
>
> *Row10*
>
> 0
>
> 1084.1
>
> *Row11*
>
> 0
>
> 1087.12
>
> *Row12*
>
> 1200
>
> 0
>
> *Row13*
>
> 0
>
> 1091
>
> *Row14*
>
> 0
>
> 0
>
>
>
> I want a formula that calculates the maximum value between two rows in
> ColumnA where the first row is given but the last row is dynamic, depending
> on whether the value in ColumnB is larger than 0
>
> So for example I want the maximum value between A3 and the first row in
> column A where there is value larger than 0 in column B, which in the table
> above would be row 10…..so the maximum value between A3:A10…. The formula
> should be =MAX(A3: ??)
>
formula might work:
=MAX(OFFSET(
On Thu, Oct 23, 2014 at 6:24 PM, steven_rourk@
>
> Just one related question Randy. I can't get around this problem. For
> example:
>
>
>
> *Column A*
>
> *Column B*
>
> *Row1*
>
> 0
>
> 0
>
> *Row2*
>
> 1140.84
>
> 0
>
> *Row3*
>
> 0
>
> 0
>
> *Row4*
>
> 0
>
> 0
>
> *Row5*
>
> 0
>
> 0
>
> *Row6*
>
> 1144.06
>
> 0
>
> *Row7*
>
> 0
>
> 0
>
> *Row8*
>
> 1142.18
>
> 0
>
> *Row9*
>
> 1140.42
>
> 0
>
> *Row10*
>
> 0
>
> 1084.1
>
> *Row11*
>
> 0
>
> 1087.12
>
> *Row12*
>
> 1200
>
> 0
>
> *Row13*
>
> 0
>
> 1091
>
> *Row14*
>
> 0
>
> 0
>
>
>
> I want a formula that calculates the maximum value between two rows in
> ColumnA where the first row is given but the last row is dynamic, depending
> on whether the value in ColumnB is larger than 0
>
> So for example I want the maximum value between A3 and the first row in
> column A where there is value larger than 0 in column B, which in the table
> above would be row 10…..so the maximum value between A3:A10…. The formula
> should be =MAX(A3: ??)
>
Thu Oct 23, 2014 7:32 pm (PDT) . Posted by:
"Randy Harmelink" rharmelink
It just occurred to me I worked on something similar -- it was creating P&F
charts from Yahoo historical data. That's pretty much the same process --
"X" values continue until a reversal, then "O" values continue until a
reversal, etc.
Some of the items on the Internet related to creating P&F charts may be of
help. For example:
http://www.gummy-stuff.org/PandF-charts.htm
Based on that and other information I had available, I had created my own
P&F workbook using RCHGetYahooHistory() to grab the historical quotes. But
that was about 7 years ago. :)
On Tue, Oct 21, 2014 at 5:18 PM, steven_rourk@...wrote:
> An Excel related question, not Addin related... sorry, dont know any
> where else to ask.
>
> So Im trying to set up an Excel sheet where I have a column with the daily
> closing price of S&P500 for the last 10 years. I want to determine all the
> market corrections (5% ups and downs from the peaks and the bottoms) with a
> moving formula. The formula should do the following:
>
> 1. validate a 5% or more move from a starting point to validate a trend
>
> 2. if for example an upward trend is validated then it has to determine
> which is the top, as this will be the new starting point to determine a
> downward trend.
>
> 3. With the new starting point validate a 5% move downward to validate
> the trend, once validated, determine which is the bottom,
>
> 4. Calculate the % difference between the top and bottom
>
> 5. Etc
>
> Its easy with a chart, you just have to look at the ups and downs, and
> determine whether there has been a larger than 5% move up or down to
> validate the trend. But Im having problems determining the right formula to
> use in Excel in order to do this automatically. Im attaching the
> spreadsheet. As you can see I started doing it manually (column F and G).
> You can see I´ve been playing around with different formulas without luck
> (columns H, I, J and K). Any help of suggestion will be much appreciated.
>
>
>
charts from Yahoo historical data. That's pretty much the same process --
"X" values continue until a reversal, then "O" values continue until a
reversal, etc.
Some of the items on the Internet related to creating P&F charts may be of
help. For example:
http://www.gummy-stuff.org/PandF-charts.htm
Based on that and other information I had available, I had created my own
P&F workbook using RCHGetYahooHistory(
that was about 7 years ago. :)
On Tue, Oct 21, 2014 at 5:18 PM, steven_rourk@
> An Excel related question, not Addin related... sorry, dont know any
> where else to ask.
>
> So Im trying to set up an Excel sheet where I have a column with the daily
> closing price of S&P500 for the last 10 years. I want to determine all the
> market corrections (5% ups and downs from the peaks and the bottoms) with a
> moving formula. The formula should do the following:
>
> 1. validate a 5% or more move from a starting point to validate a trend
>
> 2. if for example an upward trend is validated then it has to determine
> which is the top, as this will be the new starting point to determine a
> downward trend.
>
> 3. With the new starting point validate a 5% move downward to validate
> the trend, once validated, determine which is the bottom,
>
> 4. Calculate the % difference between the top and bottom
>
> 5. Etc
>
> Its easy with a chart, you just have to look at the ups and downs, and
> determine whether there has been a larger than 5% move up or down to
> validate the trend. But Im having problems determining the right formula to
> use in Excel in order to do this automatically. Im attaching the
> spreadsheet. As you can see I started doing it manually (column F and G).
> You can see I´ve been playing around with different formulas without luck
> (columns H, I, J and K). Any help of suggestion will be much appreciated.
>
>
>
Fri Oct 24, 2014 12:19 am (PDT) . Posted by:
tzewei_79
yah, the page is different now. I can't seems to work with my original excel now other than creating a new one from scratch.... Was there a summary on how we can extract from other sites? I lost the link.
Fri Oct 24, 2014 1:02 am (PDT) . Posted by:
"Folonari Darilo" folonari05
HI, i've a problem with function RCHGetElementNumber and all data reutersRat only for ticker KORS.
For example when i use RCHGetElementNumber (KORS;731)or (kors;13626) etc. it returns me an "error". Only for KORS (?!?).
As usual, thanks for your help
For example when i use RCHGetElementNumber (KORS;731)or (kors;13626) etc. it returns me an "error"
As usual, thanks for your help
For the Add-in, Documentation, Templates, Tips and FAQs, visit http://ogres-crypt.com/SMF
Tidak ada komentar:
Posting Komentar