Excel Question

[Smack]

OK - You Excel Whizzes... I need your assistance.

I have 2 separate Worksheets within 1 file.

Worksheet 1 has raw data with 3 cells.

Worksheet 2 has this same data as these 3 cells in Worksheet 1 (via links)

Worksheet 1 is updated daily with different data in these same 3 cells.

I want Worksheet 2 to retain the previous 3 cells of data (from yesterday) and include today's new data of 3 cells in a new set of cells.

I want Worksheet to retain all daily data as time goes by.

How do I achieve this task?

Smack

 

[pixel]

OK - You Excel Whizzes... I need your assistance.

I have 2 separate Worksheets within 1 file.

Worksheet 1 has raw data with 3 cells.

Worksheet 2 has this same data as these 3 cells in Worksheet 1 (via links)

Worksheet 1 is updated daily with different data in these same 3 cells.

I want Worksheet 2 to retain the previous 3 cells of data (from yesterday) and include today's new data of 3 cells in a new set of cells.

I want Worksheet to retain all daily data as time goes by.

How do I achieve this task?

Smack

You have two options:

A manual one: Every evening, you open the spreadsheet, copy the 3 cells from sheet1 and "Paste Special" only the values into the next free area of sheet2. If you wish, then you can add the date above (or to the left) of the new set. Don't forget to save the workbook when you're finished.

Or you write a macro that does the same automatically. You'll still have to open the spreadsheet and activate the macro once a day.

I only write a macro for something that simple when I want to collect large datasets over time, or when I collect the same data for lots of different stocks. e.g. dividend history and forecasts for all dividend paying stocks in the All Ords.
Example: http://rettmer.com.au/TrinityHome/Services/index.htm#_Prev6

 

[Smack]

THanks Pixel.

I am currently using the 'paste special' function in a manual sense, but this needs to be done 10 times each day as I have 10 different WOrksheets outputting their 3 cells of unique data.

it would be nice to have this done automatically via macro, but there must be a standard excel function that will enable linked cells (into Wksht 2). It is hard to explain what I want.

Smack

 

[McLovin]

it would be nice to have this done automatically via macro, but there must be a standard excel function that will enable linked cells (into Wksht 2). It is hard to explain what I want.

Smack

As I understand:

1) You have data in ws1 (3 cells).

2) That data then gets copied into ws2

3) You want to automate copying the data from ws1 to ws2, such that the new data will go to the next available free row in ws2 without erasing previous data.

If that's the case then you will need a macro.

Why do you need to paste special instead of just paste?

 

[pixel]

Why do you need to paste special instead of just paste?

The 3 cells in sheet1 will - presumably - contain a formula or function call that obtains the data from an external source. If you copy-paste, the formula will be transferred into sheet2. So you would end up with a spreadsheet full of identical formulae, which, on recalculation, would fill sheet2 with a bunch of identical sets of data triplets.

PS for Smack: If you don't have experience with writing macros, as your question suggests, you may need "professional" help. VBA skills aren't something you simply "pick up" from a Forum post.

 

[McLovin]

The 3 cells in sheet1 will - presumably - contain a formula or function call that obtains the data from an external source. If you copy-paste, the formula will be transferred into sheet2. So you would end up with a spreadsheet full of identical formulae, which, on recalculation, would fill sheet2 with a bunch of identical sets of data triplets.

Interesting. I only asked because in the OP smack said ws1 contained raw data, which led me to believe it was just that. Perhaps he can clarify.

PS for Smack: If you don't have experience with writing macros, as your question suggests, you may need "professional" help. VBA skills aren't something you simply "pick up" from a Forum post.

True that. Although the macro recorder will do 90% of what smack is wanting to do, the last 10% is the most important, ie getting the range properly defined is ws2 so you don't constantly overwrite your data.

VBA isn't difficult to learn, you just need to start with enough hair so that by the time you have finished debugging your first few macros you still have some left!

 

[cudderbean]

OK - You Excel Whizzes... I need your assistance.

I have 2 separate Worksheets within 1 file.

Worksheet 1 has raw data with 3 cells.

Worksheet 2 has this same data as these 3 cells in Worksheet 1 (via links)

Worksheet 1 is updated daily with different data in these same 3 cells.

