Thank you for your response! here is the macro I'm referring to. I don't know how to run applescripts - also the non negative numbers on the webpage appear as more of a dark gray so I'm hoping to find a solution within keyboard maestro. Hope this helps!
NP Fill.kmmacros (25.5 KB)
Very easy. Use the KM Execute an AppleScript action.
That's fine, if you can find a reliable KM solution.
IMO, trying to determine if each number is "red" using KM found image or color pixel is not likely to work well.
I'm having trouble downloading your macro, so I'll come back and edit this after I get it downloaded.
EDIT: OK, I've now looked at your macro.
Can you try this, manually:
- Select all of the fields on the web page that are of interest.
- Copy
- Paste into a blank Excel Sheet
- Save this file, zip it, and upload it here.
That will give us something we can work with to help solve your problem.
Thank you again for your replies! Here is that zip file. KMproject1.xlsx.zip (6.5 KB)
I hope this is what you meant. These are all of data that need to be *-1 that came up on a sample macro run, but depending on the situation, they could all be different and there could be more or fewer of them.
A bit more about the issue: the webpage is for studying trading tools for trading markets (foreign currency, metals, stocks etc. The down arrow key in the macro doesn't scroll through different fields on the webpage, it scrolls from one price chart (in this case currency pairs) to the next.
Since I'm recording data from 28 different pairs I have the macro set to repeat 28 times. A new webpage is loaded each repetition, but I'm copying the same fields each time (total net profit, total profit factor, total wins and total losses) and only the first of them needs to be *-1 if it's "red." There are thousands of tools to test, each with different settings and variables and stuff, and each adjustment alters which pairs have a positive or negative profit.
tl;dr: this zip may or may not be helpful because, on one hand, there is only one field of interest; on the other hand, there are virtually infinite ways to calculate the number in that field and I need to record as many of them as possible, as quickly as possible.
Basically, the image is very small and looks very similar to any other red text characters as far as image matching is concerned. If you can restrict your search to a small area, then Keyboard Maestro can better match it. You are doing that, but your area is in absolute coordinates, so I don't know where on the screen that might be - 457 from the left and 315 from the top of the main display, and then 100 x 100 rectangle below and to the right of that point.
Make sure it is searching in the correct place.
But even so, it is very hard to detect just text, because text tends to vary a lot, with anti-aliasing and subtle size changes.
Also, I note Keyboard Maestro is complaining about Translocation with the yellow warning triangle, and you should resolve that:
Thanks for the Excel file. That helped.
I'm working on a solution that works like this:
1. Select Cell in Excel that will be the Upper Left Corner of the Data to Paste from the Web page.
2. Select ALL cells on the web page, both black and red, that are of interest, that you want to copy.
3. Trigger this Macro
4. The maro/script will set all "red" numbers to a negative value.
Example Output
It should be ready to post later tonight, or tomorrow.
OK, here is the macro I promised:
MACRO: Copy Cells in HTML Table to Excel & Set Sign of Red Numbers [Example]
Please let us know if it meets you needs.
I took a completely different approach than the one you were using.
Instead of trying to ID the negative numbers by using Found Image, I copied the data to Excel, and then tested each cell there for the Red RGB color.
I think this is much more reliable, and probably faster as well.
Amazing work! Thank you so much for you help!
The macro successfully changes the "red" values negative! The only issue now is when I repeat the macro, it still stops and asks me to select a cell with the target color even though there are numbers in the magenta box. Here is how I modded the macro to repeat the required amount of times. Is there a way to make sure it "remembers" the rgb value so that the macro can run many times in succession, switching to the next page of the webpage/spreadsheet and selecting the cell of interest each time?
copy cells mod.kmmacros (20.3 KB)
A couple things I'm noticing:
When I use execute your macro, if the value selected on the webpage is not "red", it copies the data into the selected spreadsheet, then a box prompting me to select a cell with the "RED" color before continuing pops up, and when I select a cell with that color test already inside it, a new error box pops up saying "MACRO CANCELED due to script Error", and "[ERROR] no Red Cells were found with a RGB of 221, 46, 2."
When the data selected on the webpage IS "red," it copies the data into the selected cell in the spreadsheet, switches it from positive to negative and then the error box pops up "MACRO CANCELED due to script Error", and "[ERROR] no Red Cells were found with a RGB of 221, 46, 2."
when I run the macro I made incorporating yours so that the actions repeat the needed number of times, it's a little different:
For one, when i follow the instructions of the error box prompting me to select a cell with the "red" color, the data selected on the webpage is pasted into that cell over the data that was already there IN ADDITION to the cell I selected before running the macro, though only the latter is switched to a negative number. Also, the macro typically only gets through a few repetitions before timing out and aborting.
You are not using my macro as I intended.
My macro expects you to select ALL cells on the web page, and then it will copy/update all of them.
It is NOT intended to work on one cell at a time.
Look at my video/animated GIFF. That is the process you need to follow.
After all of the cells are in Excel, then you can use that data however you wish.
You must follow this process:
1. Select Cell in Excel that will be the Upper Left Corner of the Data to Paste from the Web page.
2. Select ALL cells on the web page, both black and red, that are of interest, that you want to copy.
3. Trigger this Macro
4. The maro/script will set all "red" numbers to a negative value.
That is the process for the entire web page -- NOT just one cell.
If you are following this process and still having trouble, then please make a video of what you are doing and post a link to that.
My apologies! This is all quite new to me and I'm doing a poor job describing the issue and helping others help me.
First here is a video of my attempt to run your macro as intended. I am still not succeeding as usually it just copies and pastes the data without prompting me to select a cell with the target color and then shows the "No red cells found" error.
However even if it if it does work as intended, this doesn't quite solve my issue and again that is my fault for not outlining the criteria clearly or correctly enough. Here is a video of the macro I made but was trying to modify when I first decided to seek help here:
As you can see, my macro selects the fields of interest, copies their data and pastes it into the corresponding cells in the spreadsheet and then automatically switches to the next page (in Excel) and scrolls down to load the data for the next currrency pair (in the browser) and repeats.
The main objective of the macro is to allow me to record the data from all 28 pairs quickly. Every time I adjust the method of calculating those values, I have to run the macro to get the data again in the next column on the spreadsheet.
So if yours works as intended I still need to do all of the following to resolve my issue and get where I'm trying to go:
- Delete the unwanted data that got copied (as I said earlier, there are only four specific fields on the page that I'm looking for and there are other fields between them so they cannot all be selected without roping in unwanted data).
- change the red numbers that aren't net profit back to positive (again, I only need to make the "red" numbers in the "Net Profits" row switch to negative, and not the "red" numbers in the "Profit Factor" row).
- transfer the data to my existing spreadsheet or build a new one around it.
- repeat the macro and all of the above steps every time I scroll down to the next pair and the next pair and so on.
If this is the best way to use KM to turn those numbers negative, it would still be faster to just run my original macro and then go through the 28 pages manually to change the red numbers from positive to negative (unless if I can find a way to build a new macro to automate most or all of these tasks).
Again, I am sorry if I have communicated these needs poorly and I greatly appreciate your efforts
Your videos are way too small for me to read.
Please create videos at least HD size (1920 x 1080). It would be best to be an actual video with you providing verbal narration of what you are doing, and trying to do. You can publish to YouTube as a private video, and just share the link here.
Thank you! I can't seem to figure out how to add audio for verbal narration but here are links to the videos on Youtube and I will type what the narration would have been.
DEMO Macro 1 (my macro): https://youtu.be/o_c3kammQ-I
-The purpose of my macro is to quickly copy data from the tradingview website to my excel spreadsheet. Before triggering the macro I SELECT ALL SHEETS in the spreadsheet, click on the cell where I want the FIRST data point to be pasted, UNGROUP the sheets, and finally make sure the tradingview page is displaying data from the LAST of the 28 currency pairs I want to look at.
- (0:02) when I trigger the macro, it first activates the website and hits the down arrow to scroll down to the next pair, since it was initially set to the final pair, it just goes back to the top to the FIRST pair I want to look at.
- (0:03) the macro copies the data from four separate fields of interest and pastes them into their corresponding cell in the spreadsheet.
-(0:08) after the last of the four fields has been copied and pasted, the macro tells Excel to switch to the next page, where the first target cell is already selected, and then the macro starts over and uses the down arrow key to load the data for the next currency pair on the website. - (0:09-0:15) this video shows one more repetition of the macro, but I have it set to repeat 28 times in total so that it imports the data from all 28 currency pairs. When I adjust or change the tools used to calculate this data, I select the next column in the spreadsheet and begin the process again.
- OBJECTIVE: I need to make a macro that completes this task but changes the NET PROFIT numbers (the third of the four fields being copied and pasted) from positive to negative in my spreadsheet IF they appear "red" on the website. The data from the other three fields being copied do not need to be changed, even if they are "red." At this time, I do not need to copy data from any fields other than the four I am already copying.
DEMO Macro 2 (your macro): https://youtu.be/o2aUq-_eFek
This macro is intended to (as I understand it) copy selected data from the website and paste it into a spreadsheet, prompt the user to select a cell containing the "red" color, and then change the data in ALL cells containing that color from positive to negative.
- (0:01) I circle the magenta box in the macro to show that the variable is blank.
- (0:04) I select the upper left-most cell in the spreadsheet as directed. The fields in the website are already selected.
-(0:07) the macro copies the data into the spreadsheet
-(0:10) an error box pops up with the words (MACRO CANCELLED due to script Error; [ERROR] No Red Cells were found with a RGB of 221, 46, 2).
I am not sure what I am doing to cause the macro to fail to prompt me to select a cell with the target color and change the data cells with that color from positive to negative. But even if I am able to make it do this, I am still just as far from achieving my objective as I was with my macro above, if not farther. As I said in the post above, even if this macro works as intended, it would appear that I still need to accomplish the following:
- Delete the unwanted data that got copied (there are only four specific fields on the page that I'm looking for and there are other fields between them so they cannot all be selected without roping in unwanted data).
- change the red numbers that aren't net profit back to positive (I only need to make the "red" numbers in the "Net Profits" row switch to negative, and not the "red" numbers in the "Profit Factor" row).
- transfer the data to my existing spreadsheet or build a new one around it.
- repeat the macro and all of the above steps every time I scroll down to the next pair and the next pair and so on.
If this is the best way to use KM to turn those numbers negative, it would still be faster to just run my original macro and then go through the 28 pages manually to change the red numbers from positive to negative (unless if I can find a way to build a new macro to automate most or all of these tasks).
I hope this is helpful in understanding my objectives and my current position.
@PubertHoggins, I think we are not communicating well.
Here's a full video demo of how to use my macro.
Please review, and let me know if you have questions on how to use for your use case.
Who would have thought two texans would have so much trouble communicating
Thank you for the demo vid w/ instructions! I've followed them but it still doesn't prompt me to select a cell with the "red" color. I figured out how to do video w/ audio so here is a demo with narration like you suggested. I start with your macro and then a partial demo of my existing macro to clarify what I'm trying to do.
To be clear, it will NOT ask you to select a cell if it finds cells with the "red" RGB that match the default set in the Macro.
Sorry, I misled you. The Video needs to be set for "unlisted" instead of "private". "Private" does not allow anyone but you to view the view.
So if you will just change the setting to "unlisted", and save, I should be able to view the video.
BlockquoteTo be clear, it will NOT ask you to select a cell if it finds cells with the "red" RGB that match the default set in the Macro.
but in that case shouldn't it find the data with that color and change it to negative? I wasn't able to make it do that.
BlockquoteSorry, I misled you. The Video needs to be set for "unlisted" instead of "private". "Private" does not allow anyone but you to view the view.
no problem! now it should work
OK, that's great! I can now view the video, and the problem and solution are obvious.
Some how the KM Variable "DND_CCH__RedRGB" got set to "0, 0, 0".
The fix is to change it back to the value in the macro of "221, 46, 2".
There are several ways you can do this, but the easiest is to just click on the Gear Button on the Set Variable Action in the KM Editor, and select "Try Action"
This will set the variable back to the default.
Now the macro should run properly.
Please try again, and if there are any issues, another video would be great.
So i have the variable set back to default but it is still not running as expected. here is another video.
OK, thank for the video -- very helpful.
Must be something about the web page that I'm not modeling in my test page.
So, please select all of the cells on the web page, and manually copy then paste into an excel file, that you then zip and post.
I'll take a look at it and see if I can figure out what is going on.