Data Feed Editing

[nixpuri]

Hi guys!

My problem is pretty basic but I can't figure it out.

I have a spreadsheet with a live data feed (Column O, Row 7) from the Dow Jones Index, using the feed title: DIA1716R199-US.
To break that title down:
DIA = Dow Jones Index
1716= June 1, 2017
R199 = Strike Price of 199

Now, what I have in another column (Column F, Row 7) is the strike price, (199), which I want to be the primary manipulator cell. I want to be able to change the strike price, from for example 199 to 201, and for it to automatically alter the DIA1716R199-US to alternatively say DIA1716R201-US.

Literally all I want to do is be able to change the strike price in Column F, and for that to change the data feed title in O7, which will then in turn change all the rest of my spreadsheet's numbers, because they're all based off of that data feed.

Please help someone!

When I try to change the title manually, by saying something like "DIA1716R(=VALUE (F7))-US", it says error and it won't work.

Thanks!

Nix

 

[63falcondude]

When I try to change the title manually, by saying something like "DIA1716R(=VALUE (F7))-US", it says error and it won't work.

Try ="DIA1716R"&F7&"-US"

 

[nixpuri]

Try ="DIA1716R"&F7&"-US"

Unfortunately no luck in the impacted cells it now just says #VALUE, but it's a step closer than my attempt, which wouldn't even allow the formula to exist.

 

[63falcondude]

Unfortunately no luck in the impacted cells it now just says #VALUE, but it's a step closer than my attempt, which wouldn't even allow the formula to exist.

I don't the first thing about data feeds but if you have say 199 in F7, then ="DIA1716R"&F7&"-US" will result in DIA1716R199-US.

The question here would be whether or not you can use formulas in feed titles. That, I do not know.

 

[nixpuri]

I don't the first thing about data feeds but if you have say 199 in F7, then ="DIA1716R"&F7&"-US" will result in DIA1716R199-US.

The question here would be whether or not you can use formulas in feed titles. That, I do not know.

Wow I completely thought that when you said "Try = DIA1716...etc." you meant to input everything after the equals sign, but without the equals sign included.
I tried it WITH the equals sign this time and it worked! Thank you so much!!!!!

I guess now we know, data feeds can be altered with formulae, so long as the formulae are inputted correctly.

 

[63falcondude]

Wow I completely thought that when you said "Try = DIA1716...etc." you meant to input everything after the equals sign, but without the equals sign included.
I tried it WITH the equals sign this time and it worked! Thank you so much!!!!!

I guess now we know, data feeds can be altered with formulae, so long as the formulae are inputted correctly.

Ooh okay good, I should have specified that it is a formula that you want to enter (thus including the equal sign).

I'm glad that you got it sorted out. Happy to help!

 

[63falcondude]

Hey man! You were so helpful with my previous question that I figured you would be the best bet to help me figure out this next conundrum I've got.
So, my manipulatable data cell this time is E3, and all it is is the Month input (ie. May, June, December, September, or January). Those are the only five months I would be typing in, and they have specific codes attached to the formulae I previously used.
An example formula I'm using again is: DIA1716R199-US, in O7.

The R actually stands, somehow, for June.
The others are as follows:

May = Q
June = R
December = X
September = U
January = A

Basically, what I want to achieve is, in E3, I want to type one of the five months, and in doing so, it will impact the formula in O7. For example, currently "DIA1716R199-US" is in June, but if I change E3 to January, I want the formula "DIA1716R199-US" in O7 to adjust to "DIA1716A199-US" automatically.

Pretty straight forward, but I know there's more to it than it looks.

Can you please help me again?

Thank you so much.

Nix

In the future, please ask all of your questions publicly on the forum instead of in a PM.

That being said, you can use a formula such as
="DIA1716"&IF(E3="May","Q",IF(E3="June","R",IF(E3="December","X",IF(E3="September","U","A"))))&"199-US"
in O7

or you can build a lookup table and use a nicer formula like this:

Excel 2010
	E	F	G	H	I	J	K	L	M	N	O
1			Month	Code
2			May	Q
3	June		June	R
4			December	X
5			September	U
6			January	A
7											DIA1716R199-US
Sheet1

Cell Formulas
Range	Formula
O7	="DIA1716"&INDEX(H2:H6,MATCH(E3,G2:G6,0))&"199-US"

 

[nixpuri]

Thanks!

So this works well, now my problem is combining it with the first thread's formula, ="DIA1716R"&F7&"-US".
I thought I could just do ="DIA1716"&INDEX(H2:H6,MATCH(E3,G2:G6,0))&"&F7&"-US" but that doesn't work.

 

[nixpuri]

Nevermind, I got it!