Sorting an Array Formula

[davey11372]

I am trying to do the following:

Array Formula setting.... for stock analysis. The formula syntax is,

=sddeLink|Bars!'SYMBOL,PERIOD,#BARS,[DTOHLCV],{HEADERS},{HH:MM-HH:MM},{FILL}'

Example: =sddeLink|Bars!'IBM,15,1000,DTOHLCV,HEADERS,09:30-16:00'

SYMBOL – Represents the name of the symbol you wish to see.
PERIOD – Use Q for Quarterly, M for Monthly, W for Weekly, D for Daily or for an intraday period just specify the number of minutes.
#BARS – Represents the number of bars to display.
DTOHLCV – Defines which data fields to display and in which order. See below for a list.
D = Date T = Time O = Open H = High L = Low C = Close V = Volume
HEADERS – Use this option to display the names of the fields in columns above the data.
HH:MM-HH:MM – Use this option to return only the bars within the specified time range. Applies to intraday intervals only.
FILL – Use this option to fill blank bars with the last known Close value
sddelink = the ddelink for the source database
Bars = the source database file

Since I have to do this for many stocks/tickers, I have grouped multiple sheets, selected the array area, then copied the formula into the array (so the same formula gets copied into all the grouped sheets - a separate sheet for each stock/symbol).

The SYMBOL field, of the formula, is the only field that changes on each sheet. But this field stays/stayed the same when I copy the formula into multiple sheets together.

So, instead of me editing/typing/inserting the different/new symbol manually on each sheet into its array formula, is it possible to reference the symbol field in the array formila to a cell value (eg. $a$1). I tried using the $A$1 format but it does not work ... i guess something to do with the apostrophe before the symbol text and the complexity of array formulas.

This would allow me to group the sheets, all of which have the array formula with the respective ticker, activate the array (control+shift+enter) just once, and bingo I have the results of the array sorted - with data for each ticker on a separate sheet.

 

[Legacy 98055]

Hi Dave. I don't think you can reference these values from outside your forula though I could be wrong. Everything beyond the exclamation point are open ended arguments. This being the case, it will depend upon the application sddeLink to accept named arguments individually or only as part of the datasource name. In this case, Bars!'SYMBOL. Even so, an easy solution would be to use a few lines of code and a separate worksheet to build your formulas.

For example, list these header from A1 to L1:
DataSource
Symbol
Period
#Bars
Fields
Header
IntraInterval
Fill
DestWorksheet
DestRange

You could then assemble the pieces of your formulas and click a button to apply the arrays to DestWorksheet.DestRange. This oviously could become as dynamic as you wish. Let me know if this is the direction you want to go.

 

[davey11372]

Thanks Tom, for your prompt reply.

It makes sense what you suggest, unfortunately I do not know how to write even the simplest code/script. I was trying to derive/edit something from your stockquotegrabber sheet, but frankly do not know how to go about it. I may be asking too much, but, if it does not take too much time, can you write a script with instructions that I could try, I can wait a few days. I can access the database only at work, and that would not be till Monday.

Alternatively, if it makes sense, and if it is easier.

Lets say I have an index of symbols on sheet1.

I select/paste the array formula for one ticker. Can I then have something similar to the 'Get All' command button on your stockquotegrabber sheet, such that the array formula loops through the index of tickers and downloads/imports the data for each ticker to a separate sheet?

 

[Legacy 98055]

<i>"I select/paste the array formula for one ticker. Can I then have something similar to the 'Get All' command button on your stockquotegrabber sheet, such that the array formula loops through the index of tickers and downloads/imports the data for each ticker to a separate sheet?"</i>

I'm not sure that I understand the above???

For example, list these header from A1 to L1:
DataSource
Symbol
Period
#Bars
Fields
Header
IntraInterval
Fill
DestWorksheet
DestRange

In A2 to L2, as the first formula being assembled, you would write these values to apply your array formula to a worksheet named IBM_TICKER in range A1:G1001
sddeLink
Bars
IBM
D
1000
DTOHLCV
TRUE
09:30-16:00
TRUE
IBM_TICKER
A1:G1001

BTW, what is the 15 for in your formula? I did not see an explanation for that argument.

Just keep in mind that each column represents a piece or an argument in the function. They must be listed in the correct order and I need to know the exact syntax of your function.

 

[davey11372]

The 15 is the period interval/argument, in other words every 15 minutes (could be daily = D, weekly = W, every minute = 1, --------- PERIOD = Use Q for Quarterly, M for Monthly, W for Weekly, D for Daily or for an intraday period just specify the number of minutes).

=sddeLink|Bars!'SYMBOL,PERIOD,#BARS,[DTOHLCV],{HEADERS},{HH:MM-HH:MM},{FILL}'

Example: =sddeLink|Bars!'IBM,15,1000,DTOHLCV,HEADERS,09:30-16:00'

In this example, the ddelink would download data -
from the database filename Bars
for the ticker IBM
for every 15 minute interval, for a 1000 intervals staring from the most recent and going back every 15 minutes for a 1000 times,
data would include - D/date, T/time, O/open price, H=high price, L-low price, C-closing price, V-volume
Headers - Date, time, open, high, low, close, volume
for the active session from 9:30 am to 4 pm (stock market open time)

rather than me doing this one at a time by manually changing the ticker, I want a macro to do it for me.

I don't see a way to attach a file/image here, that would make it a little easier.

 

[Legacy 98055]

Dave. Email me the worksheet with your formulas "built" and I'll throw in the code. I am not going to build this from scratch.

 

[davey11372]

Tom, where do I find your email address? In fact, I did try to email you yesterday, I took a guess at your email address .. don't know if it was the correct one.

Also, when you say, formulas 'built', I am not sure I understand ... but I will send you the worksheet and some documentation.

 

[Legacy 98055]

You can go to my profile for the email address or

tstomnineteenseventy@gmail.com

replace nineteenseventy with 1970