Don't forget to scribe my channel.
If you like this video click the like button and share it to your friends.
Note: Since greater than and less than symbol is not accepted by the youtube in discription. enter the symbol stop "greater than" 550 && stop "less than"955. see video at 9.38Sec for reference
1) First Open google sheet
2) In Column A1 paste the below formula
=IF(A7=A9,"LEAVE",IF(A7=A10,"LEAVE",IF(A7=A11,"LEAVE",IF(A7=A12,"LEAVE",IF(A7=A13,"LEAVE",IF(A7=A14,"LEAVE",IF(A7=A15,"LEAVE",IF(A7=A16,"LEAVE",IF(A7=A17,"LEAVE",IF(A7=A18,"LEAVE",IF(A7=A19,"LEAVE",IF(A7=A20,"LEAVE",IF(A7=A21,"LEAVE",IF(A7=A22,"LEAVE"))))))))))))))
3)In Column A2 paste the below formula
=((A3*60)+A4)
4) In Column A3 paste the below formula
=hour(C1)
5) In Column A4 paste the below formula
=MINUTE(C1)
6 In Column A5 paste the below formula
=SECOND(C1)
7) In Column A6 paste the below text
TODAY DATE
8 In Column A7 paste the below formula
=TODAY()
9) In Column A8 paste the below text
HOLIDAY LIST
10) Collect the Holiday list from NSE web site
Paste the dates in the A9, A10, .... (This step is to pause the script on holidays).
11) In Column B1 paste the below formula
=text(today(),"dddd")
12)In Column C1 paste the below formula
=now()
13) ENTER SYMBOL in Column B2 example 3MINDIA
14) Paste this formula in Column C2 =googlefinance("NSE:" &B2,"price")
15) You will get the Current date price
16) You Can enter as many as stocks you need in the Column B
17) Just click the Column C2 and Drag down till end of the stock list
18) The formula applies automaticaly
19) Now Click Extensions from main bar
20) click Macros
21) Click Record Macro
22) Click any two cells
23) Click save
24) Name the macro as you like
SCRIPT to get DATA in google sheet From 9:35AM to 3:30PM (Monday to Friday)
It will be auto paused on Saturday, Sunday and Holidays(for Holidays based on the list of holidays given in column A)
var spreadsheet = SpreadsheetApp.getActive().getSheetByName('datafromweb')/** name of the sheet*/;
var day=spreadsheet.getRange(1,2).getValue();
var stop=spreadsheet.getRange(2,1).getValue();
var holiday=spreadsheet.getRange(1,1).getValue();
if(holiday !== "LEAVE")
{if(day !== "Saturday" && day !== "Sunday")
{if(stop (put greater than symbol)550 && stop (put less than symbol)955) {
var sheetFrom = SpreadsheetApp.getActive().getSheetByName('datafromweb');
var sheetTo = SpreadsheetApp.getActive().getSheetByName('1hourdata')/** name of the sheet*/;
var values = sheetFrom.getRange(1, 3, sheetFrom.getLastRow(), 1).getValues();
sheetTo.getRange(1,sheetTo.getLastColumn()+1,values.length,1).setValues(values);
/** var delcol = SpreadsheetApp.getActive().getSheetByName('1hourdata');
delcol.getRange('B:B').activate();
delcol.deleteColumns(delcol.getActiveRange().getColumn(), delcol.getActiveRange().getNumColumns());*/
var sheetFromm = SpreadsheetApp.getActive().getSheetByName('1hourdata');
sheetFromm.getRange('B1').activate();
var currentCell = sheetFromm.getCurrentCell();
sheetFromm.getSelection().getNextDataRange(SpreadsheetApp.Direction.NEXT).activate();
currentCell.activateAsCurrentCell();
currentCell = sheetFromm.getCurrentCell();
sheetFromm.getSelection().getNextDataRange(SpreadsheetApp.Direction.DOWN).activate();
currentCell.activateAsCurrentCell();
var sheetToo = SpreadsheetApp.getActive().getSheetByName('Sheet3');
sheetToo.getRange('C2').activate();
sheetFromm.getRange('\'1hourdata\'!B1:ku504').copyTo(sheetToo.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_NORMAL, false);
sheetToo.getRange('C2').activate();
};
};
};
};
6 окт 2024