I want Worksheet 2 to retain the previous 3 cells of data (from yesterday) and include today's new data of 3 cells in a new set of cells.

I want Worksheet to retain all daily data as time goes by.

How do I achieve this task?

Smack

I could do it for you in a jiffy with XL4 but that's no use to you. When Visual Basic was introduced as standard in later versions of Excel mid 90s, I couldn't be bothered learning it. It was like swapping to Mandarin when you already knew Japanese...and if it ain't broke don't fix it.

My past is catching up with me now. Windows XP accepted XL4 macro language, but Windows 7 doesn't, although I believe Windows 7 Professional and Win 7 Ultimate will run dual Operating systems XP and Win7. I will try that soon, but atm I have to run 2 puters.

Get info from the net how best to learn the latest Visual Basic such as The Complete Idiot's Guide to Visual Basic etc. You'll have fun learning and be amazed at the power of macros handling repetitve tasks. It will also help you get your head around other coding in many stock market software packages such as Bullcharts, Amibroker,Tradestation and Metastock.

In the meantime,if you have to do it manually, cut down on the number of codes you have to update. If you see anything unusual in the market leaders in each sector, take a closer look at some of the other companies in that sector.

Good luck, mate.

 

[pixel]

VBA isn't difficult to learn, you just need to start with enough hair so that by the time you have finished debugging your first few macros you still have some left!

Roger that
I made the same experience a couple of decades ago when I got stuck into Macro-4 language. By the time I could make that sit up and beg, I didn't have enough hair left to switch to VBA. Luckily for me, XLM's are still supported; so I can continue to develop even under Office 2010.

PS for cudderbean: Ours just crossed. What kind of problem did you encounter under Win7? I keep developing in Macro4 and it's all working perfectly. I'm using 64bit Win 7 "Home Premium"

 

[cudderbean]

Roger that
I made the same experience a couple of decades ago when I got stuck into Macro-4 language. By the time I could make that sit up and beg, I didn't have enough hair left to switch to VBA. Luckily for me, XLM's are still supported; so I can continue to develop even under Office 2010.

PS for cudderbean: Ours just crossed. What kind of problem did you encounter under Win7? I keep developing in Macro4 and it's all working perfectly. I'm using 64bit Win 7 "Home Premium"

Well, that's very encouraging, Pixel, thank you. Maybe I gave up too soon on my 64bit Win7 Home Premium. My Excel 4 is 16 bit software. I can't recall what the exact problem was now, other than not being able to get it to run properly. Did you load the XL4 software from an original CD? My originals are a set of about 12 floppies, which I had zipped together for convenience. Maybe I have missed some sort of ini or dll file.

I can't retry it at the moment, since I've had an accidental spillage on my Win7 laptop... I can fudge a bootup, but anything elaborate involving the keyboard, I'm stuffed. I'm using XP on my backup Acer Aspireone (tiny RAM) atm. I'm in NZ atm visiting rellies. When I get back to Thailand next month, the blokes at the local IT centre will hopefully have my Win7 laptop up and running again. I'll certainly give XL4 another go then...it's wonderful being able to copy a live 1,000 code watchlist from a trading platform directly into an XL macro for instant analysis on the same machine.

Thanks for your help.

 

[pixel]

My Excel 4 is 16 bit software.

methinks that's your problem.
Get yourself a Home and Student pack of Office 2010. That'll work.
Shop around - Dick Smith may not be the cheapest. I bought my copy - original Microsoft license for up to 3 users - from my IT supplier, where I also bought the ASUS desktop.

 

[Koeln]

The 3 cells in sheet1 will - presumably - contain a formula or function call that obtains the data from an external source. If you copy-paste, the formula will be transferred into sheet2. So you would end up with a spreadsheet full of identical formulae, which, on recalculation, would fill sheet2 with a bunch of identical sets of data triplets.

PS for Smack: If you don't have experience with writing macros, as your question suggests, you may need "professional" help. VBA skills aren't something you simply "pick up" from a Forum post.

Smak: Do not let this discourage you. Here a macro that I recorded which does exactly the job of inserting in line 1 in the second sheet and pushes all existing rows down. It even removes formulars in sheet 2 if there were any in sheet 1. Use the macro recorder to create one, go to edit and paste/overwrite the following code if it did not work for you.
You see that it took me 7 tries to make it work I entered some comment lines beginning with ' to explain what it does.

