Stock data manipulation

[dhananjaylegal]

Hello. I have end of day data downloaded and copied into Excel like
this :

Column A Column B Column C Column D
(symbol) (Price) (symbol) (symbol)
(day 1) (day 1) (day 2) (day 2)
Abb 20.25 Aay 45.20
Acc 25.20 Abb 22.25
Adm 10.20 Acc 26.20

What I want done ?

1. You see that there are four unique symbols from both Column A and
Column C.
2. Column A does not contain data for the symbol 'Aay'. Yet, it should
introduce it by comparing its own data with data in Column C.
3. Column B has no data for the symbol Aay. Yet, when the symbol Aay is
reinserted into Column A, Column B has to speak for the price and let
it be 'NA'.
4. Column C and D do not contain the data for the symbol Adm. Column C
should compare itself with Column A. The symbol Adm is unique for
Column A. This has to be introduced in Column C.
5. Column D has no data for the symbol Aay. Yet, when the symbol Adm is
reinserted into Column C, Column D has to speak for the price and let
it be 'NA'.

When complete, the data will look like this :

Column A Column B Column C Column D
(symbol) (Price) (symbol) (symbol)
(day 1) (day 1) (day 2) (day 2)
Aay NA Aay 45.20
Abb 20.25 Abb 22.25
Acc 25.20 Acc 26.20
Adm 10.20 Adm NA

All help greatly appreciated.

DHANANJAY

 

[Daniel CHEN]

DHANANJAY,

Your stock data manipulation is doable and can be automatically consolidated
via VBA coding.
However, it is difficult to explain and need lines of VBA code.

An indirect way is:
1) first list all the unique symbols in a column, say F and H
2) in Column G, use vlookup to find price from column B, if not found
returns NA
3) in Column I, use vlookup to find price from column D, if not found
returns NA

Hope this helps you to start.

--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Excel/VBA Tool & Training Material
==================================