What is the best way to copy past closing prices for a particular day in history of my watch lists to be loaded into a spreadsheet for further analysis?
Best way is using AB analysis and in your case applying Exploration...
Copy below code and apply it to analysis and run it as Exploration. Then copy result list via CTRL+C of your keyboard and insert it to Excel via CTRL+V.
Filter = 1;
AddColumn(C, "Close", 1.2);
Copying results from AB to Excel
If you want to copy particular day then simply set analysis "From-to dates" range to same date (and re-run exploration).
E.g.
Of course you can set From-to date range via code also
// set analysis toolbar's range to "All quotes"
from_date = "2019-07-01 00:00:00";
to_date = "2019-07-02 00:00:00";
dt = DateTime();
Filter = dt >= _DT(from_date) AND dt <= _DT(to_date);
AddColumn(C, "Close", 1.2);
And setting range of analysis to "All Quotes".
3 Likes
Hello, I tried what you recommended but I am still not obtaining the results I want. I feel maybe I am not making myself clear enough.
Let's say in a of particular watch list (DOW 30), I want the closing prices of each 30 stocks in the watch list for 9/3/19 and 9/6/19 . I want the ability to choose any two dates and then export to Excel (that I can do). Can I choose the dates in the Range on the Analysis page and then use the Explore? I will like the results in format displayed below. If the closing prices cannot be listed together then I do not mind running it for the separate dates. Thanks in advance.
Symbol Date Close Date Close
AAPL 9/3/19 --- 9/6/19 ---
AXP 9/3/19 --- 9/6/19 ---
BA
// set analysis toolbar's range to "All quotes"
date1 = "2019-09-03 00:00:00";
date2 = "2019-09-06 00:00:00";
dt = DateTime();
Filter = dt == _DT(date1) OR dt == _DT(date2);
AddColumn(C, "Close", 1.2);
Set Range to "1 recent day(s)" for it to work properly.
// Scan specific date and display Close price
// Convert date to datenum : 3 Sep 2019 = (2019 - 1900) * 10000 + 9 * 100 + 3
// Set the Range to "1 recent day(s)" in the exploration menu
Filter = 0;
Date_num = DateNum();
Close_price = Close;
first_date = (2019-1900) * 10000 + 9 * 100 + 3;
second_date = (2019-1900) * 10000 + 9 * 100 + 6;
// Set price to zero if date not found
first_price = 0;
second_price = 0;
for (i=BarCount - 1; i>=0; i--) {
// Start with the most recent date first
if (Date_num[i] == second_date) {
second_price = Close_price[i];
}
if (Date_num[i] == first_date) {
first_price = Close_price[i];
Filter = 1;
break;
}
}
AddTextColumn("9/3/2019","First Date");
AddColumn(first_price, "First Close");
AddTextColumn("9/6/2019","Second Date");
AddColumn(second_price, "Second Close");
there is zero looping required to get to result. Zero.
/// Scan specific date and display Close price
/// Zero BarCount Looping
/// @link https://forum.amibroker.com/t/how-to-copy-past-closing-price-for-my-watch-list-for-any-past-date/14495/7
first_date = "2019-09-03 00:00:00";
second_date = "2019-09-06 00:00:00";
first_price = Lookup(C, first_dt = _DT(first_date));
second_price = Lookup(C, second_dt = _DT(second_date));
Filter = Status("LastBarInRange");
SetOption( "NoDefaultColumns", True);
AddTextColumn(Name(), "Ticker", 1);
AddTextColumn(Interval(2), "Interval", 1);
AddColumn(first_price, DateTimeFormat("%m/%d/%Y",first_dt));
AddColumn(second_price, DateTimeFormat("%m/%d/%Y",second_dt));
Or @bolaakinpelu, if you really want to waste column space then
/// Scan specific date and display Close price
/// Zero BarCount Looping
/// @link https://forum.amibroker.com/t/how-to-copy-past-closing-price-for-my-watch-list-for-any-past-date/14495/7
Filter = Status("LastbarinRange");
first_date = "2019-09-03 00:00:00";
second_date = "2019-09-06 00:00:00";
first_price = Lookup(C, first_dt = _DT(first_date));
second_price = Lookup(C, second_dt = _DT(second_date));
SetOption( "NoDefaultColumns", True);
AddTextColumn(Name(), "Ticker", 1);
AddTextColumn(Interval(2), "Interval", 1);
AddTextColumn(DateTimeFormat("%m/%d/%Y",first_dt), "Date");
AddColumn(first_price, "Price");
AddTextColumn(DateTimeFormat("%m/%d/%Y",second_dt), "Date");
AddColumn(second_price, "Price");
And @bolaakinpelu,
Please read below thread before starting to create a thread:
Your first post had zero information about horizontal date alignment.
Mind reading has not been figured out yet.
You just wanted to know how to copy any price from AB to Excel in first post.
In the end you have made people wasting time because that information is missing in first post too!
1 Like