Sub Macro7()
'
' Macro7 Macro
'
' Keyboard Shortcut: Ctrl+l
'
'Copy from Sheet 1
Sheets("Sheet1").Select
Range("A1:C1").Select
Selection.Copy
' Paste in Sheet 2
Sheets("Sheet2").Select
Rows("1:1").Select
Selection.Insert Shift:=xlDown
'Select first 3 values in sheet 2 and copy/paste special to remove any formulas that were in sheet 1)
'Not needed if there were no forumlas in sheet 1
Range("A1:C1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
'Last line is needed
Application.CutCopyMode = False
End Sub

 

[McLovin]

Hi Koeln

I'm guessing you're relatively new to VBA because you are doing what all newbies do and using the select command. There's nothing wrong with doing it that way (although you can run into some difficulty with more complex scripts) but you'll find that it's really not that efficient.

Something like...

Sub Copy_Paste ()
Dim cl As Range
   Set cl = Sheets("Sheet1").Range("a1")
   cl.EntireRow.Copy Destination:=Sheets("Sheet2").Range("a65000").End(xlUp).Offset(1, 0)
End

Will do exactly what you are trying to do.

It's important to try and keep your coding as tight as possible. Using the select command doesn't do this.

 

[Smack]

thanks gents.

will have a play....

Smack

 

[Gringotts Bank]

I wonder if someone would mind excelling this recent history of pair trades and telling me if it's in profit or loss for the period shown. I think if I tried this manually I'd go cross-eyed! Let me know if the format is no good.

Thanks!


Date,Type,LeftCode,LC,RightCode,RC,Portfolio
2/2/2012,Exit,DBB,20.72,GDX,57.47,ETF
2/2/2012,Exit,V,106.06,MA,381.57,STOCKS
2/1/2012,Sell/Buy,AOL,17.76,YHOO,15.73,STOCKS
1/31/2012,Sell/Buy,GBPUSD.FXCM,1.576,USDJPY.FXCM,76.25,FOREX
1/30/2012,Buy/Sell,EWU,16.57,EWW,58.18,ETF
1/27/2012,Buy/Sell,AUDNZD.FXCM,1.2917,EURUSD.FXCM,1.3213,FOREX
1/27/2012,Sell/Buy,EURUSD.FXCM,1.3213,USDJPY.FXCM,76.653,FOREX
1/27/2012,Buy/Sell,AUDCHF.FXCM,0.9723,NZDUSD.FXCM,0.8244,FOREX
1/27/2012,Sell/Buy,EWS,12.42,EWT,12.62,ETF
1/24/2012,Sell/Buy,T,30.09,VZ,37.79,STOCKS
1/24/2012,Buy/Sell,EWA,22.86,EWY,57.49,ETF
1/19/2012,Buy/Sell,FCG,17.38,XME,53.11,ETF
1/18/2012,Exit,GLD,161.6,HYG,89.45,ETF
1/18/2012,Exit,CHFJPY.FXCM,81.726,GBPUSD.FXCM,1.5435,FOREX
1/18/2012,Exit,EBAY,30.34,AMZN,189.44,STOCKS
1/17/2012,Sell/Buy,DBB,20.34,FCG,17.38,ETF
1/17/2012,Sell/Buy,DBB,20.34,GDX,53.18,ETF
1/13/2012,Exit,EWC,27.13,EWS,11.49,ETF
1/12/2012,Exit,GBPCHF.FXCM,1.4459,USDJPY.FXCM,76.738,FOREX
1/11/2012,Buy/Sell,FCG,18.08,IYM,68.55,ETF
1/11/2012,Buy/Sell,FCG,18.08,XLB,35.835,ETF
1/11/2012,Buy/Sell,CADJPY.FXCM,75.336,NZDJPY.FXCM,61.231,FOREX
1/11/2012,Buy/Sell,GBPUSD.FXCM,1.5326,NZDJPY.FXCM,61.231,FOREX
1/11/2012,Buy/Sell,FCG,18.08,XLB,35.835,ETF
1/11/2012,Buy/Sell,FCG,18.08,IYM,68.55,ETF
1/11/2012,Exit,EPP,40.52,EWU,16.29,ETF
1/11/2012,Exit,EWA,22.34,EWU,16.29,ETF
1/11/2012,Buy/Sell,CADJPY.FXCM,75.336,NZDJPY.FXCM,61.231,FOREX
1/11/2012,Buy/Sell,GBPUSD.FXCM,1.5326,NZDJPY.FXCM,61.231,FOREX
1/11/2012,Buy/Sell,FCG,18.08,IYM,68.55,ETF
1/10/2012,Exit,LQD,113.7744,PBW,5.4015,ETF
1/10/2012,Exit,DBB,19.4996,HYG,89.33,ETF
1/5/2012,Sell/Buy,V,101.91,MA,352.41,STOCKS
1/4/2012,Buy/Sell,CHFJPY.FXCM,81.379,GBPUSD.FXCM,1.5635,FOREX
1/4/2012,Sell/Buy,GBPCHF.FXCM,1.4699,USDJPY.FXCM,76.693,FOREX
1/3/2012,Exit,FCG,18.69,USO,39.43,ETF
1/2/2012,Buy/Sell,CADJPY.FXCM,75.334,NZDUSD.FXCM,0.7781,FOREX
12/30/2011,Sell/Buy,EWC,26.6,EWS,10.83,ETF
12/29/2011,Buy/Sell,GLD,149.39,HYG,89.3871,ETF
12/28/2011,Buy/Sell,EWA,21.2,EWU,15.94,ETF
12/28/2011,Buy/Sell,EPP,38.664,EWU,15.94,ETF

 

[skyQuake]

I wonder if someone would mind excelling this recent history of pair trades and telling me if it's in profit or loss for the period shown. I think if I tried this manually I'd go cross-eyed! Let me know if the format is no good.

Thanks!

Clean up your data! Theres orphan trades, open positions, and duplicates.

The few closed trades look good though.View attachment 45965

 

[Gringotts Bank]

Thanks sky. Once it was cleaned up, there wasn't a lot left, so I just checked them manually.

 

[Gringotts Bank]

Is anyone able to write the following for me please?

If the word in C1 = word in any row of column 1, then note the row that it occurs in (row x), and return Bx.

VLOOKUP has me stumped.

 

[pixel]

Is C1 short for R1C3?
What is supposed to happen if column 1 contains the value in more than one places?
Are the values in column 1 sorted? That will determine whether you use VLOOKUP or some FIND formula.
How many rows are there in the first 2 columns?

one solution (under the suitable set of assumptions) would be -
=VLOOKUP(C1,A1:B1000,2) to be typed into any cell outside the leftmost two columns.

I assumed 1000 rows in the table, contents sorted in ascending order, and you change the value/name in cell C1 manually. The contents of whichever cell has the vlookup formula will then change to the contents 1 to the right of the found cell in column 1.
If there is potentially no match in all of column 2, you will need some more nested if clauses to cater for the "N/A" case.

 

[Gringotts Bank]

Is C1 short for R1C3?
What is supposed to happen if column 1 contains the value in more than one places?
Are the values in column 1 sorted? That will determine whether you use VLOOKUP or some FIND formula.
How many rows are there in the first 2 columns?

one solution (under the suitable set of assumptions) would be -
=VLOOKUP(C1,A1:B1000,2) to be typed into any cell outside the leftmost two columns.

I assumed 1000 rows in the table, contents sorted in ascending order, and you change the value/name in cell C1 manually. The contents of whichever cell has the vlookup formula will then change to the contents 1 to the right of the found cell in column 1.
If there is potentially no match in all of column 2, you will need some more nested if clauses to cater for the "N/A" case.

Very good of you pixel, thanks.
C1 is R1C3. Column 1 can be sorted alphabetically if need be. 1000 rows - I can work with that. Cheers.

 

[pixel]

Very good of you pixel, thanks.
C1 is R1C3. Column 1 can be sorted alphabetically if need be. 1000 rows - I can work with that. Cheers.

No problem, GB
Here is the conditional version that gives you n/a if your search name is not in the list at all.
=IF(R1C3=VLOOKUP(R1C3,R1C1:R1000C2,1),VLOOKUP(R1C3,R1C1:R1000C2,2),NA())
The table in the leftmost columns must be sorted in ascending order. Of course, you can replace the 1000 by the row number of the last table entry.
(I prefer R1C1 notation)