12 New Messages
Digest #2434
Messages
Thu Dec 13, 2012 6:16 pm (PST) . Posted by:
"Stock Jock" stockjocktrader
Randy,
Have you or anyone else posted any video tutorials or examples
regarding your addin on the YouTube website?
Thu Dec 13, 2012 10:15 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Not that I'm aware of. I don't even own a still shots camera. I've never
done anything with video. :)
On Thu, Dec 13, 2012 at 7:16 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
> Have you or anyone else posted any video tutorials or
> examples regarding your addin on the YouTube website?
>
done anything with video. :)
On Thu, Dec 13, 2012 at 7:16 PM, Stock Jock <stockjocktrader@
> Have you or anyone else posted any video tutorials or
> examples regarding your addin on the YouTube website?
>
Thu Dec 13, 2012 6:21 pm (PST) . Posted by:
"Stock Jock" stockjocktrader
Randy,
If I happen to need to use MS Access, do you know of any stock
market addins like yours that will work with it?
Thu Dec 13, 2012 10:18 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
It's been well over a decade since I've used Access. I used it for a horse
racing database back in the 90's. Or maybe that was LOTUS Symphony.
But Acess uses VBA in more or less the same way EXCEL does, right?
I would think you could set up the add-in as a reference library, then use
its function from your own VBA to fill fields and load rows into Access.
On Thu, Dec 13, 2012 at 7:21 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
> If I happen to need to use MS Access, do you know of any stock market
> addins like yours that will work with it?
>
racing database back in the 90's. Or maybe that was LOTUS Symphony.
But Acess uses VBA in more or less the same way EXCEL does, right?
I would think you could set up the add-in as a reference library, then use
its function from your own VBA to fill fields and load rows into Access.
On Thu, Dec 13, 2012 at 7:21 PM, Stock Jock <stockjocktrader@
> If I happen to need to use MS Access, do you know of any stock market
> addins like yours that will work with it?
>
Thu Dec 13, 2012 10:37 pm (PST) . Posted by:
"Stock Jock" stockjocktrader
I used to use macros in Symphony. I thought that was great for what I
needed at the time. It was the first, that I know of, integrated office
system a few years before Microsoft and Corel came out with theirs. I
think Corel purchased the Lotus company. Anyway, I have a collection of
modules for Access and some for Excel that I use often and I'm thinking
of putting them into an addin or a reference file. How are addins made?
Is there some sort of export function from Access or Excel?
------------
In smf_addin@yahoogrou
It's been well over a decade since I've used Access. I used it for a
horse racing database back in the 90's. Or maybe that was LOTUS
Symphony.
But Acess uses VBA in more or less the same way EXCEL does, right?
I would think you could set up the add-in as a reference library, then
use its function from your own VBA to fill fields and load rows into
Access.
Thu Dec 13, 2012 10:55 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
I used the information I found here:
http://www.bettersolutions.com/excel/ECA723/NI647213531.htm
I think all I did was create the VBA project in a normal workbook and save
it as an XLA file -- and that removes the worksheet(s) from the file and
just keeps the VBA project.
I used to have a personal.xls file, but saved that as an XLA file as well,
so that I could just use it as an add-in.
On Thu, Dec 13, 2012 at 11:37 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
> I used to use macros in Symphony. I thought that was great for what I
> needed at the time. It was the first, that I know of, integrated office
> system a few years before Microsoft and Corel came out with theirs. I
> think Corel purchased the Lotus company. Anyway, I have a collection of
> modules for Access and some for Excel that I use often and I'm thinking of
> putting them into an addin or a reference file. How are addins made? Is
> there some sort of export function from Access or Excel?
>
http://www.betterso
I think all I did was create the VBA project in a normal workbook and save
it as an XLA file -- and that removes the worksheet(s) from the file and
just keeps the VBA project.
I used to have a personal.xls file, but saved that as an XLA file as well,
so that I could just use it as an add-in.
On Thu, Dec 13, 2012 at 11:37 PM, Stock Jock <stockjocktrader@
> I used to use macros in Symphony. I thought that was great for what I
> needed at the time. It was the first, that I know of, integrated office
> system a few years before Microsoft and Corel came out with theirs. I
> think Corel purchased the Lotus company. Anyway, I have a collection of
> modules for Access and some for Excel that I use often and I'm thinking of
> putting them into an addin or a reference file. How are addins made? Is
> there some sort of export function from Access or Excel?
>
Thu Dec 13, 2012 9:22 pm (PST) . Posted by:
"Chee Kean C" cckean
Hi Randy
I was just wondering, other than the SMF Addin that you have created, what other software, tools or excel addin that you have designed or are working on now?
Regards
Kenny
I was just wondering, other than the SMF Addin that you have created, what other software, tools or excel addin that you have designed or are working on now?
Regards
Kenny
Thu Dec 13, 2012 10:11 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
Nothing really. I'm retired. You had something in mind? :)
The add-in is a result of my frustration with the EXCEL Web Query in older
versions of EXCEL. It used to cause EXCEL to crash when you used it too
often, because of "memory leaks". It also only grabbed whole tables, and I
wanted something that could grab specific pieces of information.
The RCHGetTableCell() function actually came about as a result of my seeing
patterns in how I was extracting specific pieces of data. I realized I was
copying code and just changing a few things here and there for a new data
item, so I figured why not make a subroutine do all the work and pass those
few things as parameters.
I'm just sharing what started out as something I wrote for my own use.
That's why the old functions use the RCH prefix and the new ones are smf --
I developed the RCH ones as functions in my personal macro library, far
before any add-in existed.
I've been developing/supporting software for nearly 50 years now, so I'm
pretty much just doing what I've always done. Just on an amateur basis
instead of a professional one. It would take a lot to bring the add-in up
to a professional level. But, to tell the truth, I'm shocked there isn't a
commercial product that does what the add-in does, even if one of the data
providers had it as a portal to their own data.
Come to think of it, I guess that's what FRED has done for economic data.
On Thu, Dec 13, 2012 at 7:17 PM, Chee Kean C <cckean@yahoo.com > wrote:
> Hi Randy
>
> I was just wondering, other than the SMF Addin that you have created, what
> other software, tools or excel addin that you have designed or are working
> on now?
>
> Regards
> Kenny
>
The add-in is a result of my frustration with the EXCEL Web Query in older
versions of EXCEL. It used to cause EXCEL to crash when you used it too
often, because of "memory leaks". It also only grabbed whole tables, and I
wanted something that could grab specific pieces of information.
The RCHGetTableCell(
patterns in how I was extracting specific pieces of data. I realized I was
copying code and just changing a few things here and there for a new data
item, so I figured why not make a subroutine do all the work and pass those
few things as parameters.
I'm just sharing what started out as something I wrote for my own use.
That's why the old functions use the RCH prefix and the new ones are smf --
I developed the RCH ones as functions in my personal macro library, far
before any add-in existed.
I've been developing/supporti
pretty much just doing what I've always done. Just on an amateur basis
instead of a professional one. It would take a lot to bring the add-in up
to a professional level. But, to tell the truth, I'm shocked there isn't a
commercial product that does what the add-in does, even if one of the data
providers had it as a portal to their own data.
Come to think of it, I guess that's what FRED has done for economic data.
On Thu, Dec 13, 2012 at 7:17 PM, Chee Kean C <cckean@yahoo.
> Hi Randy
>
> I was just wondering, other than the SMF Addin that you have created, what
> other software, tools or excel addin that you have designed or are working
> on now?
>
> Regards
> Kenny
>
Thu Dec 13, 2012 10:13 pm (PST) . Posted by:
"Randy Harmelink" rharmelink
You should be able to use add-in functions to collect data on nearly
anything that an EXCEL Web Query can bring in. It just is more exact about
what it brings in.
I've used it on NetFlix and IMDB, and I've had other people pull in data
from sports and real estate sites.
On Thu, Dec 13, 2012 at 7:09 PM, Stock Jock <stockjocktrader@yahoo.com >wrote:
>
> In looking at your code and the reference files, am I
> correct to think that the addin can get data from more sources than just
> from Yahoo Finance and all of them are EOD data only?
>
anything that an EXCEL Web Query can bring in. It just is more exact about
what it brings in.
I've used it on NetFlix and IMDB, and I've had other people pull in data
from sports and real estate sites.
On Thu, Dec 13, 2012 at 7:09 PM, Stock Jock <stockjocktrader@
>
> In looking at your code and the reference files, am I
> correct to think that the addin can get data from more sources than just
> from Yahoo Finance and all of them are EOD data only?
>
Fri Dec 14, 2012 12:15 am (PST) . Posted by:
"randc_1yh" randc_1yh
Hi Randy,
Have been using an Excel macro I purchased long ago to import real time stock data into an Excel spreadsheet. It takes a "snapshot" of Real-Time stock data (OHLV etc.), in user-definable set time increments (i.e., every 10 seconds, 30 seconds, 60 seconds, etc.) for 1200+ stock tickers at a time.
Problem is, it runs only under WindowsXP, and I am upgrading computers and need something similar to run under Vista or newer Windows OS.
Apparently Yahoo! Finanace has two databases, one for end-of-day, and one for real time. (I pay a monthly fee for real time access to Yahoo! stock data.)
Simple as this, I want to put a random 1200+ stock symbols vertically in Column A, and every 30 seconds import real-time data to update Last, High, Low, Volume, etc. in columns B thru H, can smf_addin pull in real time data like that from Yahoo!?
Have been using an Excel macro I purchased long ago to import real time stock data into an Excel spreadsheet. It takes a "snapshot" of Real-Time stock data (OHLV etc.), in user-definable set time increments (i.e., every 10 seconds, 30 seconds, 60 seconds, etc.) for 1200+ stock tickers at a time.
Problem is, it runs only under WindowsXP, and I am upgrading computers and need something similar to run under Vista or newer Windows OS.
Apparently Yahoo! Finanace has two databases, one for end-of-day, and one for real time. (I pay a monthly fee for real time access to Yahoo! stock data.)
Simple as this, I want to put a random 1200+ stock symbols vertically in Column A, and every 30 seconds import real-time data to update Last, High, Low, Volume, etc. in columns B thru H, can smf_addin pull in real time data like that from Yahoo!?
Fri Dec 14, 2012 12:27 am (PST) . Posted by:
"Randy Harmelink" rharmelink
It's my understanding that the CSV current quotes file that Yahoo provides
gives real time quotes if you are signed up for them. But I've never done
it myself.
In order for that to work for the add-in, you need to log in with either
the EXCEL Web Query process (preferred) or with IE. That should create the
proper security cookie for the XMLHTTP process that the add-in uses to
retrieve data from the Internet. EXCEL/XMLHTTP/IE are all MicroSoft
products and use the same methods to access the Internet. So the add-in
uses IE settings, IE cookies, and IE temporary Internet files. Logging in
with something like FireFox would not create the necessary security cookie
for EXCEL or the add-in to use.
Each RCHGetYahooQuotes() function can get data on up to 200 ticker symbols
(a limit imposed by Yahoo on how many stocks can be loaded into that CSV
file). But you can use six different functions to get 1200 ticker symbols.
You'd need to write your own VBA code to do the timing routine, using VBA's
onTime command. Really, all you'd need to have the routine do is execute
the smfForceRecalculation macro of the add-in (see the LINKS area of the
group for more info on that macro). That would update all add-in functions
you have in the workbook.
Or, you could have your VBA routine load values into the workbook, using
the same functions.
On Fri, Dec 14, 2012 at 1:07 AM, randc_1yh <randcinv@infowest.com > wrote:
>
> Have been using an Excel macro I purchased long ago to import real time
> stock data into an Excel spreadsheet. It takes a "snapshot" of Real-Time
> stock data (OHLV etc.), in user-definable set time increments (i.e., every
> 10 seconds, 30 seconds, 60 seconds, etc.) for 1200+ stock tickers at a time.
>
> Problem is, it runs only under WindowsXP, and I am upgrading computers and
> need something similar to run under Vista or newer Windows OS.
>
> Apparently Yahoo! Finanace has two databases, one for end-of-day, and one
> for real time. (I pay a monthly fee for real time access to Yahoo! stock
> data.)
>
> Simple as this, I want to put a random 1200+ stock symbols vertically in
> Column A, and every 30 seconds import real-time data to update Last, High,
> Low, Volume, etc. in columns B thru H, can smf_addin pull in real time data
> like that from Yahoo!?
>
gives real time quotes if you are signed up for them. But I've never done
it myself.
In order for that to work for the add-in, you need to log in with either
the EXCEL Web Query process (preferred) or with IE. That should create the
proper security cookie for the XMLHTTP process that the add-in uses to
retrieve data from the Internet. EXCEL/XMLHTTP/
products and use the same methods to access the Internet. So the add-in
uses IE settings, IE cookies, and IE temporary Internet files. Logging in
with something like FireFox would not create the necessary security cookie
for EXCEL or the add-in to use.
Each RCHGetYahooQuotes(
(a limit imposed by Yahoo on how many stocks can be loaded into that CSV
file). But you can use six different functions to get 1200 ticker symbols.
You'd need to write your own VBA code to do the timing routine, using VBA's
onTime command. Really, all you'd need to have the routine do is execute
the smfForceRecalculati
group for more info on that macro). That would update all add-in functions
you have in the workbook.
Or, you could have your VBA routine load values into the workbook, using
the same functions.
On Fri, Dec 14, 2012 at 1:07 AM, randc_1yh <randcinv@infowest.
>
> Have been using an Excel macro I purchased long ago to import real time
> stock data into an Excel spreadsheet. It takes a "snapshot" of Real-Time
> stock data (OHLV etc.), in user-definable set time increments (i.e., every
> 10 seconds, 30 seconds, 60 seconds, etc.) for 1200+ stock tickers at a time.
>
> Problem is, it runs only under WindowsXP, and I am upgrading computers and
> need something similar to run under Vista or newer Windows OS.
>
> Apparently Yahoo! Finanace has two databases, one for end-of-day, and one
> for real time. (I pay a monthly fee for real time access to Yahoo! stock
> data.)
>
> Simple as this, I want to put a random 1200+ stock symbols vertically in
> Column A, and every 30 seconds import real-time data to update Last, High,
> Low, Volume, etc. in columns B thru H, can smf_addin pull in real time data
> like that from Yahoo!?
>
Fri Dec 14, 2012 3:39 am (PST) . Posted by:
"andrewhills97" andrewhills97
Hi Randy,
How do I change the smfGetOptionQuotes file to retrieve Put prices instead of Call option prices? I have tried replacing "Call" with "Put" but didnt work.
Thanks
Andrew
--- In smf_addin@yahoogroups.com , "Randy H" <rharmelink@...> wrote:
>
> On Wed, Dec 12, 2012 at 9:07 PM, andrewhills97 <andrewhills97@...
> <mailto:andrewhills97@...> > wrote:
> Hi Randy, I have read Links 2.2 and played around with
> 'smfGetOptionQuotes' but am struggling.
> I am trying to populate a spreadsheet with Call Option Prices with the
> following row and column inputs:
> - Down column A, there are 10 ITM and 10 OTM Exercise Prices,
> - Across Row 7, next 10 expiration date
> - Then within these ranges, the Call Option prices.
>
> Re: down column A (ie Exercise prices), i enter
> "=smfGetOptionStrikes($B$2,$B$5,"Call",$B$3,,H33,1)". B2 is ticker; B5
> is expiry date; B3 is data source; H33 is a number which decreases by
> 2; I dont know what the 1 does. This does not retrieve OTM Exercise
> Price - any help would be great here.
>
> I don't know what your H33 is being used for. That parameter should be
> either 0 or 1, depending on whether you want the function to create
> ticker symbols that smfGetOptionQuotes() can use. It looks like you want
> to drop the H33 reference and just use a 1 there, with no later
> parameters. For example:
>
> =smfGetOptionStrikes(Ticker,Expiry_Date,"Call",Data_Source,1)
>
> However, if you need to vary the expiration dates, you don't want the
> "1" parameter. Drop it to just get the strike prices.
>
> Re: Across Row 7, i enter "=smfGetOptionExpirations($B$2,$B$3)". B2 and
> B3 as above, but this only retrieves the first expiration date....
>
> Unfortunately, the function produces ROWS of output. If you want to
> spread it across columns, you need to retrieve it in a column and then
> transpose it:
>
> Re: filling this table, i have attempted this yet but i was going to try
> "=smfGetOptionQuotes($C24,D$22,,$B$3)". D22 is "l", ie last price; B3
> as above; C24 is the Ticker description. How do I create Ticker from
> results down columnA and Across row 7?
>
> Since you want to vary the expiration date and strike prices for each
> cross-section in the table, you'll need to create the "options ticker
> symbol" for each one. It would be something like:
>
> =smfGetOptionQuotes(Ticker&TEXT(D$22," mm/dd yyyy ")&$C24&"
> Call","l",0,Data_Source)
>
> Where column C contains ONLY strike prices, instead of actual "option
> ticker symbols", because you want to vary the strike price AND the
> expiration date. What you did earlier with the smfGetOptionStrikes()
> function was generating "option ticker symbols" for a specific
> expiration date. That's not what you want if you're filling in a table
> that varies that expiration date. You just want to give that function
> the earliest expiration date, because it's most likely to have the full
> range of available strike prices.
>
How do I change the smfGetOptionQuotes file to retrieve Put prices instead of Call option prices? I have tried replacing "Call" with "Put" but didnt work.
Thanks
Andrew
--- In smf_addin@yahoogrou
>
> On Wed, Dec 12, 2012 at 9:07 PM, andrewhills97 <andrewhills97@
> <mailto:andrewhills
> Hi Randy, I have read Links 2.2 and played around with
> 'smfGetOptionQuotes
> I am trying to populate a spreadsheet with Call Option Prices with the
> following row and column inputs:
> - Down column A, there are 10 ITM and 10 OTM Exercise Prices,
> - Across Row 7, next 10 expiration date
> - Then within these ranges, the Call Option prices.
>
> Re: down column A (ie Exercise prices), i enter
> "=smfGetOptionStrik
> is expiry date; B3 is data source; H33 is a number which decreases by
> 2; I dont know what the 1 does. This does not retrieve OTM Exercise
> Price - any help would be great here.
>
> I don't know what your H33 is being used for. That parameter should be
> either 0 or 1, depending on whether you want the function to create
> ticker symbols that smfGetOptionQuotes(
> to drop the H33 reference and just use a 1 there, with no later
> parameters. For example:
>
> =smfGetOptionStrike
>
> However, if you need to vary the expiration dates, you don't want the
> "1" parameter. Drop it to just get the strike prices.
>
> Re: Across Row 7, i enter "=smfGetOptionExpir
> B3 as above, but this only retrieves the first expiration date....
>
> Unfortunately, the function produces ROWS of output. If you want to
> spread it across columns, you need to retrieve it in a column and then
> transpose it:
>
> Re: filling this table, i have attempted this yet but i was going to try
> "=smfGetOptionQuote
> as above; C24 is the Ticker description. How do I create Ticker from
> results down columnA and Across row 7?
>
> Since you want to vary the expiration date and strike prices for each
> cross-section in the table, you'll need to create the "options ticker
> symbol" for each one. It would be something like:
>
> =smfGetOptionQuotes
> Call","l",0,
>
> Where column C contains ONLY strike prices, instead of actual "option
> ticker symbols", because you want to vary the strike price AND the
> expiration date. What you did earlier with the smfGetOptionStrikes
> function was generating "option ticker symbols" for a specific
> expiration date. That's not what you want if you're filling in a table
> that varies that expiration date. You just want to give that function
> the earliest expiration date, because it's most likely to have the full
> range of available strike prices.
>
Tidak ada komentar:
Posting Komentar